How to show two results of two queries of mysql in one table?

Question

This is my code and the result is wrong. I want to show the results of my first query in first column and results of second query in the 2nd column,but both results go in one (first) column. How can I have like this without changing the query?

/*
    username        username
    username_a_1    username_b_1
    username_a_1    username_b_4
    username_a_2    username_b_1
    username_a_2    username_b_4
    username_a_3    username_b_5
    username_a_4    username_b_2
    username_a_4    username_b_3
    username_a_5    username_b_1
    username_a_5    username_b_4
*/
<html>
<head></head>
<table border="1" >
    <tr>
        <th>USERNAME</th>
        <th>USERNAME</th>
    </tr>
<?php
include'db_connect.php';

$query1='SELECT username FROM contacts_a ';
$query_run1=mysql_query($query1);

$query2="SELECT contacts_a.username,contacts_b.username FROM contacts_a LEFT JOIN contacts_b ON contacts_a.level=contacts_b.level";
$query_run2=mysql_query($query2);



 while($query_array1=mysql_fetch_assoc($query_run1)){
        foreach($query_array1 as $index => $names){

            echo   '<tr>
                        <td>'.(($names == NULL )? 'NULL': $names).'</td>
                   </tr>'; 
        }//end of foreach

  }//end of while

  while($query_array2=mysql_fetch_assoc($query_run2)){

        foreach($query_array2 as $index => $names){

             echo   '<tr>
                      <td>'.(($names == NULL )? 'NULL': $names).'</td>
                    </tr>'; 
        }//end of foreach

  }//end of while




?>
</table>

</html>

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). *They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation)*. Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Oct 06 '14 at 18:12
You already have all the data you need in your second query: contacts_a.username is the username from the first query and contacts_b.username is the username from the second query. Just loop over the second set of data to print to your table. — Mark, Oct 06 '14 at 18:14
... and add an `ORDER BY` clause to that query to get the rows returned in a sequence that you specify, rather than allowing the database to return the rows in whatever order it wants. — spencer7593, Oct 06 '14 at 19:01

score 0 · Accepted Answer · edited May 23 '17 at 10:33

Try this:

// Select the data, all together now. The difference is we'll give it a name
$query2="SELECT contacts_a.username as firstTableName, 
                contacts_b.username as secondTableName 
           FROM contacts_a 
      LEFT JOIN contacts_b 
             ON contacts_a.level = contacts_b.level";

// Execute the Query
$query_run2=mysql_query($query2);

// Loop over our results...
while($query_array1 = mysql_fetch_assoc($query_run1)) {
    // We're going to use this again, give it a good name
    $firstName = ($query_array1['firstTableName'] == NULL)   ? 'NULL' :
        $query_array1['firstTableName'];
    $secondName = ($query_array1['secondTableName'] == NULL) ? 'NULL' :
        $query_array1['secondTableName'];

    // Put out a new table row
    echo   '<tr>';
        // And our first TD...
       echo '<td>' . firstName  .'</td>';
       echo '<td>' . secondName .'</td>';
    echo   '</tr>'; 

// End our loop
} // So Long and Thanks for All the Fish!

Some other thoughts: Don't use MySQL! You can also get rid of the first query now, as it's superfluous. Regarding your NULL checks, what happens if the data goes in as an empty string? Typically I check for null and for empty quotes: '' or "" just to make sure I get back what I want.

DISCLAIMER: I didn't actually try this, and I use PDO so my MySQL syntax for getting back and reading the MySQL array may be incorrect!

thanks for your answer but my big problem is how to fill two columns of table at the same time when i have 2 queries and while loop you wrote does n`t do that — mre7_a, Oct 06 '14 at 19:31

score -2 · Answer 2 · answered Oct 06 '14 at 18:15

$qa1 = mysql_fetch_assoc($query_run1);
$qa2 = mysql_fetch_assoc($query_run2);

do {
  echo   '<tr>
    <td>'.(current($qa1) == NULL ? 'NULL': current($qa1)).'</td>
    <td>'.(current($qa2) == NULL ? 'NULL': current($qa2)).'</td>
  </tr>'; 
} while( next($qa1) || next($qa2) );

How to show two results of two queries of mysql in one table?

2 Answers2