For some reason,when I run this query through PHP, it returns 8 rows, when in reality i only have 2 rows in my database, and when i run it through phpmyadmin it returns 2 obviously.
$sql = "SELECT url FROM bookmarks,users WHERE bookmarks.user_id = {$session->user_id}";
$resultado = $db->query($sql);
echo mysql_num_rows($resultado);
I'm breaking my head!
your query produces cartesian product because you have not supplied the relationship between the two tables: bookmarks and users,
SELECT url
FROM bookmarks
INNER JOIN users
ON bookmarks.COLNAME = users.COLNAME
WHERE bookmarks.user_id = '$session->user_id'
where COLNAME is the column that defines how the table are related with each other, or how the tables should be linked.
To further gain more knowledge about joins, kindly visit the link below:
As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.
As JW pointed out, you are producing a Cartesian Product -- you aren't joining bookmarks against users. That's why you're duplicating rows.
With that said, you don't need to join users at all in your above query:
"SELECT url FROM bookmarks WHERE bookmarks.user_id = {$session->user_id}"
Good luck.
