• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • PDO::query()

    (PHP 5 >= 5.1.0, PHP 7, PECL pdo >= 0.2.0)

    执行 SQL 语句,以 PDOStatement 对象形式返回结果集

    说明

    publicPDO::query(string $statement): PDOStatement
    publicPDO::query(string $statement,int$PDO::FETCH_COLUMN,int $colno): PDOStatement
    publicPDO::query(string $statement,int$PDO::FETCH_CLASS,string $classname,array $ctorargs): PDOStatement
    publicPDO::query(string $statement,int$PDO::FETCH_INTO,object $object): PDOStatement

    PDO::query()在单次函数调用内执行 SQL 语句,以 PDOStatement 对象形式返回结果集(如果有数据的话)。

    如果反复调用同一个查询,用PDO::prepare()准备 PDOStatement 对象,并用PDOStatement::execute()执行语句,将具有更好的性能。

    如果没有完整获取结果集内的数据,就调用下一个PDO::query(),将可能调用失败。应当在执行下一个PDO::query()前,先用PDOStatement::closeCursor()释放数据库PDOStatement 关联的资源。

    Note:

    如果传入函数的参数数量超过一个,多余的参数将相当于调用结果对象PDOStatement::setFetchMode()方法。

    参数

    $statement

    需要准备、执行的 SQL 语句。

    查询里的数据应该用恰当的形式转义。

    返回值

    PDO::query()返回 PDOStatement 对象,或在失败时返回FALSE

    范例

    展示 PDO::query 的使用

    PDO::query()一个不错的功能是:执行 SELECT 语句,并能够循环遍历结果集。

    <?php
    function getFruit($conn) {
        $sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
        foreach ($conn->query($sql) as $row) {
            print $row['name'] . "\t";
            print $row['color'] . "\t";
            print $row['calories'] . "\n";
        }
    }
    ?>
    

    以上例程会输出:

    apple   red     150
    banana  yellow  250
    kiwi    brown   75
    lemon   yellow  25
    orange  orange  300
    pear    green   150
    watermelon      pink    90
    

    参见

    • PDO::exec() 执行一条 SQL 语句,并返回受影响的行数
    • PDO::prepare() 准备要执行的语句,并返回语句对象
    • PDOStatement::execute() 执行一条预处理语句
    The handling of errors by this function is controlled by the attribute PDO::ATTR_ERRMODE.
    Use the following to make it throw an exception:
    <?php
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    ?>
    
    > When query() fails, the boolean false is returned. 
    I think that is "Silent Mode".
    If that set attribute ErrorMode "Exception Mode"
    then that throw PDOException.
     $pdoObj = new PDO( $dsn, $user, $pass );
     $pdoObj->setAttribute("PDO::ATTR_ERRMODE", PDO::ERRMODE_EXCEPTION);
    After a lot of hours working with DataLink on Oracle->MySQL and PDO we (me and Adriano Rodrigues, that solve it) discover that PDO (and oci too) need the attribute AUTOCOMMIT set to FALSE to work correctly with.
    There's 3 ways to set autocommit to false: On constructor, setting the atribute after construct and before query data or initiating a Transaction (that turns off autocommit mode)
    The examples:
    <?php
    // First way - On PDO Constructor
    $options = array(PDO::ATTR_AUTOCOMMIT=>FALSE);
    $pdo = new PDO($dsn,$user,$pass,$options);
    // now we are ready to query DataLinks
    ?>
    <?php 
    // Second Way - Before create statements
    $pdo = new PDO($dsn,$user,$pass);
    $pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,FALSE);
    // or
    $pdo->beginTransaction();
    // now we are ready to query DataLinks
    ?>
    To use DataLinks on oci just use OCI_DEFAULT on oci_execute() function;
    Trying to pass like second argument PDO::FETCH_ASSOC it still work.
    So passing FETCH TYPE like argument seems work.
    This save you from something like:
    <?php
    $result = $stmt->setFetchMode(PDO::FETCH_NUM);
    ?>
    Example:
    <?php
    $res = $db->query('SELECT * FROM `mytable` WHERE true', PDO::FETCH_ASSOC);
    ?>
    
    If someone is suffering of the "MySQL server has gone away" problem after executing multiple queries, this is a solution that solved it for me. It's similar to the one needed for the exact same problem in mysqli.
    <?php
    $stmt=$db->prepare($query);
    $stmt->execute();
    do { $stmt->fetch(); $stmt->closeCursor(); ++$line; } while($stmt-
    >nextRowset());
    ?>
    I found this only works using prepare and execute this way, not if you 
    directly execute the query with query().
    The Documentation doesn’t mention this, but you can use a fetch style as a second parameter. For example:
    <?php
      $data=$pdo->query($sql,PDO::FETCH_NUM);
      foreach($data as $row) {
        //  etc
      }
    ?>
    

    上篇:PDO::prepare()

    下篇:PDO::quote()