SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    canada
    Posts
    157
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    MySQL pulling information from multiple table issue

    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.

    PHP Code:
    $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
    Code:
    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. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    In PHP you could create a new array of users, with a sub-array for each user of the fruits
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    canada
    Posts
    157
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    In PHP you could create a new array of users, with a sub-array for each user of the fruits
    just for clarification are you saying, create 2 different queries?

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by robin01 View Post
    just for clarification are you saying, create 2 different queries?
    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).
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by robin01 View Post
    How can I rewrite the code so that the user information is pulled once and the fruits show up 3 times.
    this should actually be done in php

    but since you posted in the mysql forum, let me introduce you to the GROUP_CONCAT function
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •