Page 1 of 1

Too many results

Posted: Sun Jul 14, 2002 9:21 pm
by chris12295
I have 2 seperate tables, one with ad information and one with pictures, the are related by adID field. When i run this

Code: Select all

while($row = mysql_fetch_array($results, MY
SQL_ASSOC)) {
$count+=1;
$asdf = mysql_query("SELECT * from pictures, user_ads where pictures.adID = '$rowїadID]'");
while($apic = mysql_fetch_array($asdf, MYSQL_ASSOC)) {
echo "$apic&#1111;src] : : : $count<br>";
&#125;
however, it seems the right images are printed but they are printed once for each ad in search query. I only want them printed once, (may be more than one image ascosiated with an ad). Whats wrong with the code?

Posted: Mon Jul 15, 2002 6:22 am
by samscripts
Hi, I think the problem is that you are not joining the user_ads table and the pictures table in the query, so mysql is returning lots or unrelated rows. Does that make sense?

Anyway, try adding the following to the WHERE condition:

Code: Select all

AND user_ads.adID=pictures.adID
(if adID is the name of the id field in your user_ads table.)

hope this helps, Sam

Posted: Mon Jul 15, 2002 10:34 am
by mikeq
Samscripts is absolutely correct, what you have is known as a Cartesian product because you did not join all the tables.

Basically every record in 1 table gets joined to every record in the other table, so if you have 1 table with 1000 records and 1 table with a million records doing a query like this would produce 1 billion record result set and take forever to run.

samscripts said "Anyway, try adding the following to the WHERE condition", rather than 'TRY' you must ALWAYS join your tables.

Mike

Posted: Mon Jul 15, 2002 12:27 pm
by chris12295
samscripts, that was perfect, i had forgotten to add that line and overlooked it everytime, thank you very much