MySQL PDO Name-Value Prepared Statement Using Last Parameter Only
Thanks for all the help everybody!
I went with Michael's solution, but tested Ryan's too.
i.e.
Update to note as solved. Using...
$stmt->execute($params); // scrap the foreach nonsense...
bindValue() rather than bindParam() is also appropriate.
To wrap things up, as per Ryan's comment, I'm pushing an answer out.
Thanks again!
PDO prepared statement with optional parameters
Some good old dynamic SQL query cobbling-together...
$sql = sprintf('SELECT * FROM user WHERE name LIKE :name %s %s',
!empty($_GET['city']) ? 'AND city = :city' : null,
!empty($_GET['gender']) ? 'AND gender = :gender' : null);
...
if (!empty($_GET['city'])) {
$stmt->bindParam(':city', '%'.$_GET['city'].'%', PDO::PARAM_STR);
}
...
You can probably express this nicer and wrap it in helper functions etc. etc, but this is the basic idea.
PHP PDO only last value of array gets inserted using bindValue & bindParam
This is only executing the insert with the last value as it's the last value that is bound to the statement. Call execute in each iteration of the loop.
foreach($this->array as $k => &$v) {
$sql->bindValue(":Name" , $v , PDO::PARAM_STR);
$sql->execute();
}
In PHP PDO, how can I bind named parameters of a prepared statement without knowing their names?
There's absolutely no reason to use bindParam
.
If your SQL has named placeholders then your array must be associative. You need to call it like this:
queryDB($query, ['name' => $name, 'age' => $age]);
You could then loop with foreach($params as $key => $value)
and use bindParam
but as I said, there's absolutely no reason to use it.
Instead, pass the array to execute
.
function queryDB(PDO $dbh, string $query, ?array $param = null)
{
$stmt = $dbh->prepare($query);
$stmt->execute($param);
return $stmt->fetchAll();
}
P.S. You can even remove the if
statement and the call to query
. This method does the same thing as prepare
and execute
. There's no reason to have a special case like this in your code.
PDO bindParam - last value inserted in all
Answered:
Missing execute
$result->execute();
PHP's PDO prepared statement: am I able to use one placeholder multiple times?
PDO::prepare states that
[y]ou cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.
Since it's generally better to leave emulation mode off (so the database does the prepared statement), you'll have to use id_0
, id_1
, etc.
PDO: Passing extra parameters to a prepared statment than needed
I got a chance to test my question, and the answer is you cannot send more parameters than the query uses. You get the following error:
PDOException Object
(
[message:protected] => SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
[string:Exception:private] =>
[code:protected] => HY093
[file:protected] => C:\Destination\to\file.php
[line:protected] => line number
[trace:Exception:private] => Array
(
[0] => Array
(
[file] => C:\Destination\to\file.php
[line] => line number
[function] => execute
[class] => PDOStatement
[type] => ->
[args] => Array
(
[0] => Array
(
[:u_ID] => 1
[:tid] => 1
[:current_time] => 1353524522
)
)
)
[1] => Array
(
[file] => C:\Destination\to\file.php
[line] => line number
[function] => function name
[class] => class name
[type] => ->
[args] => Array
(
[0] => SELECT
column
FROM
table
WHERE
user_ID = :u_ID AND
thread_ID = :tid
[1] => Array
(
[:u_ID] => 1
[:tid] => 1
[:current_time] => 1353524522
)
)
)
)
[previous:Exception:private] =>
[errorInfo] => Array
(
[0] => HY093
[1] => 0
)
)
I don't know a huge amount about PDO, hence my question, but I think that because :current_time is sent but not used and the error message is "Invalid parameter number: parameter was not defined" you cannot send extra parameters which are not used.
Additionally the error code HY093 is generated. Now I can't seem to find any documentation explaining PDO codes anywhere, however I came across the following two links specifically about HY093:
What is PDO Error HY093
SQLSTATE[HY093]
It seems HY093 is generated when you incorrectly bind parameters. This must be happening here because I am binding too many parameters.
Can PHP PDO Statements accept the table or column name as parameter?
Table and Column names CANNOT be replaced by parameters in PDO.
In that case you will simply want to filter and sanitize the data manually. One way to do this is to pass in shorthand parameters to the function that will execute the query dynamically and then use a switch()
statement to create a white list of valid values to be used for the table name or column name. That way no user input ever goes directly into the query. So for example:
function buildQuery( $get_var )
{
switch($get_var)
{
case 1:
$tbl = 'users';
break;
}
$sql = "SELECT * FROM $tbl";
}
By leaving no default case or using a default case that returns an error message you ensure that only values that you want used get used.
PDO using quotes in prepared statements to call mysql procedure?
You can't bind multiple parameters to a single parameter. That would defeat the level of protection parameter binding provides. Therefore, you only have one parameter in your procedure available, but you're telling your prepare statement to accept two more parameters with ?, ?
.
You shouldn't need separate variables for your binding. Just put them together as comma separated values in PHP:
$binding = "$phone,$email";
$s = self::$handle->prepare("CALL inTable('users','phone,email', ?);");
$s->bindParam(1, $binding, PDO::PARAM_STR);
Related Topics
How to Ipc Between PHP Clients and a C Daemon Server
How to Read Xmp Data from a Jpg with PHP
What Is the Use of <<<Eod in PHP
Fastest Way to Extract a Specific Frame from a Video (Php/Ffmpeg/Anything)
How to Save Changed Simplexml Object Back to File
Laravel Gmail Not Working, "Username and Password Not Accepted. Learn More..."
How To: Install Memcache on Xampp (Windows 7/8/10)
"Usort" a Doctrine\Common\Collections\Arraycollection
How to Disable Notices and Warnings in PHP Within the .Htaccess File
How to Do a Curl Request to the Same Server
Create Programmatically a Product Using Crud Methods in Woocommerce 3
Should I Be Using Assert in My PHP Code
How to Convert MySQL Time to Unix Timestamp Using PHP
PHP Float with 2 Decimal Places: .00