PHP duplicating query results?

Question

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!

score 7 · Answer 1 · edited May 23 '17 at 11:49

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:

Visual Representation of SQL Joins

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.

How to prevent SQL injection in PHP?

Thanks my friend, not mainly because of the answer but you opening a new door full of knowledge — Solorzano Jose, Feb 15 '13 at 05:36

score 3 · Answer 2 · answered Feb 15 '13 at 05:37

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.

PHP duplicating query results?

2 Answers2