douzhuan1169
2017-04-30 04:30
浏览 47
已采纳

PHP / PDO - 使用变量赋值作为fetch-statement

Okay - so I have a custom function

selectQuery($query, $fetch = 'fetch', $rowCount = 1, 
              $onlyRowCount = false, $outputerror = false)

Which works fine. However, I have one particular query where the fetch-method is fetchAll(PDO::FETCH_COLUMN,0), and if I pass that as the $fetch-value in the function-call and try to use that in the output, it just returns NULL(because it doesn't work - the query works fine if run directly, or hard-coded).

So, what I'm asking, is there a way to use a value of a variable (which, at least to start with, is a string) to assign a fetch method?

I've tried the following:

$stmt->fetchAll(PDO::FETCH_COLUMN,0); //works, but is hard-coded

$stmt->$fetch //returns NULL

$stmt->{$fetch} //returns NULL

So, is there a way to use that variable $fetch as the assignment directly? Casting the variable to something, or some other way?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • donglizuo8892 2017-04-30 05:08
    已采纳

    This function is horrible. That long tail of parameters alone! And the lack of prepared statements. And a database related function that decides on its own whether to output an error or not, as though database interaction is something different from other code.

    Do yourself a favor, make this function this way

    function query($query, $parameters = []) {
        $pdo = // here your way of getting a PDO instance. 
        // DON't TELL ME YOU ARE CREATING A NEW ONE EVERY TIME
        if (!$parameters)
        {
             return $this->query($sql);
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute($parameters);
        return $stmt;
    }
    

    This is ALL you need and it's MUCH better than you have at the moment.

    Returning a statement is the key. It lets you to attach any fetch method you like to the function call - the most natural way of getting different result types from such a function. Or not to fetch anything at all, if a query happens to be UPDATE or INSERT.

    Want a row count?

    $count = query("DELETE FROM usars")->rowCount();
    

    want a fetch?

    $user = query("select * from users where id=?", [$id])->fetch();
    

    want fetchAll with PDO::FETCH_COLUMN? here you are

    $users = query("select name from users")->fetchAll(PDO::FETCH_COLUMN);
    

    Simple, usable, flexible, readable and secure.

    If you don't know how to make this function connect only once, here is a link to a simple PDO wrapper I wrote. Note the examples section. It is so exciting that I'd better put it here:

    # Table creation
    DB::query("CREATE temporary TABLE pdowrapper 
               (id int auto_increment primary key, name varchar(255))");
    
    # Prepared statement multiple execution
    $stmt = DB::prepare("INSERT INTO pdowrapper VALUES (NULL, ?)");
    foreach (['Sam','Bob','Joe'] as $name)
    {
        $stmt->execute([$name]);
    }
    $id = DB::lastInsertId());
    
    # Getting rows in a loop
    $stmt = DB::run("SELECT * FROM pdowrapper");
    while ($row = $stmt->fetch())
    {
        echo $row['name'], PHP_EOL;
    }
    
    # Getting one row
    $id  = 1;
    $row = DB::run("SELECT * FROM pdowrapper WHERE id=?", [$id])->fetch();
    
    # Getting single field value
    $name = DB::run("SELECT name FROM pdowrapper WHERE id=?", [$id])->fetchColumn();
    
    # Getting array of rows
    $all = DB::run("SELECT name, id FROM pdowrapper")->fetchAll(PDO::FETCH_KEY_PAIR);
    
    # Update
    $new = 'Sue';
    $count = DB::run("UPDATE pdowrapper SET name=? WHERE id=?", [$new, $id])->rowCount();
    
    打赏 评论
  • doucha7329 2017-04-30 04:51

    I think you want to do a variable function call. But the problem here is that you pass the function name fetchAll with its parameters (PDO::FETCH_COLUMN,0) as one argument to your selectQuery function.

    I think you need to pass the target function name as one argument, and the target function parameters as another argument.

    selectQuery($query, $fetch = 'fetch', $rowCount = 1, 
                  $onlyRowCount = false, $outputerror = false, $params= array())
    
    {...
        call_user_func_array($stmt->$fetch, $params);
    ...}
    

    https://secure.php.net/manual/en/function.call-user-func-array.php

    打赏 评论
  • douyi1084 2017-04-30 07:47

    This works in my test to run a different fetch method per the argument:

    function query($sql, $fetch) {
        $pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', 'xxxx');
        $stmt = $pdo->query($sql);
        $result = $stmt->$fetch();
        return $result;
    }
    

    (I'm not suggesting making a new connection for every query, that's just what I did for my test.)

    However, trying to make a dynamic call to control the fetch mode and other arguments would be more complex. I'd suggest creating a callable:

    function query($sql, $fetch, $setFetchModeArgs = null) {
        $pdo = new PDO('mysql:host=127.0.0.1;dbname=test', 'root', 'masterkey');
        $stmt = $pdo->query($sql);
        if ($setFetchModeArgs != null) {
            call_user_func_array([$stmt, "setFetchMode"], $setFetchModeArgs);
        }
        $result = $stmt->$fetch();
        return $result;
    }
    
    query("SELECT ...", "fetchAll", [PDO::FETCH_COLUMN, 0]);
    

    Anyway, that's how to make what you're trying to do work. But I don't think this is a good design.

    打赏 评论
  • douzhao2047 2017-05-01 01:48

    After rewieving the answer by @Your Common Sense, I ended up with this function inside my DB-class:

        public static function Query($query, $prepared = [], $outputerror = true) {
            /* Lets a user run a query directly via $core->Query()
            /* First variable is the actual query
            /* Second variable is the variables array (for prepared queries) [':var1'=>$var1],[$var1,$var2] and so on
            /* Third variable tells whether or not to output errormessages - default no (false)
            /* Use the function like $row = $core->Query(); Minimum you need to enter $query */
            $dbprefix = Config::read('dbprefix');
            $core = Core::getInstance();
            $stmt = '';
            if (!empty($query)) {
                if (!$prepared) {
                    $stmt = $core->dbh->query($query);
                    if ($stmt !== false) {
                        return $stmt;   
                    } elseif ($outputerror === true) {
                        echo displayErrors($stmt, true, __FILE__);
                    }
                } else {
                    $stmt = $core->dbh->prepare($query);
                    if ($stmt !== false) {
                        $stmt->execute($prepared);
                    } elseif ($outputerror === true) {
                        echo displayErrors($stmt, true, __FILE__);
                    }
                }
                return $stmt;
            }
        } // end function Query
    

    Works perfectly, involves a bit of a rewrite in my other files, but that was expected

    I've rewarded the answer to @Your Common Sense

    打赏 评论

相关推荐 更多相似问题