Comparing querry results-please help!

[faco]

Dear all,

 

I have a problem with comparing query results. Can You please help me or give me a hint how to solve this?

i have two tables: cart and books. In table books i have list of books with column 'bid' (int type)which is also a primary key. in table cart i have column 'bookid' (int type). What i want is to go through results of these 2 queries and if there is a match between 'bid' and 'bookid' to display the other info from table books which is associated with that 'bid'!

 

$sql2 = "SELECT cid, bookid FROM cart";

$result2=mysql_query($sql2) or die (mysql_error());

$sql3 = "SELECT bid, author, title, price FROM books";

$result3=mysql_query($sql3) or die (mysql_error());

while($row2 = mysql_fetch_array($result2))

{

while($row3 = mysql_fetch_array($result3))

{

if($row3['bid'] == $row2['bookid'])

{

echo $row3['title'];

echo $row3['author'];

echo $row3['price'];

}

}

}

 

This code gives me only one set of the results...probably because this second while loop can only be done once, but how to rewrite this in some other way to receive all the results...i have tried using foreach loops but probably have implemented wrong...so how to solve all of this?

Thanks and regards,

 

faco

[jeremy0]

Dear all,

 

I have a problem with comparing query results. Can You please help me or give me a hint how to solve this?

i have two tables: cart and books. In table books i have list of books with column 'bid' (int type)which is also a primary key. in table cart i have column 'bookid' (int type). What i want is to go through results of these 2 queries and if there is a match between 'bid' and 'bookid' to display the other info from table books which is associated with that 'bid'!

 

$sql2 = "SELECT cid, bookid FROM cart";

$result2=mysql_query($sql2) or die (mysql_error());

$sql3 = "SELECT bid, author, title, price FROM books";

$result3=mysql_query($sql3) or die (mysql_error());

while($row2 = mysql_fetch_array($result2))

{

while($row3 = mysql_fetch_array($result3))

{

if($row3['bid'] == $row2['bookid'])

{

echo $row3['title'];

echo $row3['author'];

echo $row3['price'];

}

}

}

 

This code gives me only one set of the results...probably because this second while loop can only be done once, but how to rewrite this in some other way to receive all the results...i have tried using foreach loops but probably have implemented wrong...so how to solve all of this?

Thanks and regards,

 

faco

 

reverse your 'while' statements that loop over your query results - that should probably do the trick.

 

while($row3 = mysql_fetch_array($result3))
{
while($row2 = mysql_fetch_array($result2))
{
   if($row3['bid'] == $row2['bookid'])
      {
echo $row3['title'];
echo $row3['author'];
echo $row3['price'];
}
}
}

[jeremy0]

also, it would be more efficient to store the cart query in memory instead of hitting the database for every book record; or you could combine the two queries into one sql join and affect the output that way..

[faco]

also, it would be more efficient to store the cart query in memory instead of hitting the database for every book record; or you could combine the two queries into one sql join and affect the output that way..

 

hey,

i've tried with reversing the while loop, but didnt work, it skips some results. also, storing cart query in memory is not a solution as i have some other attributes in that table which arent displayed, like userid and etc which implies that i have different cart all the time...so...i tried with mysql join on which i have totally forgot, and i managed it with one hit to DB. You helped me a lot. Thanks, 

Also the code now looks like this:

 

$sql4 = "SELECT books.bid, books.author, books.price, books.title, cart.bookid FROM books, cart WHERE books.bid = cart.bookid ";

$result4=mysql_query($sql4) or die (mysql_error());

 

while($row4 = mysql_fetch_array($result4))

{

echo $row4['title'];

echo $row4['author'];

echo $row4['price'];

}