Issues

ZF-2112: Zend_Db_Adapter_Interface::fetchCol() does not properly retrieve a column on single column SQL statements.

Description

When using a Mysqli adapter(not sure if this occurs with others) the following produces an error:



Warning: mysqli_stmt::bind_param() [function.mysqli-stmt-bind-param]: Number of variables doesn't match number of parameters in prepared statement in /usr/home/sciworld/application/library/Zend/Db/Statement/Mysqli.php on line 241

This occurs independent of if a column index is provided.

Interestingly, the following code does work correctly:

$stmnt = $db->query("SELECT areaID FROM userPermissions WHERE userID='$userID'"); $column = $stmnt->fetchColumn();```

It seems perhaps the parsing of the statement in the first case is not functioning properly, although admittedly I don't have much experience with Zend_Db. I do know the documentation does indicate that the first case should work identically to the second, which is not the case(unless I am missing something here).

Comments

This issue should have been fixed for the 1.5 release.

Please categorize/fix as needed.

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

What's the final SQL query? What does $userID resolve to? I can't reproduce this here but if you can supply any additional information it will help immensely.

It appears that this is still happening (verified in 1.5.2) if the where statement is searching for a column with an id of 0 and the column being returned has a 0 in it.

Repro:


mysql> CREATE TABLE cds_delete ( id SMALLINT NOT NULL DEFAULT 0, name VARCHAR(10) NOT NULL DEFAULT '') ENGINE=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO cds_delete VALUES (0, 'zero'), (1, 'one'), (2, 'two');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from cds_delete;
+----+------+
| id | name |
+----+------+
|  0 | zero | 
|  1 | one  | 
|  2 | two  | 
+----+------+
3 rows in set (0.00 sec)

<?php
require_once 'Zend/Db.php';
$db = Zend_Db::factory('Mysqli', array(...));

var_dump(
    $db->fetchCol("SELECT id FROM cds_delete WHERE id IN (0, 1, 2)"),
    $db->fetchCol("SELECT id FROM cds_delete WHERE id IN (1, 2)"),
    $db->fetchCol("SELECT name FROM cds_delete WHERE id IN (0, 1, 2)"),
    $db->fetchCol("SELECT name FROM cds_delete WHERE id IN (1, 2)")
);
?>

Expected Result:


array(3) {
  [0]=>
  int(0)
  [1]=>
  int(1)
  [2]=>
  int(2)
}
array(2) {
  [0]=>
  int(1)
  [1]=>
  int(2)
}
array(3) {
  [0]=>
  string(4) "zero"
  [1]=>
  string(3) "one"
  [2]=>
  string(3) "two"
}
array(2) {
  [0]=>
  string(3) "one"
  [1]=>
  string(3) "two"
}

Actual Result:


array(0) {
}
array(2) {
  [0]=>
  int(1)
  [1]=>
  int(2)
}
array(3) {
  [0]=>
  string(4) "zero"
  [1]=>
  string(3) "one"
  [2]=>
  string(3) "two"
}
array(2) {
  [0]=>
  string(3) "one"
  [1]=>
  string(3) "two"
}

Notice that the first fetchCol is returning an empty arrays instead of the expected results.

I traced this issue down to the Zend_Db_Statement::fetchAll function and determined this is due to improper type checking.


    public function fetchAll($style = null, $col = null)
    {
        $data = array();
        if ($style === Zend_Db::FETCH_COLUMN && $col === null) {
            $col = 0;
        }
        if ($col === null) {
            while ($row = $this->fetch($style)) {
                $data[] = $row;
            }
        } else {
            while ($val = $this->fetchColumn($col)) {
                $data[] = $val;
            }
        }
        return $data;
    }

Notice that both {{while}} loops are not checking the types returned from {{fetchColumn}} and {{fetch}}, therefore anything that could evaluate to a false will cause the {{while}} loop to terminate. This is why when the integer 0 is being returned by the {{fetchColumn}} method, nothing is being returned.

Updating the code to verify that a boolean false was returned appears to fix this issue (I can only vouch that this fixes the fetchCol issue, I didn't bother to look into what {{fetch}} should/could return so that should still be verified).


    public function fetchAll($style = null, $col = null)
    {
        $data = array();
        if ($style === Zend_Db::FETCH_COLUMN && $col === null) {
            $col = 0;
        }
        if ($col === null) {
            while (($row = $this->fetch($style)) !== false) {
                $data[] = $row;
            }
        } else {
            while (($val = $this->fetchColumn($col)) !== false) {
                $data[] = $val;
            }
        }
        return $data;
    }

This issue has gone unaddressed for too long. I'm re-assigning to Ralph for re-evaluation and categorization.

Will evaluate within 2 weeks

Cole Snodgrass solution is incorrect for Mysqli adapter. False is returned only when error will occurre, when stmt cursor reaches the end of data NULL will be returned. More about this here: http://php.net/manual/en/mysqli-stmt.fetch.php

Proper solution:


public function fetchAll($style = null, $col = null)
{
    $data = array();
    if ($style === Zend_Db::FETCH_COLUMN && $col === null) {
        $col = 0;
    }
    if ($col === null) {
        while ($row = $this->fetch($style)) {
            $data[] = $row;
        }
    } else {
        while (false !== $val = $this->fetchColumn($col)) {
            if ($val === null)
                break;
            $data[] = $val;
        }
    }
    return $data;
}

can not reproduce

Reopen for ZF-6788

add "false !== " condition to "$val = $this->fetchColumn($col)".

But I think the problem with mysql should be issue on fetch() method.