2 while loops in php - mysql select statement

Question

I have 2 tables in DB:

clients (Show all clients data)

clientsmany (admin could add many phone numbers for each client)

I would like to print all the details about the clients in 1 html table and if any client has more than phone number, all the numbers are printed in the same cell of 'td'

<?php
$result = mysql_query("SELECT * FROM clients");
$result1 = mysql_query("SELECT clientsmany.phone, clients.ID FROM clients INNER JOIN clientsmany ON clients.ID=clientsmany.ClientID");

while($row = mysql_fetch_array($result)){ //Not read!
    while($row1 = mysql_fetch_array($result1)){ //Working correctly and show the list of 'phone' in $row1 for clientsmany.phone
echo "<center><table border='1'>";
echo "<tr><td>".$row['ID']."</td><td>".$row['phone']."<br>".$row1['phone']."</td></tr>";
echo "</table></center>";
}}
?>

Why the 1st while is not working??

The 2nd while only works and print a correct data then it exit automatic!

STR · Accepted Answer · 2013-10-06 20:13:04Z

4

<?php
echo "<center><table border='1'>";
$result = mysql_query("SELECT * FROM clients");
 while($row = mysql_fetch_array($result)){
 $result1 = mysql_query("SELECT * FROM clientsmany WHERE clientsid=".$row['id']);
 if(mysql_num_rows($result1)>0 ){
 while($row1 = mysql_fetch_array($result1)){
 echo "<tr><td>".$row['ID']."</td><td>".$row['phone']."<br>".$row1['phone']."</td>       </tr>";
}
}else{
 echo "<tr><td>".$row['ID']."</td><td>".$row['phone']."</td></tr>";
}
}
echo "</table></center>";
?>

edited Oct 6, 2013 at 20:13

answered Oct 6, 2013 at 19:24

STR

563 bronze badges

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

Comments

Patrick Evans · Accepted Answer · 2013-10-06 05:10:19Z

1

Use GROUP_CONCAT to create a single query and you will be able to a single loop

GROUP_CONCAT will take a column that is repeated and separate each value with a comma (by default it can be changed) and return it into a single value

$query = <<<END
    SELECT 
       clients.*,
       GROUP_CONCAT(clientsmany.phone) as phonenums 
    FROM 
      clients 
    INNER JOIN 
      clientsmany ON clients.ID=clientsmany.ClientID 
    GROUP BY 
      clients.ID 
END;

A query like this would give you all the clients table columns and a column named phonenums which will be a comma separated list of the phone numbers

Now since you only have one query you only need one loop

$db = mysqli_connect(...);

...

//only need to echo out the <table> part once
//so taken out of the while loop
echo "<center><table border='1'>";

$result = mysqli_query($db,$query);
while( ($row = mysqli_fetch_assoc($result)) ) {
   echo <<<END
      <tr>
         <td>{$row['ID']}</td>
         <td>{$row['SomeOtherColumn']}</td>  
         <td>{$row['phonenums']}</td>
      </tr>
END;
}

//Again the </table> only needs done once 
//so taken out of the loop
echo "</table></center>";

Notice the mysli_* functions being used. Mysql api is depreciated, for the most part you can just rename the functions you currently use to mysqli_, but note that some require the $db link as a parameter so make sure to read the php manual on each of the functions so you know how to call them correctly.

Also note that I am using heredoc syntax instead of doing multiple echo calls

edited Oct 6, 2013 at 5:10

answered Oct 6, 2013 at 4:50

Patrick Evans

42.8k6 gold badges81 silver badges94 bronze badges

3 Comments

Ahmed Over a year ago

it show all the data in clientsmany table correctly but not all the rows in the main clients table :) .... I need both tables appears

Patrick Evans Over a year ago

@Ahmed you have to add the the other columns to the while loop, you never showed what the full structure to the clients table was so there was no way for us to know how to show the other fields, which is why i added the {$row['SomeOtherColumn']} part in there so you would know to add whatever other column data you needed to show.

mickmackusa Over a year ago

This is the best advice for the task. The STR's answer, despite functioning as required, should not be used by the OP or researchers. This is the professional way. Because all desired data can be compiled into a single result set, it should be. @Ahmed

Sumit Bijvani · Accepted Answer · 2013-10-06 04:39:55Z

0

First, mysql_* functions are depreciated. Try to use mysqli_* functions.

If you want to display data in 1 html table, so why you have started while above table tag?

Try this query..

SELECT clientsmany.phone, clients.* FROM clients, clientsmany WHERE clients.ID=clientsmany.ClientID"

Then use while statement below table tag (No need of 2 different while loop)...

echo "<center><table border='1'>";
while($row1 = mysqli_fetch_array($result1))  {
   // your <tr><td> code
}
echo "</table></center>";

edited Oct 6, 2013 at 4:39

answered Oct 6, 2013 at 4:34

Sumit Bijvani

8,17518 gold badges52 silver badges83 bronze badges

2 Comments

Sumit Bijvani Over a year ago

@JanDvorak Whole code of OP is in mysql_* functions that's why I have proceed it in mysql_ function but now I have changed it.

Ahmed Over a year ago

it show all the data in clientsmany table correctly but not all the rows in the main clients table :) .... I need both tables appears

Collectives™ on Stack Overflow

2 while loops in php - mysql select statement

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related