doufei7464
2012-02-16 19:58
采纳率: 0%
浏览 56
已采纳

具有k / v对和未知数的PDO?

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

图片转代码服务由CSDN问答提供 功能建议

我有一个关于PDO与数据库交谈的问题, 我熟悉的例子是:

  $ data = array('Cathy','9 Dark and Twisty Road','Cardiff');  
 
 $ STH = $ DBH->(“INSERT INTO folks(name,addr,city)值(?,?,?); 
 $ STH->执行($ data); 
   
 
 

但是,如果我们有ak / v对,它会是一样吗?ala

  $ data = array(' 一个'=>'Cathy','two'=>'9 Dark and Twisty Road','three'=>'Cardiff'); 
 
 $ STH = $ DBH->(“INSERT INTO 伙计(?,?,?)值(?,?,?); 
 $ STH->执行($ data); 
   
 
 

如果 我们有一个无法确定的数值吗?

  $ data = array(range(0,rand(1,99)); 
 
 $ STH = $ DBH-  >(“INSERT INTO伙计们(/ *你们怎么把东西放在这里?* /)值(/ *你们怎么把东西放在这里?* /); 
 $ STH->执行($ data); 
    
 
 

这让我更加困惑....

有人可以告诉我上面两个如何使用k / v对和未知计数?

非常感谢

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dslfq06464 2012-02-16 20:35
    已采纳

    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);
    }
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • duancong7358 2012-02-17 02:01

    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.')';
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题