I'm updating some old PHP code and ran across an issue I don’t completely understand. In the old days of mysql_* functions you could include a variable in your SQL query like:
$query = "SELECT * FROM table $limit";
Where $limit = "LIMIT 0,50";. Thus the complete query was
$query = "SELECT * FROM table LIMIT 0,50";
And everything worked fine. However, with PDO prepared statements and named parameters, this type of simple substitution doesn't seem possible unless you break up the limit statement. For example:
$stmt = $conn->prepare('SELECT * FROM table :myLimit');
$stmt->execute(array(':myLimit'=>' LIMIT 0,50'));
Results in the error:
ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 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 '?' at line 1
(on a side note I find the error completely useless since there is no question mark being used – but back to the issue at hand)
But if I change that query to the following so that the LIMIT is broken down further:
$stmt = $conn->prepare('SELECT * FROM table LIMIT :start,:end ');
$stmt->execute(array(':start'=>0,':end'=>50));
It works great.
:myLimit as the named parameter and array(':myLimit'=>' LIMIT 0,50') as the value work?The PDO pages on php.net are a little ambiguous when it comes to what can and can’t be used as named parameters and I was looking for something a little more in-depth than what I found:
I'm currently using PHP 5.1.6
why doesn't using :myLimit as the named parameter and array(':myLimit'=>' LIMIT 0,50') as the value work?
Because prepared statements are for data only
What are the rules for using named parameters, and how do they differ from the simple variable substitution in the SQL string that the old mysql_* functions could use?
The rules are simple: you can use parameters (of either type) for the data only
I'm currently using PHP 5.1.6
Man. You know, you are a bit late with upgrade. Around ten years or so.
You cannot use a named parameter marker of the same name twice in a prepared statement.
In a modern versions you can.
You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.
That's true. Again because [native] prepared statement is for data literal only
When you bind a value, you can only include a value not any part of the query other than a value to be checked against.
Bound values are used to protect against query manipulation so if you could change the query or add extra mysql commands into the bound parameter then this would negate the whole point of binding a value
For example you can bind a name or number to check if something equals that value, you cannot bind a condition