Variable getting dropped in PDO query

spufi

$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 even do a var_dump before the SQL statement and it looks fine.

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

I do one after the execute and suddenly the name part isn't there.

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

bradgrafelman

What is it that you're trying to do?

I ask, because what you're doing now doesn't make sense:

	$sql     = "SELECT name FROM topic WHERE $topicid = :id";
	$stmt    = $pdo->prepare($sql);
	$stmt->bindParam(':id', $topicid);

So your SQL query statement could basically be written as:

$sql     = "SELECT name FROM topic WHERE $topicid = $topicid";

which is the same as saying:

$sql     = "SELECT name FROM topic WHERE TRUE";

spufi

I'm grabbing the name from the topic table where the incoming topicid that was grabbed from the lookup posttopic table has to match the id found in the topic table.

I figured out the answer anyway.

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.

Roger_Ramjet

And why are you using multiple queries? Just select name along with the id in 1 query using a JOIN. Anything else is just silly.