Query with a Sub Query

marion0

I'm trying to create a query that will pull data from one table, and display it using a while() loop. In the while loop I've setup a second query to be run, but things aren't working out like I thought they would.

Here's an example of the code.

$sql1 = "SELECT $table1";
$sql2 = "SELECT $table2";

$res1 = mysql_query($sql1);
$res2 = mysql_query($sql2);

while ( $row = mysql_fetch_array($res1)){
$column = $row['column'];
echo("$column");
while ( $row_2 = mysql_fetch_array($res2)){
$column_2 = $row_2['column_2'];

echo("  $column");
}
}

What I thought would happen was I get a result like this:

$column
   $column_2
$column
   $column_2
$column
   $column_2

Instead what I get is:

$column
   $column_2
   $column_2
   $column_2
$column
$column

All of the results end up under the first value returned from the first query. Am I using the wrong mysql function, or do I not have my while loops set up correctly?

lurius

Q: "Am I using the wrong mysql function, or do I not have my while loops set up correctly?"

A: While loops are not set up correctly.

try:

while ($row1 = mysql_fetch_assoc ($res1))
{
$column = $row['column'];
echo("$column");
// Fetch just one row from res2
$row2 = mysql_fetch_assoc ($res2);
// If we can't fetch any further rows from res2, continue with res1
if ($row2 == false)
continue; // continue fetching $res1
$column2 = $row2['column_2'];
echo("$column2");
}

Drawback is that if res2 has more rows than res1, they are not printed...

Add:
while ($row2 = mysql_fetch_assoc ($res2))
{
....
}
after the whole thing to fix this...

-Lurius

marion0

lurius you've gotten me farther than I was, but I'm still missing something.

Here's my code:
while( $row = mysql_fetch_assoc($return_member_list) ){
$member_name = ucfirst($row['last_name']).", ".ucfirst($row['first_name']);
echo("<p>$member_name<br>\n");
$row_guest = mysql_fetch_assoc($return_guest_list);
if ($row_guest == false)
continue;
$guest_name = $row_guest['guest_name'];
echo("      $guest_name<br>\n");
}
while( $row_guest = mysql_fetch_assoc($return_guest_list)){
$guest_name = $row_guest['guest_name'];
echo("      $guest_name<br>\n");
}

It's output is:

member_name
    guest_name
member_name
    guest_name
member_name
    guest_name
    guest_name
    guest_name
    guest_name

The last person now gets all of the guests, and they're not listed by the right member_name.

Did I goof in the code? I don't see where it differs from lurius'

marion0

lurius,

I went another with my code, and the code you gave me helped me out a great deal.

I went through and kept thinking there was another way to do this that was a little less complicated.

I kept playing with your code, and realized what I was trying to do. I needed to run 1 sql stament at a time, and then run the seond based on the results of the first. I was trying to get it all at one time and sepearate it later.

I left my first sql statement alone. The second I moved into the while loop of the first, and then ran the second sql statment with its own loop, however, I modified the sql2 to pull the information associted with the returned value I declared for first sql statement.

$sql1 = SELECT data
$res1 = mysql_query($sql1);
while($row = mysql_fetch_array($res1)){
$column = $row['column'];
echo("$column");
$sql2 = SELECT data WHERE column_2 = '$column'
$res2 = mysql_query($sql2);
while($row_2 = mysql_fetch_array($res2)){
$column_2 = $row_2['column_2'];
echo("$column_2");
}
}

You should get the idea with that. Anyway the end result is what I needed.