Variable getting dropped in PDO query

PHP
1 

$sql  = "SELECT topicid FROM posttopic WHERE $id = :postid";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':postid', $id);
$stmt->execute();
while ($topic = $stmt->fetch(PDO::FETCH_ASSOC)) {
	$topicid = $topic['topicid'];
	$sql     = "SELECT name FROM topic WHERE $topicid = :id";
	$stmt    = $pdo->prepare($sql);
	$stmt->bindParam(':id', $topicid);
	echo "<p>SQL:  ".$sql."</p>";
	$stmt->execute();
}

The first SELECT works fine. It’s the second one that throws an exception.

I echo out the SQL statement and get…


SQL: SELECT name FROM topic WHERE 1 = :id

However, after the error is thrown, I get a SQL statement looking like…


SQL: SELECT name FROM topic WHERE = :id

Here’s the obvious error message…

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘= NULL’ at line 1’

I guess I’m trying to figure out how the $topicid value is getting dropped.

2

When you think about it, this makes sense or? :slight_smile:

While it might look like you have wasted time on this, you have actually learned something the “hard way” and you will never forget it.

On your code, if your using prepared statement, why do you create the statement over and over again? By doing that you dont take advantage of the prepare statement at all. You can move the statement outside the loop and keep the variable assignment and execution inside the loop.

Though, you can put your two queries as a linked one as well. Making the multiple secondary queries redundant.

3

(shakes fist)

Here’s the code change that works.


$sql  = "SELECT topicid FROM posttopic WHERE $id = :postid";
	$topics = $pdo->prepare($sql);
	$topics->bindParam(':postid', $id);
	$topics->execute();
	while ($topic = $topics->fetch(PDO::FETCH_ASSOC)) {
		$topicid = $topic['topicid'];
		$sql     = "SELECT name FROM topic WHERE $topicid = :id";
		$stmt    = $pdo->prepare($sql);
		$stmt->bindParam(':id', $topicid);
		$stmt->execute();
}

Now that I see what the change needed to be, it makes sense. $stmt is being used as part of the fetch so I can’t redefine what $stmt means while that is happening.

4

I went and added the var_dump after the execute statement and the dump looks like this…

array(1) { [“topicid”]=> string(1) “1” }

5

array(1) { [“topicid”]=> string(1) “1” } array(1) { [“name”]=> string(7) “General” }

Nothing looks to be null. The name value is actually correct, so I’m curious how the dump is getting the correct value, but yet somehow the code still gives the exception.

6

So wait:

$sql = “SELECT topicid FROM posttopic WHERE $id = [noparse]:p[/noparse]ostid”;

You’re dynamically assiging a field to filter on, then after that:

$stmt->bindParam(‘[noparse]:p[/noparse]ostid’, $id)

Using the SAME variable you used in the query to insert the field you’re filtering on?
Surely if $id = 1;, that would result in:

SELECT topicid FROM posttopic WHERE [b]1[/b] = [b]1[/b]";

I’m not sure that’s what you want to do :wink: Just pointing this out

7 
$sql  = "SELECT topicid FROM posttopic WHERE $id = :postid";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':postid', $id);
$stmt->execute();
while ($topic = $stmt->fetch(PDO::FETCH_ASSOC)) {
var_dump($topic);  // <--- this one here <---
    $topicid = $topic['topicid'];
    $sql     = "SELECT name FROM topic WHERE $topicid = :id";
    $stmt    = $pdo->prepare($sql);
    $stmt->bindParam(':id', $topicid);
    echo "<p>SQL:  ".$sql."</p>";
    $stmt->execute();
}

Add the line indicated, does it display NULL or does it display the topic id that you expect?