I have a question about PDO for talking to databases, the example I am familiar with is:
$data = array('Cathy', '9 Dark and Twisty Road', 'Cardiff');
$STH = $DBH->("INSERT INTO folks (name, addr, city) values (?, ?, ?);
$STH->execute($data);
But, if we had a k/v pair, would it be the same? ala
$data = array('one'=>'Cathy', 'two'=>'9 Dark and Twisty Road', 'three'=>'Cardiff');
$STH = $DBH->("INSERT INTO folks (?, ?, ?) values (?, ?, ?);
$STH->execute($data);
And what if we had a none ascertainable amount of values?
$data = array(range(0, rand(1,99));
$STH = $DBH->("INSERT INTO folks (/* how would you put stuff here? */) values (/* how would you put stuff here? */);
$STH->execute($data);
It leaves me more confused than not....
Could someone show me how the above two would work with k/v pairs and unknown counts?
Much thanks
Prepared statements only work with literals, not with identifiers. So you need to construct the SQL statement with the identifiers filled in (and properly escaped).
Properly escaping literals is tricky, though. PDO doesn't provide a method for doing literal-escaping, and MySQL's method of escaping literals (using `) is completely different from every other database and from the ANSI SQL standard. See this question for more detail and for workarounds.
If we simplify the issue of escaping the identifiers, you can use a solution like this:
// assuming mysql semantics
function escape_sql_identifier($ident) {
if (preg_match('/[\x00`\\]/', $ident)) {
throw UnexpectedValueException("SQL identifier cannot have backticks, nulls, or backslashes: {$ident}");
}
return '`'.$ident.'`';
}
// returns a prepared statement and the positional parameter values
function prepareinsert(PDO $pdo, $table, $assoc) {
$params = array_values($assoc);
$literals = array_map('escape_sql_identifier', array_keys($assoc));
$sqltmpl = "INSERT INTO %s (%s) VALUES (%s)";
$sql = sprintf($sqltmpl, escape_sql_identifier($table), implode(',',$literals), implode(',', array_fill(0,count($literals),'?'));
return array($pdo->prepare($sql), $params);
}
function prefixkeys($arr) {
$prefixed = array();
for ($arr as $k=>$v) {
$prefixed[':'.$k] = $v;
}
return $prefixed;
}
// returns a prepared statement with named parameters
// this is less safe because the parameter names (keys) may be vulnerable to sql injection
// In both circumstances make sure you do not trust column names given through user input!
function prepareinsert_named(PDO $pdo, $table, $assoc) {
$params = prefixkeys($assoc);
$literals = array_map('escape_sql_identifier', array_keys($assoc));
$sqltmpl = "INSERT INTO %s (%s) VALUES (%s)";
$sql = sprintf($sqltmpl, escape_sql_identifier($table), implode(',',$literals), implode(', ', array_keys($params)));
return array($pdo->prepare($sql), $params);
}
You don't have to use ? as the binding placeholder, you can use :names and an associative array. You can then pass the associative array as the binding list and PDO will now to match the keys of the array with the :binding_names. For example, with an associative array, if the keys match the fields in the database, you can do something like this:
$data = array('one'=>'Cathy', 'two'=>'9 Dark and Twisty Road', 'three'=>'Cardiff');
$fields = array_keys($data);
$field_str = '`'.implode('`,`',$fields).'`';
$bind_vals = ':'.implode(',:',$fields);
$sql = 'INSERT INTO tablename ('.$field_str.') VALUES ('.$bind_vals.')';
$sth = $dbh->prepare($sql);
$sth->execute($data);
That will handle an unknown number of name/value pairs. There is no getting around not knowing what field names to use for the insert. This example would also work with ? as the binding placeholder. So instead of names, you could just repeat the ?:
$bind_vals = str_repeat('?,', count($data));
$sql = 'INSERT INTO tablename ('.$field_str.') VALUES ('.$bind_vals.')';