PDO weirdness

PHP
1

I’ve only actually made a point of getting to grips with PHP’s PDO library in the last week, and this morning have run into a strange problem which I’m hoping someone can shed some light on for me.

FWIW, I’m using ODBC to connect to SQL Server 2005.

It seems that when I bind a value to a parameter in a join condition, if the value is a string, it is converted to the TEXT datatype which is incompatible in SQL Server with the equality operator. But as soon as the parameter is moved into the query’s WHERE clause, it executes fine.

Here’s some code which gives an example of this happening.


$query = $db->prepare('SELECT * FROM posts_tags WHERE tag = :tag');
$query->bindValue(':tag', 'example', PDO::PARAM_STR);
$query->execute(); // works fine

$query = $db->prepare('SELECT * FROM posts p INNER JOIN posts_tags t ON p.id = t.post_id WHERE t.tag = :tag');
$query->bindValue(':tag', 'example', PDO::PARAM_STR);
$query->execute(); // works fine

$query = $db->prepare('SELECT * FROM posts p INNER JOIN posts_tags t ON p.id = t.post_id AND t.tag = :tag');
$query->bindValue(':tag', 'example', PDO::PARAM_STR);
$query->execute(); // produces below error

Error message:

SQLSTATE[42000]: Syntax error or access violation: 402 [Microsoft][ODBC  SQL Server Driver][SQL Server]The data types varchar and text are  incompatible in the equal to operator. (SQLExecute[402] at  ext\\pdo_odbc\\odbc_stmt.c:133)

Does anyone know how I can stop this error from occurring?

2

Ha! That’s me told. :smiley:

Oh well, back to the ol’ drawing board.

However, I think using a conditional outside of a WHERE clause seems weird. Not that that’s your fault. :stuck_out_tongue:

3

It’s perfectly valid SQL :slight_smile: The AND… is the second condition of the join. Tested outside of PHP with no problems.

4

The latter query is invalid, I think! :slight_smile:

You have an AND and no WHERE clause (which you’ve pointed out), which is invalid SQL no ? Does the query execute fine outside of PHP with the sample data provided?

5

I’m going to have to disagree with you on this one.

If I were to change the INNER JOIN to a LEFT JOIN, the condition would have to form part of the join, as putting it in the WHERE clause would exclude all rows from the left table with no matching row in the right table.

I prefer to group conditions together like this anyway, even when using an INNER JOIN, as it makes the query easier to read IMO.