PHP + MySQL - Why does this query always double up the results?

R

Ricochet

New member
Basically im trying to find all items which aren't owned by the current user and are in the same room as the user. However when i send it into an array with a while loop i always get the results doubling up is it the mysql or the while loop that's wrong? or both?!

$query = "SELECT * FROM items_to_users,items WHERE items_to_users.user_id='$user_id' AND items.room_id='$room_id' AND items_to_users.item_id != items.id";

The code to add it to the array is below:

$fetch = mysql_query($query) or die(mysql_error());
$items = array();
while ($item = mysql_fetch_array($fetch)) {
array_push($items,$item['item_id']);
}
print_r($item);


What am i doing wrong?
 
The doubling of results from a SQL query ALWAYS means that you have not properly constrained the query. Your WHERE clause is wrong. And without knowing your table structures, I cannot be more specific.

However, looking at your query, I'm guessing that your join clause should be equal to instead of not equal to? Try writing it this way, it will make more sense. I broke it into separate lines for readability.

select *
from items_to_users inner join items on (items_to_users.item_id = items.id)
where items_to_users.user_id = '$user_id'
and items.room_id = '$room_id';

Also, I'm just learning PHP, but do you really want quotes around the PHP variable names? Will the variables evaluate to their value before the string gets created and stored in $query?
 
Without knowing the table structure it will be difficult to comment right away.
However, it appears there could be something wrong with your query.

Try with some specific values on the rhs and see why it is not imparting the right results.
 
Back
Top