Query echo duplicated values when selecting two tables

Question

I have a site that prints out all the registered users . the query worked fine until i wanted to add value from another table. now its printing out duplicated values.

lets say I have 2 users . it supposed to print out

John Doe Jane Doe

But after adding the table with the avatar it prints out John Doe Jane Doe John Doe Jane Doe

This is my query.

<?php 
function fetch_users(){
global $db;
$query = $db->query("SELECT user.id, user.username,user.email, userdetails.profile_img FROM user , userdetails");
$user = array();
while(($row = $query->fetch(PDO::FETCH_ASSOC)) !==FALSE) { 
 $user[] = $row;
}
return $user;
}
?>

But If i remove the last added table userdetails and use the query without adding the table to the name. it work fine again, but then witout the avatar ofscourse.

php.

<?php foreach(fetch_users() as $user){  ?>
 <p>
 <a href="profile.php?uid=<?php echo $user['id'];?>"><?php echo $user['username'];?> </a>
 </p>
 <?php
  }
  ?>

you're seeing a cartesian product of the tables because you're joining the tables without a condition. Try specifying WHERE caluse — Ejaz

Leng Leng · Accepted Answer · 2013-04-17T21:05:27

You may want to look into MySQL joins.

For instance, you could do something like the following:

SELECT user.id, user.username, user.email, userdetails.profile_img
FROM user
LEFT JOIN userdetails
ON user.id = userdetails.<user id column name goes here>
GROUP BY user.id

This assumes you have (which you should definitely have, if you don't) a column in the userdetails table which contains keys to reference the user table by id.

Query echo duplicated values when selecting two tables

2 Answers