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>';
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…
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?
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.
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.
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;
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
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
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,
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.
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
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 =?