3
\$\begingroup\$

I asked this question before. However, I realized that I had to edit some of my code after I got good answers. Therefore, I'm asking a new question here.


I have the following database:

enter image description here

Each member may have multiple addresses (1:n) stored in table address. It's possible that one member has multiple addresses within one country.

Further, each member may have multiple titles (m:n) mapped in table title2memb. Each entry idRank has a name which can be found in title.

I want to get the first N members that have at least one address in the country "US" and I want to display all their titles.

Here is working code, using two JOINS and two where statements for N=2:

<?php

$db       = new mysqli("localhost","root","","example");
$sql      = "SELECT DISTINCT m.name, m.id FROM member m
              INNER JOIN address a ON m.id = a.idMember AND a.country='US' LIMIT 2";
$membersResult   = $db->query($sql);

while($member = $membersResult->fetch_assoc()){
  $id            = $zeile['id'];
 
  echo $zeile['name'] . ": ";

  $sub_sql       = "SELECT t.name FROM title t INNER JOIN title2memb tm ON
                    t.id = tm.idRank AND tm.idMember='$id'";
  $sub_result    = $db->query($sub_sql);
  while($rank = $sub_result->fetch_assoc())
  {
    echo $rank['name'] . " ";
  }

  echo "<br>";
}


?>

And as a result I get:

Bernd: Bee

Tom: Bear Dog

Is it really necessary to have here 2 nested while clauses? Or can I get both in one SQL?

Some remarks:

  • I had to use the DISTINCT keyword in my first SQL, otherwise I would get Bernd twice.
  • I have avoided using prepared statements and encoding the input to make the code more readable. I know that the here presented version is vulnerable to XSS.
\$\endgroup\$
1
\$\begingroup\$

Firstly, for questions like this is would be helpful to provide an SQL fiddle. I mocked one up to test on, it only takes a few minutes but would be much faster for you since you have the table definitions.

Is it really necessary to have here 2 nested while clauses? Or can I get both in one SQL?

You can absolutely get everything you need in one SQL query without nested queries. In fact, you should. Performing database queries within a loop is never ideal and it's always a better idea to get the data you need (even if slightly more than you need) then filter it down with PHP to avoid having to do multiple queries.


The logic

I want to get the first N members that have at least one address in the country "US" and I want to display all their titles.

OK - so you're aiming for two things - the member's name and their titles. Your conditions are based on the address table data, so you should start your query there:

SELECT `idMember` FROM `address`
WHERE `country` = 'US'
GROUP BY `idMember`
LIMIT 2

Your at least one address in the country "US" condition is covered by WHERE country = 'US', and the GROUP BY will limit your duplicated rows. If you want to see how many addresses each member has (or filter on it) you can add COUNT(*) to the select clause to see. You will adjust the LIMIT as required.

This gives you the member IDs that you need to retrieve titles for. From here, just query the tables you need and join the results above:

SELECT `member`.`id`, `member`.`name`, `title`.`name`
FROM `title`
INNER JOIN `title2memb` ON `title2memb`.`idRank` = `title`.`id`
INNER JOIN (
  SELECT `idMember` FROM `address`
  WHERE `country` = 'US'
  GROUP BY `idMember`
  LIMIT 2
) AS `us_members`
  ON `us_members`.`idMember` = `title2memb`.`idMember`
INNER JOIN `member` ON `member`.`id` = `us_members`.`idMember`;

Note that I've added the member's ID here because you'll use it to group the result set with PHP, as MySQL as a relational database is not able to return multi-dimensional results (see NoSQL/MongoDB/DynamoDB etc if you want that!).

I have avoided using prepared statements and encoding the input to make the code more readable. I know that the here presented version is vulnerable to XSS.

Noted - won't comment on that.

To implement, you can do something like this:

$country = 'US';
$limit = 2;

$sql = '
    SELECT `member`.`id`, `member`.`name`, `title`.`name` AS `title_name`
    FROM `title`
    INNER JOIN `title2memb` ON `title2memb`.`idRank` = `title`.`id`
    INNER JOIN (
      SELECT `idMember` FROM `address`
      WHERE `country` = "' . $country . '"
      GROUP BY `idMember`
      LIMIT  ' . $limit . '
    ) AS `us_members`
      ON `us_members`.`idMember` = `title2memb`.`idMember`
    INNER JOIN `member` ON `member`.`id` = `us_members`.`idMember`;
';

$membersResult = $db->query($sql);

// Use PHP to group the results by the member ID
$members = array();
while ($member = $membersResult->fetch_assoc()) {
     // Track the member's name and an array of titles against their ID (which is unique)
     if (!array_key_exists($member['id'], $members)) {
        $members[$member['id']] = array(
            'name' => $member['name'],
            'titles' => array()
        );
     }
     // Add the title
     $members[$member['id']]['titles'][] = $member['title_name'];
}

This will give you a simple structured array like so:

Array
(
    [1] => Array
        (
            [name] => Bernd
            [titles] => Array
                (
                    [0] => Bee
                )
        )
    [3] => Array
        (
            [name] => Tom
            [titles] => Array
                (
                    [0] => Bear
                    [1] => Dog
                )
        )
)

Now, loop that array and output the data how you want to:

foreach ($members as $memberId => $member) {
    echo sprintf('%s: %s<br>', $member['name'], implode(' ', $member['titles']));
}

Example output (from a browser):

Bernd: Bee
Tom: Bear Dog

General formatting

While we're here, you should consider a few best practices:

  • Use single quotes always, unless you require double quotes
  • Choose a rule for indentation and stick to it. Four spaces is generally accepted as "the norm" for PHP (see while contents)
  • Add spaces before and after brackets in control structures (see while)
  • Space out arguments in function calls (see $db)
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.