Multiple row query not working

[gwpaul]

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

 
    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>'; 

[Michael Morris]

I think you're misusing UNION. Try a join

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));

[gwpaul]

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?

[Michael Morris]

My mistake. The query values must be passed at execute time. Hence..

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

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

More information found here.

[gwpaul]

Michael

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

gary

[Michael Morris]

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.

[gwpaul]

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

$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');
} 

[ServerStorm]

Hi Gary,

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

Steve

[StarLion]

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...

[gwpaul]

My original thread was moved to this forum, sorry for the duplication.

Gary

[gwpaul]

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

[StarLion]

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.)

[gwpaul]

Describe gets you

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

However an export gets you

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;

[StarLion]

So... yeah. Michael's query is pretty much exactly what you want...

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

[cpradio]

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

[gwpaul]

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,

[molona]

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.

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

[r937]

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

[r937]

these two threads should really be merged, mr advisor

[molona]

Off Topic:

Threads have been merged

[gwpaul]

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

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.poc_id = pocontact.poc_id) 

WHERE gb_messages.userid =?