SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple row query not working

    I have a query that does not work. I am trying to get data from two tables. The important result is that I can put all the data into the while loop at the end. If I edit the query to one table, all works fine, so I have narrowed it down to the SELECT statement. The tables are innodb with the userid as the foreign key.

    Anyone have any suggestions?

    Thank you

    Gary

    PHP Code:
     
        
    try{
    $sql "SELECT pocontact.recipient_fn, pocontact.recipient_mi, pocontact.recipient_ln FROM pocontact  
     
    UNION SELECT 
     gb_messages.msg_date, gb_messages.msg_id FROM gb_messages
     WHERE userid ='
    $userid'";
    $q $conn->prepare($sql);
    $q->execute();
    } catch (
    PDOException $e) {
        print 
    "Error!: " $e->getMessage() . "<br/>";
        die(
    'Ooops');
    }

    $count $q->rowCount();
    if(
    $count>= 1){
    echo 
    '<h3>Existing Messages</h3>';
    }else{
    if(
    $count == 0){
    echo 
    '<h3>You have Not Created any Messages</h3>';
    }
    }
    echo 
    '<table>';
    while(
    $row $q->fetch())
    {

    echo 
    '<tr><td>'$row['recipient_fn']. ' '.$row['recipient_mi'] .' ' $row['recipient_ln'].'</td><td>'$row['relation'].'</td><td>'$row['msg_date'].' </td><td><a href="update-goodbye.php?msg_id='.$row['msg_id'].'">Edit</a></tr>';

    }
    echo 
    '</table>'

  2. #2
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,027
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    I think you're misusing UNION. Try a join

    Code sql:
    SELECT 
      pocontact.recipient_fn, pocontact.recipient_mi, pocontact.recipient_ln, gb_messages.msg_date, gb_messages.msg_id
    FROM pocontact 
    JOIN gb_messages ON (gb_messages.userid = pocontact.userid)
    WHERE gb_messages.userid = ?

    Also, since you are using PDO, take full advantage of it's ability to transmit the query and the data of the query seperately to avoid SQL injection attack. Note the ? mark in the query above. When you do your prepare you'll change it to this...

    $q = $conn->prepare($sql, array($userid));

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Michael

    Thank you very much for your reply. It did not work with the ?, however when I changed the ? to the $userid, it worked as I had hoped. I tried moving the prepare statement above the query, but that did not change the result.

    Again, thank you for your reply and your help.

    Gary

    ON Edit

    I just noticed that it is returning multiple results. I am getting 18 rows in the while loop, there are 3 rows in the gb_messages table and 6 rows in the pocontact table with the userid. Weird... any idea?

  4. #4
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,027
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    My mistake. The query values must be passed at execute time. Hence..

    Code php:
    $q = $conn->prepare($sql);
    $q->execute(array($userid));

    Where the SQL text has the question mark as outlined above.

    More information found here.

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Michael

    That worked, however I am still getting the multiple returns (should be 3, getting 18).

    gary

  6. #6
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,027
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Without knowing the database structure I can't help you. Also, the majority of this question seems SQL related, so the database forum might be a better place for the question.

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Triple Results from MySQL SELECT

    I have a query that is returning triple results. Can anyone suggest a solution?

    PHP Code:
    $sql="SELECT 
      pocontact.recipient_fn, pocontact.recipient_mi, pocontact.recipient_ln, gb_messages.msg_date, gb_messages.msg_id
    FROM pocontact 
    JOIN gb_messages ON (gb_messages.userid = pocontact.userid)
    WHERE gb_messages.userid = ?  "
    ;


    $q $conn->prepare($sql);
    $q->execute(array($userid));
    } catch (
    PDOException $e) {
        print 
    "Error!: " $e->getMessage() . "<br/>";
        die(
    'Ooops');


  8. #8
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi Gary,

    I've moved this post to the Database & MySQL forum as you will likely need some additional help with your SQL

    Steve
    ictus==""

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    er.. yeah. Based on your initial query, my question is this:

    what links the two tables?

    Your results are behaving like you havent specified a join relation...
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  10. #10
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My original thread was moved to this forum, sorry for the duplication.

    Gary

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know how to answer your question. The tables are InnoDB engines and they are joined by a Foreign Key, I don't know if that helps your understanding of it.

    Gary

  12. #12
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Easiest thing to help us understand would be to give us the schema (design) of those two tables.

    (DESCRIBE pocontact; DESCRIBE gb_messages; will spit out your structure.)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  13. #13
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Describe gets you

    DESCRIBE pocontact;# Rows: 17
    DESCRIBE gb_messages;# Rows: 9

    However an export gets you
    Code:
    CREATE TABLE IF NOT EXISTS `pocontact` (
        `userid` int(25) NOT NULL,
        `poc_id` varchar(20) NOT NULL,
        `recipient_fn` varchar(30) NOT NULL,
        `recipient_mi` varchar(4) default NULL,
        `recipient_ln` varchar(50) NOT NULL,
        `relation` varchar(30) NOT NULL,
        `recip_email` varchar(50) NOT NULL,
        `conf_recip_email` varchar(50) NOT NULL,
        `usps_mail` varchar(50) NOT NULL,
        `usps_mail2` varchar(50) default NULL,
        `city` varchar(40) NOT NULL,
        `state` varchar(20) NOT NULL,
        `zip` varchar(12) NOT NULL,
        `country` varchar(25) NOT NULL,
        `phone` varchar(15) NOT NULL,
        `submitted` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
        `ip` varchar(20) NOT NULL,
        KEY `userid` (`userid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE IF NOT EXISTS `gb_messages` (
        `userid` int(25) NOT NULL,
        `msg_id` varchar(20) NOT NULL,
        `poc_id` varchar(20) NOT NULL,
        `msg_date` varchar(20) NOT NULL,
        `type` varchar(20) NOT NULL,
        `salutation` varchar(30) NOT NULL,
        `message` varchar(20000) NOT NULL,
        `submitted` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
        `ip` varchar(20) NOT NULL,
        KEY `userid` (`userid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Last edited by ServerStorm; Jan 28, 2013 at 22:53.

  14. #14
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    So... yeah. Michael's query is pretty much exactly what you want...
    Code MySQL:
    SELECT 
      pocontact.recipient_fn, pocontact.recipient_mi, pocontact.recipient_ln, gb_messages.msg_date, gb_messages.msg_id
    FROM pocontact 
    JOIN gb_messages ON (gb_messages.userid = pocontact.userid) AND gb_messages.userid = ?
    ORDER BY gb_messages.msg_date DESC
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  15. #15
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,136
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    The issue you are having is you have one record in your pocontact table that you are associating to multiple records for that given user in the gb_messages table. You are then seeing the user data repeated for each record of the gb_messages table.

    The only way around this is to perform two separate queries to get your data, one for the user data and the other for the messages. Granted, I don't really recommend that, instead just loop through your existing results and only output new user data when you see a different username in the returned result set

  16. #16
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The query results in triple records being displayed. Each message has a msg_id (a unique number for each message). The results list the msg_id 3 times and mixes the other columns. So the first 3 results are as follows

    Sam J. Wart January 28, 2013 1359393578 <=msg_id
    sdgfsdgf NULL fgsd January 28, 2013 1359393578
    recipient_fn NULL recipient_ln January 28, 2013 1359393578

    The next six results are the same except that the msg_id is that of the next record.

    Can anyone see where I am going wrong?

    Thank you for your help.

    Gary

    On Edit: Sorry, the results lost formatting on the post to the board,

  17. #17
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,235
    Mentioned
    239 Post(s)
    Tagged
    1 Thread(s)
    The only thing I can think of is that we're not using the right field for the join... It is logical to think that userid is the primary key in one table and the foreign key on the other... but maybe the database was structured to be joined using poc_id which is the other field common to both tables?

    edit: I changed @StarLion ;'s query and changed the "AND" for a "WHERE". Try that. Else, try using the poc_id to create the relationship between both tables.
    Code:
    
    SELECT    pocontact.recipient_fn, pocontact.recipient_mi, pocontact.recipient_ln,
     gb_messages.msg_date, gb_messages.msg_id FROM pocontact JOIN gb_messages 
    ON (gb_messages.userid = pocontact.userid) 
    WHERE gb_messages.userid = ? ORDER BY gb_messages.msg_date DESC

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the easiest way to debug this multiplicity is to use SELECT * to retrieve certain rows from one table (using for example msg_id 1359393578) and then in a separate query use SELECT * to retrieve the rows that you think are supposed to be related (using whatever values the first query returned for the join column) from the other table

    that'll tell you what's what -- either you're joining on the wrong column, or there are actually dupes in your data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    these two threads should really be merged, mr advisor
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,235
    Mentioned
    239 Post(s)
    Tagged
    1 Thread(s)
    Off Topic:

    Threads have been merged

  21. #21
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    SEPA
    Posts
    100
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you to everyone that helped. I went off of molona's suggestion and tied the tables by the poc_id instead of the primary key. The query that works is below.

    Again thank you to everyone that helped.

    gary

    PHP Code:
    SELECT    pocontact.recipient_fnpocontact.recipient_mipocontact.recipient_ln,
     
    gb_messages.msg_dategb_messages.msg_id FROM pocontact  JOIN gb_messages 
    ON 
    (gb_messages.poc_id pocontact.poc_id

    WHERE gb_messages.userid =? 


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
  •