Query doesnt work as expected

PHP
1

I’m confused, I am running this query on a mysql database using php

SELECT mess.subject, mess.topic_id, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, members.name, mess.display 
FROM messages AS mess 
INNER JOIN members ON members.id = mess.user_id 
WHERE mess.parent_id = 3 
OR mess.mess_id = 3 
AND mess.display = 1 
ORDER BY mess.created

I ran it in phpmyadmin and it seemed to work

Pasted image1920×1080 225 KB

heres my php

  try {

$stmt = $dbh->prepare('SELECT mess.subject, mess.topic_id, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, members.name, mess.display
FROM messages AS mess
INNER JOIN members 
ON members.id =  mess.user_id
WHERE mess.parent_id = :id
OR mess.mess_id = :id
AND mess.display = 1
ORDER BY mess.created');

$stmt->execute(array(
':id' => 3
));	

while($row = $stmt->fetch()) {
echo "<div class='panel panel-default'><div class='panel-heading'>";
echo "<span class='pull-right'>";
echo "<small>Post by ".$row['name'];
echo " on ".date( 'm/d/y g:i A', strtotime($row['created']))."</small>";
echo "</span>";
echo "<h3 class='panel-title' style='margin-right:175px; font-size:150%'>".$row['subject']."</h3>";
echo "</div>\n";
echo "<div class='panel-body comment more'>".$row['message_txt'];
echo "</div>";
echo "<div class='well'>\n";
echo "<form role='form' method='post' action='reply.php'>\n";
echo "<input type='hidden' value='{$_GET['id']}' name='parent_id'>";
echo "<input type='hidden' value='{$_GET['topic']}' name='topic'>";
echo "<input type='hidden' value='".$row['topic_id']."' name='topic_id'>";
echo "<input type='hidden' value='{$_SESSION['id']}' name='u_id'>";
echo "<div class='form-group'>";
echo "<textarea class='form-control' placeholder='comment' name='comment' rows='5'></textarea>";
echo "</div>\n";
echo "<button type='submit' class='btn btn-default'>Submit</button>";
echo "</form></div>\n";
}
}

This is the result though

<div class='panel panel-default'><div class='panel-heading'><span class='pull-right'><small>Post by tom on 01/02/15 9:48 AM</small></span><h3 class='panel-title' style='margin-right:175px; font-size:150%'>Where is the...?</h3></div>
<div class='panel-body comment more'>blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah blah  blah blah blah blah blah blah blah blah blah blah blah blah</div><div class='well well-sm'>Walk toward the El Camino Pool from the Tennis Courts and take a right<p class='text-right small'>vvv on 01/04/15 9:48 AM</p></div>
<div class='well well-sm'>Walk toward the El Camino Pool from the Tennis Courts and take a left<p class='text-right small'>vvv on 01/08/15 9:48 AM</p></div>
<div class='well'>
<form role='form' method='post' action='reply.php'>
<input type='hidden' value='3' name='parent_id'><input type='hidden' value='Welcome' name='topic'><input type='hidden' value='' name='topic_id'><input type='hidden' value='1' name='u_id'><div class='form-group'><textarea class='form-control' placeholder='comment' name='comment' rows='5'></textarea></div>
<button type='submit' class='btn btn-default'>Submit</button></form>

What confuses me is that the topic_id hidden element has no value, but the $row[‘name’] seems to work…

2

I am not seeing the field user_id in the messages table yet you are using it in your ON condition, i.e.
members.id = mess.user_id

Maybe I’m not seeing it in the screenshot.

Also you should use parentheses around those two WHERE OR conditions if also having other conditions e.g. AND

WHERE (mess.parent_id = :id
OR mess.mess_id = :id)
AND mess.display = 1
3

You can’t have two placeholders with the same name.

  try {

$stmt = $dbh->prepare('SELECT mess.subject, mess.topic_id, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, members.name, mess.display
FROM messages AS mess
INNER JOIN members 
ON members.id =  mess.user_id
WHERE mess.parent_id = :id
OR mess.mess_id = :id2
AND mess.display = 1
ORDER BY mess.created');

$stmt->execute(array(
':id' => 3,
':id2'=> 3
));
4

I know you can have the same placeholder name if you bind parameters, which is what I normally do. I don’t know about passing an array in execute as I don’t usually do that.

EDIT: I ran a little test and it worked just fine after changing

mess.user_id
TO
mess.parent_id

Full query used

$stmt = $dbh->prepare('SELECT mess.subject, mess.topic_id, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, members.name, mess.display
FROM messages AS mess
INNER JOIN members 
ON members.id =  mess.parent_id
WHERE (mess.parent_id = :id
OR mess.mess_id = :id)
AND mess.display = 1
ORDER BY mess.created');

$stmt->execute(array(
':id' => 1
));
5

You cant reuse your placeholder from what i’ve been told? The engine wont replace the second occurance…

6

Yes, Oddz and Starloin, you are correct about placeholders. Note, I didn’t get any errors/warnings with error reporting on.

7

What was your ?
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

8

Yes I had the following after DB connection

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

…and query in try catch

catch (PDOException $ex) {
   echo  $ex->getMessage();
 }

Not sure if emulation mode is on.

EDIT
Testing again with reused placeholders.

(mess.parent_id = :id
OR mess.mess_id = :id)

I changed DB table so the parent_id was NOT 1, but mess_id was 1.
I did NOT get a result from query. I changed INNER JOIN to LEFT JOIN and I did get a result for that second placeholder. So at least with my server settings, they are both being set and used.

9

thanks for all the responses, I really like this community.

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.