MySQL pulling information from multiple table issue

Databases
1

Hello

I’m trying to access information from 2 different tables and its retrieving the information but it all so pulling in user information multiple times pending how many fruits listed in table tbl_fruits.

like to be able to display user information once and pull what ever number of fruits associated with the user at the same time.

2 tables:
tbl_users:

  • userid
  • firstname
  • lastname

tbl_fruits:

  • userid
  • fruit

in the example code userID 4 has 3 fruits associated with him in tbl_fruit. As you can see from the results below the user information is listed multiple times. How can I rewrite the code so that the user information is pulled once and the fruits show up 3 times.


$clientID = "4";

try
{	$stmt = $dbcon1 ->query("SELECT
				tbl_fruits.fruit,
				tbl_users.userid,
				tbl_users.firstname,
				tbl_users.lastname
		       FROM tbl_users
                 LEFT JOIN tbl_fruits
			   ON tbl_fruits.userid = tbl_users.userid	
   	             WHERE  tbl_users.userid = '$clientID' ");				
	
	$testArray = $stmt->fetchAll(PDO::FETCH_ASSOC);			
}
catch(PDOException $e)
{    echo $e->getMessage(); }

echo '<pre>';
print_r($testArray);
echo '</pre>';

results


rray
(
    [0] => Array
        (
            [fruit] => Apple
            [userid] => 4
            [firstname] => John
            [lastname] => Smith
        )

    [1] => Array
        (
            [fruit] => Orange
            [userid] => 4
            [firstname] => John
            [lastname] => Smith
        )

    [2] => Array
        (
            [fruit] => Banana
            [userid] => 4
            [firstname] => John
            [lastname] => Smith
        )

)
2

In PHP you could create a new array of users, with a sub-array for each user of the fruits

3

just for clarification are you saying, create 2 different queries?

4

No, you take the array with the results of the query in the op and use that to build a new array, I can’t remember a specific thread title but there has been a thread in the last few months that shows how to do it (in the case of that it was reorganising the output of the $_FILES array when multiple files were uploaded to make it easier to process the uploaded files).

5

this should actually be done in php

but since you posted in the mysql forum, let me introduce you to the GROUP_CONCAT function



SELECT tbl_users.userid
     , tbl_users.firstname
     , tbl_users.lastname
     , GROUP_CONCAT(tbl_fruits.fruit) AS fruits
  FROM tbl_users
LEFT OUTER
  JOIN tbl_fruits 
    ON tbl_fruits.userid = tbl_users.userid  
GROUP
    BY tbl_users.userid