strange output from $MYSQLi->fetch_row()

Question

Here is the MYSQLi code:

  public function display() {
    $sql = "SELECT title, date_posted, text, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";
    $results = $this->query($sql, "ii",
                              $this->page_offset,
                              $this->notes_per_page);
    $results = $this->db->store_result();
    while ($row = $results->fetch_row()) {
      var_dump($row);
    }
    //$this->write($results);
  }

  // this is the $this->db->query() function referred to above.
  public function query() {
    $args = func_get_args();
    $statement = $this->db->prepare($args[0]);
    $args = array_slice($args, 1);
    call_user_func_array(array($statement, 'bind_param'), &$args);
    $statement->execute();
    return $statement;
  }

The MYSQL table:

mysql> desc notes;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| date_posted | date         | NO   |     | NULL    |                |
| title       | varchar(255) | NO   |     | NULL    |                |
| text        | longblob     | NO   |     | NULL    |                |
| url         | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

A sample row:

mysql> SELECT title, url, date_posted FROM notes WHERE url='init';
+-------+------+-------------+
| title | url  | date_posted |
+-------+------+-------------+
| init  | init | 2011-02-16  |
+-------+------+-------------+
1 row in set (0.00 sec)

The output for the corresponding row. What in the world...?:

array(4) { [0]=> string(0) "" [1]=> string(0) "" [2]=> string(4) "init" [3]=> string(4) "�" }

If I switch fetch_array() to fetch_object(), I get this:

object(stdClass)#3 (4) { ["title"]=> string(0) "" ["date_posted"]=> string(0) "" ["text"]=> string(4) "init" ["url"]=> string(4) "�" }

Thank you for all and any help/suggestions/comments!

New observation:

Adding another column to the query outputs a new column (the wrong one again though). So for example, if:

// and yes, I do realize that I am repeating 'url',
// and I have no clue why this is happening.
$sql = "SELECT title, date_posted, text, url, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";

Then the output row is:

array(5) { [0]=> string(0) "" [1]=> string(0) "" [2]=> string(4) "init" [3]=> string(4) "�" [4]=> string(350) "

This is the text for the init article. I cut it short for the sake of brevity in this stackoverflow question " }

Answer 1

    public function display() {
    $sql = "SELECT title, date_posted, text, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";
    $results = $this->query($sql, "ii",
                              $this->page_offset,
                              $this->notes_per_page);
    var_dump($results);
  }



 public function query() {
    $args = func_get_args();
    $statement = $this->db->prepare($args[0]);
    $args = array_slice($args, 1);
    call_user_func_array(array($statement, 'bind_param'), &$args);
    $statement->execute();
    $return = array();
$statement->store_result();
$row=array();
$data = $statement->result_metadata();
$fields = array();
$fields[0] = &$statement;
while($field = $data->fetch_field()) {
$fields[] = &$row[$field->name];
}
call_user_func_array("mysqli_stmt_bind_result", $fields);

$i = 0;
while ($statement->fetch()) {
foreach ($row as $key1=>$value1) $return[$i][$key1] = $value1;
$i++;
}
$statement->free_result();
return $return;


  }

Answer 2

There is a chance that because you're blowing away the statement object (you delete it by reassigning the variable that refers to it) it's getting garbage collected and affecting the result. Try changing your code like so:

 public function display() {
    $sql = "SELECT title, date_posted, text, url
              FROM notes ORDER BY date_posted DESC
              LIMIT ?, ?";
    // Notice the changed variable name
    $stmt = $this->query($sql, "ii",
                              $this->page_offset,
                              $this->notes_per_page);
    $results = $this->db->store_result();
    while ($row = $results->fetch_row()) {
      var_dump($row);
    }
    //$this->write($results);
  }

Answer 3

(edit: oops I misread part of your code. Anyway...)

Before fetching data from a prepared statement, you have to bind variables to fetch into, using mysqli_stmt::bind_result(). Then, you fetch each row using mysqli_stmt::fetch().

public function display() {     
    $sql = "SELECT title, date_posted, text, url
            FROM notes ORDER BY date_posted DESC
            LIMIT ?, ?";
    $results = $this->query($sql, "ii", 
                      $this->page_offset,
                      $this->notes_per_page);

    /* We have to BIND variables to store the result in */
    $row = new StdClass;
    $row->title = null;
    $row->date_posted = null;
    $row->text = null;
    $row->url = null;
    $results->bind_results($row->title, $row->date_posted, $row->text, $row->url);

    while (($status = $results->fetch()) === true) { 
        var_dump($row):
    }
    if($status === false) die("Error fetching data");
}  

Answer 4

To be honest, I could not get your code working - store_result() keeps returning (bool)false. Though, this worked fine for me:

class Sandbox {

    private $db;
    private $page_offset = 0;
    private $notes_per_page = 10;

    public function __construct() {
        $this->db = new mysqli('127.0.0.1', 'user', 'pass', 'test');
    }

    /**
     */
    public function display() {
        $sql = "
            SELECT title, date_posted, text, url
            FROM notes
            ORDER BY date_posted DESC
            LIMIT ?, ?
        ";
        $statement = $this->query($sql, 'ii', $this->page_offset, $this->notes_per_page);
        $results = $statement->get_result();
        while ($row = $results->fetch_row()) {
            var_dump($row);
        }
    }

    /**
     * @param string $sql
     * @param string $types
     * @param mixed  $arg,...
     * @return mysqli_stmt
     */
    public function query($sql, $types = null, $arg = null, $arg = null) {
        $args = func_get_args();
        array_shift($args);

        $statement = $this->db->prepare($sql);
        call_user_func_array(array($statement, 'bind_param'), &$args);
        $statement->execute();

        return $statement;
    }

}

$notes = new Sandbox();
$notes->display();

Output:

array(4) {
  [0]=>
  string(4) "init"
  [1]=>
  string(10) "2011-02-16"
  [2]=>
  NULL
  [3]=>
  string(4) "init"
}