Too many results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
chris12295
Forum Contributor
Posts: 113
Joined: Sun Jun 09, 2002 10:28 pm
Location: USA
Contact:

Too many results

Post 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?
samscripts
Forum Commoner
Posts: 57
Joined: Tue Apr 23, 2002 4:34 pm
Location: London, UK

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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
chris12295
Forum Contributor
Posts: 113
Joined: Sun Jun 09, 2002 10:28 pm
Location: USA
Contact:

Post by chris12295 »

samscripts, that was perfect, i had forgotten to add that line and overlooked it everytime, thank you very much
Post Reply