[php] Duplicate results.

Hello everyone.

I am having a problem figuring out a problem im having with a php page i created. If you look at the code below, You maybe able to see a solution (Problem listed below code)

Code:

$x = mysql_query("SELECT * FROM friendslist WHERE fl_ADDER = ".$ir['userid'].""); while ($y = mysql_fetch_array($x)) { $a = mysql_query("SELECT * FROM personal_announcements WHERE pea_adder = '{$y['fl_ADDED']}' OR pea_adder = '".$ir['userid']."' ORDER BY pea_time ASC LIMIT $number"); if(mysql_num_rows($a) == '0') { if($ir['user_level'] == '2') { echo "<tr> <th colspan = '4'>You have no announcements to view</th> </tr>"; } else { echo "<tr> <th colspan = '3'>You have no announcements to view</th> </tr>"; } } else { while ($b = mysql_fetch_array($a)) { $user = mysql_fetch_object(mysql_query("SELECT * FROM users WHERE userid = ".$b['pea_adder']."")); echo " <tr> <td><a href = 'viewuser.php?u=$user->userid'>$user->username</a></td> <td>".$b['pea_title']."</td> <td><a href = 'personala.php?action=view&paid=".$b['pea_id']."'>Read</a></td>"; if($ir['user_level'] == '2') { echo "<td><a href = 'personala.php?action=delete&paid=".$b['pea_id']."'>Delete</a></td>"; } echo "</tr>"; } } echo " <tr>"; if($ir['user_level'] == '2') { echo "<td colspan = '4'><center><a href = 'personala.php?action=add'><font color = 'blue'>Add your own announcement</font></a></td>"; } else { echo "<td colspan = '3'><center><a href = 'personala.php?action=add'><font color = 'blue'>Add your own announcement</font></a></td>"; } } echo "</tr> </table>";

The problem im having is that when i run the page, It lists the 1 announcement but 6 times (Duplicates). Im assuming this is because i have my id in the friends list 6 times by different members.

I was thinking to simply do one while loop and have the first set an array which would show the first query as something like WHERE pea_adder = ".in_array($query)." or something. The problem im having is, I dont know any PHP functions that could create the array from the query OR if that would even work how i want it to anyway.

I also tried this in a left join to see if it solved the issue, but got the same result, 1 result shown 6 times.

If someone could point me in the right direction with this i would really appreciate it.

Edit: the $number in:

Code:

ORDER BY pea_time ASC LIMIT $number

is defined in a session so they can change the amount they view, more/less is +/- 5 and probably irrelevant to this problem, But thought i would list that anyway

Re: [php] Duplicate results.

What is the purpose of the code? What should it do?

The first 2 queries:

PHP Code:

$x = mysql_query("SELECT * FROM friendslist WHERE fl_ADDER = ".$ir['userid'].""); while ($y = mysql_fetch_array($x)) { $a = mysql_query("SELECT * FROM personal_announcements WHERE pea_adder = '{$y['fl_ADDED']}' OR pea_adder = '".$ir['userid']."' ORDER BY pea_time ASC LIMIT $number");

You have that value $ir['userid'], with which you select all rows in the friendslist where fl_ADDER == $ir['userid']. Than you fetch the results in an array (so $y['fl_ADDED'] == $ir['userid']!), than you select other rows from personal_announcements where pea_adder == $ir['userid'] or == $y['fl_ADDED']. (BTW, what are those brackets around $y['fl_ADDED'] in the second query? Whats the purpose of these?).

I think (if I am right, I dont know what the tables consist of and what the purpose of the code is) that you can simplify that to:

PHP Code:

$x = mysql_query("SELECT * FROM friendslist WHERE fl_ADDER = ".$ir['userid'].""); $a = mysql_query("SELECT * FROM personal_announcements WHERE pea_adder = '".$ir['userid']."' ORDER BY pea_time ASC LIMIT $number");

Perhaps that will solve the duplicate problem...

PS: Use if(mysql_num_rows($a) == 0) instead of if(mysql_num_rows($a) == '0'). The output of mysql_num_rows() can be any number or false when it fails. It will never return a string. (although PHP will convert the string to a number, it is better to test if it equals a number and not a string).

Re: [php] Duplicate results.

Quote:

Originally Posted by Daevius

What is the purpose of the code? What should it do?

Its to show announcements from people who are on your friend list.

Quote:

Originally Posted by Daevius

The first 2 queries:

PHP Code:

$x = mysql_query("SELECT * FROM friendslist WHERE fl_ADDER = ".$ir['userid'].""); while ($y = mysql_fetch_array($x)) { $a = mysql_query("SELECT * FROM personal_announcements WHERE pea_adder = '{$y['fl_ADDED']}' OR pea_adder = '".$ir['userid']."' ORDER BY pea_time ASC LIMIT $number");

You have that value $ir['userid'], with which you select all rows in the friendslist where fl_ADDER == $ir['userid']. Than you fetch the results in an array (so $y['fl_ADDED'] == $ir['userid']!), than you select other rows from personal_announcements where pea_adder == $ir['userid'] or == $y['fl_ADDED']. (BTW, what are those brackets around $y['fl_ADDED'] in the second query? Whats the purpose of these?).

No, Because the first query pulls all results from friends list where you are the added person, Therefore giving the list of ID's of the person who actually added you, Hence the need for a loop to pull all details into the second query.

Also, The brackets around the query makes no difference as far as i know, { and } works the same as ". and ."

Quote:

Originally Posted by Daevius

I think (if I am right, I dont know what the tables consist of and what the purpose of the code is) that you can simplify that to:

PHP Code:

$x = mysql_query("SELECT * FROM friendslist WHERE fl_ADDER = ".$ir['userid'].""); $a = mysql_query("SELECT * FROM personal_announcements WHERE pea_adder = '".$ir['userid']."' ORDER BY pea_time ASC LIMIT $number");

Perhaps that will solve the duplicate problem...

PS: Use if(mysql_num_rows($a) == 0) instead of if(mysql_num_rows($a) == '0'). The output of mysql_num_rows() can be any number or false when it fails. It will never return a string. (although PHP will convert the string to a number, it is better to test if it equals a number and not a string).

Calling only $ir to show results wouldnt help as $ir is your own details, Your level, username etc, So you would only see your own announcements, When you should see yours and your friends.

Also, The following wouldnt work alone:

PHP Code:

$x = mysql_query("SELECT * FROM friendslist WHERE fl_ADDER = ".$ir['userid']."");

because the query would pull numerous rows, So needs to loop to pull them all?

Re: [php] Duplicate results.

Ok, I now have a better idea how the code works.

But why do you select, everytime you loop, your own announcements too?

Code:

OR pea_adder = '".$ir['userid']."'

Doesn't that query gets eachtime its called, the list of announcements made by friends AND his own announcements (so it would result in all announcements made by friends and his own announcements repeated as often as how many friends he has)?

Re: [php] Duplicate results.

Quote:

Originally Posted by Daevius

Ok, I now have a better idea how the code works.

But why do you select, everytime you loop, your own announcements too?

Code:

OR pea_adder = '".$ir['userid']."'

Doesn't that query gets eachtime its called, the list of announcements made by friends AND his own announcements (so it would result in all announcements made by friends and his own announcements repeated as often as how many friends he has)?

Your right, A friend came on msn earlier and we brain stormed the whole page for reasons.

That was the problem, Which is now fixed after making new functions to view your own in a seperate table.

Thanks for the help with it :)

No idea why i overlooked this to be honest :boxed2: