php pdo prepare repetitive variables
The simple answer is: You can't. PDO uses an abstraction for prepared statements which has some limitations. Unfortunately this is one, you have to work-around using something like
$query = "UPDATE users SET firstname = :name1 WHERE firstname = :name2";
$stmt = $dbh -> prepare($query);
$stmt -> execute(array(":name1" => "Jackie", ":name2" => "Jackie"));
In certain cases, such as emulated prepared statements with some versions of the PDO/MySQL driver, repeated named parameters are supported; however, this shouldn't be relied upon, as it's brittle (it can make upgrades require more work, for example).
If you want to support multiple appearances of a named parameter, you can always extend PDO and PDOStatement (by classical inheritance or by composition), or just PDOStatement and set your class as the statement class by setting the PDO::ATTR_STATEMENT_CLASS
attribute. The extended PDOStatement (or PDO::prepare
) could extract the named parameters, look for repeats and automatically generate replacements. It would also record these duplicates. The bind and execute methods, when passed a named parameter, would test whether the parameter is repeated and bind the value to each replacement parameter.
Note: the following example is untested and likely has bugs (some related to statement parsing are noted in code comments).
class PDO_multiNamed extends PDO {
function prepare($stmt) {
$params = array_count_values($this->_extractNamedParams());
# get just named parameters that are repeated
$repeated = array_filter($params, function ($count) { return $count > 1; });
# start suffixes at 0
$suffixes = array_map(function ($x) {return 0;}, $repeated);
/* Replace repeated named parameters. Doesn't properly parse statement,
* so may replacement portions of the string that it shouldn't. Proper
* implementation left as an exercise for the reader.
*
* $param only contains identifier characters, so no need to escape it
*/
$stmt = preg_replace_callback(
'/(?:' . implode('|', array_keys($repeated)) . ')(?=\W)/',
function ($matches) use (&$suffixes) {
return $matches[0] . '_' . $suffixes[$matches[0]]++;
}, $stmt);
$this->prepare($stmt,
array(
PDO::ATTR_STATEMENT_CLASS => array('PDOStatement_multiNamed', array($repeated)))
);
}
protected function _extractNamedParams() {
/* Not actually sufficient to parse named parameters, but it's a start.
* Proper implementation left as an exercise.
*/
preg_match_all('/:\w+/', $stmt, $params);
return $params[0];
}
}
class PDOStatement_multiNamed extends PDOStatement {
protected $_namedRepeats;
function __construct($repeated) {
# PDOStatement::__construct doesn't like to be called.
//parent::__construct();
$this->_namedRepeats = $repeated;
}
/* 0 may not be an appropriate default for $length, but an examination of
* ext/pdo/pdo_stmt.c suggests it should work. Alternatively, leave off the
* last two arguments and rely on PHP's implicit variadic function feature.
*/
function bindParam($param, &$var, $data_type=PDO::PARAM_STR, $length=0, $driver_options=array()) {
return $this->_bind(__FUNCTION__, $param, func_get_args());
}
function bindValue($param, $var, $data_type=PDO::PARAM_STR) {
return $this->_bind(__FUNCTION__, $param, func_get_args());
}
function execute($input_parameters=NULL) {
if ($input_parameters) {
$params = array();
# could be replaced by array_map_concat, if it existed
foreach ($input_parameters as $name => $val) {
if (isset($this->_namedRepeats[$param])) {
for ($i=0; $i < $this->_namedRepeats[$param], ++$i) {
$params["{$name}_{$i}"] = $val;
}
} else {
$params[$name] = $val;
}
}
return parent::execute($params);
} else {
return parent::execute();
}
}
protected function _bind($method, $param, $args) {
if (isset($this->_namedRepeats[$param])) {
$result = TRUE;
for ($i=0; $i < $this->_namedRepeats[$param], ++$i) {
$args[0] = "{$param}_{$i}";
# should this return early if the call fails?
$result &= call_user_func_array("parent::$method", $args);
}
return $result;
} else {
return call_user_func_array("parent::$method", $args);
}
}
}
PDO prepared statement with dynamic variables
You need to build the SQL using something like...
$insert = $this->db->prepare("
INSERT INTO
{$this->table}
($fields)
VALUES
($bindValues)
");
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 Prepared Statement with parameters array
You have a space at the end of this parameter name:
":pers_emp_pre_id "=>$data['pers_emp_pre_id']
^ here
Should be:
":pers_emp_pre_id"=>$data['pers_emp_pre_id']
PDO: Using same bind variable multiple times
Problem was the quotes around the :status. Removed quotes and all is good.
PDO PHP bindParam() repeated use of same parameters
Yes there is...
You can supply bindParam
as an array to the execute
function...
Something like this:
$statement->execute([
':username'=> $username,
':password'=> $password
]);
It's using bindParam
and execute
in just one statement, and it looks cleaner in my opinion.
Store PDO Values in PHP Variable
So you need to return it..
$st = $pdo->prepare("SELECT * FROM user_tbl WHERE user_id= :user_id");
$st->bindParam(':user_id', $user_id);
$st->execute();
$return = $st->fetch(PDO::FETCH_OBJ);
$return $return;
Then when calling your class in say index.php..
$var = new ClassName();
$callUser = $var->PublicFunctionTitle($user_id);
Then you can run things like <?php echo $callUser->status;?>
PHP PDO prepared statement IN() array
Here's some code I whipped up to simulate the desired outcome:
<?php
function preprepare(&$query, &$data) {
preg_match_all('/\?/', $query, $matches, PREG_OFFSET_CAPTURE);
$num = count($matches[0]);
for ($i = $num; $i;) {
--$i;
if (array_key_exists($i, $data) && is_array($data[$i])) {
$query = substr_replace($query, implode(',', array_fill(0, count($data[$i]), '?')), $matches[0][$i][1], strlen($matches[0][$i][0]));
array_splice($data, $i, 1, $data[$i]);
}
}
}
$query = 'SELECT * FROM `table` WHERE `col1` = ? AND `col2` IN(?) AND `col3` = ? AND `col4` IN(?)';
$data = array('foo', array(1, 2, 3), 'bar', array(4, 2));
preprepare($query, $data);
var_dump($query, $data);
?>
It outputs:
array (size=2)
0 => string 'SELECT * FROM `table` WHERE `col1` = ? AND `col2` IN(?,?,?) AND `col3` = ? AND `col4` IN(?,?)' (length=93)
1 =>
array (size=7)
0 => string 'foo' (length=3)
1 => int 1
2 => int 2
3 => int 3
4 => string 'bar' (length=3)
5 => int 4
6 => int 2
The query and data can then be used in a normal PDO prepared statement. I don't know if it accounts for all PDO magic niceness but it works pretty well so far.
Related Topics
PHP Get Url of Redirect from Source Url
How to Logout from a Session in PHP
Laravel: How to Change the Default Auth Password Field Name
PHP Create a Multidimensional Array from an Array with Relational Data
How to Check If a Row Exist in the Database Using Pdo
PHP Flush/Ob_Flush Not Working
How to Echo a MySQLi Prepared Statement
Laravel 5.4 on PHP 7.0: Pdo Exception - Could Not Find Driver (Mysql)
Soap PHP Fault Parsing Wsdl: Failed to Load External Entity
Convert Jpg Image to Gif, Png & Bmp Format Using PHP
How to Check If a Url Exists in PHP
PHP JSON Encode - Malformed Utf-8 Characters, Possibly Incorrectly Encoded
How to Put PHP Inside JavaScript
Linux and Oracle Instant Client