How can I select data from a mySQL table inside a foreach loop of another mySQL selection?

Question

I wanted to know, if you have a smarter solution:

<table>
    <thead>
        <tr>
            <th>Person</th>
            <th>Country</th>
            <th>Colors</th>
            <th>Number</th>
        </tr>
    </thead>
    <tbody>

    <?php 
        $pdo = Database::connect();
        $sql = "SELECT  *  FROM people ORDER BY timestamp ASC;" ;

        foreach ($pdo->query($sql) as $row) {
            $person = $row['person'];
            echo '<tr> ';
            echo('<td>'.$person.'</td>');
            echo('<td>'.$row['country'].' </td>');
            echo('<td>');
            $sql2 = "SELECT  *  FROM features WHERE person = '$person' ORDER BY colors ASC;" ;
            foreach ($pdo->query($sql2) as $row) {
                echo($row['colors'].'<br>');
            }
            echo('</td>');
            echo('<td>'.$row['Number'].' </td>');
            echo '</tr> ';
        }
        Database::disconnect();
    </tbody>
</table>

So what I wish to achieve, in one row I want to display all colors of my table featuresthat have the same person name than my table people:

Person   Country   Colors    Number 
===================================
Tom      France     red      12
                    green
                    blue

I know the way I did it is not a good way, but I don't know how to do it in another way. Because in the way I solved it, I get the following result:

Person   Country   Colors    Number 
===================================
Tom      France     red     
                    green
                    blue

Sougata Bose · Accepted Answer · 2015-08-11 08:23:20Z

1

Change the variable name ($row) for the second query. Its been overwritten

$sql2 = "SELECT  *  FROM features WHERE person = '$person' ORDER BY colors ASC;" ;
foreach ($pdo->query($sql2) as $row_sec) {
  echo($row_sec['colors'].'<br>');
}

The better way would be using JOINs to fetch all the data in a single query. Build the array accordingly or use GROUP_CONCAT and print the data. The example would be -

$sql = "SELECT  p.*, GROUP_CONCAT(f.colors) colors FROM people
 LEFT JOIN features f ON f.person = p.person
 GROUP BY p.person
 ORDER BY timestamp ASC;" ;
foreach ($pdo->query($sql) as $row) {

   echo '<tr> ';
   echo('<td>'.$row['person'].'</td>');
   echo('<td>'.$row['country'].' </td>');
   echo('<td>');
   echo(implode('<br/>', explode(',', $row['colors'])));
   echo('</td>');
   echo('<td>'.$row['number'].' </td>');
   echo '</tr> ';

}

edited Aug 11, 2015 at 8:23

answered Aug 11, 2015 at 8:16

Sougata Bose

31.8k8 gold badges52 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul · Accepted Answer · 2015-08-11 08:21:37Z

1

You can make it a single query using a simple JOIN and no need to fetching from loop like

SELECT  f.*  
FROM features f
JOIN people p ON f.person = p.person
ORDER BY f.colors;

answered Aug 11, 2015 at 8:21

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

How can I select data from a mySQL table inside a foreach loop of another mySQL selection?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related