get array of rows with mysqli result

Question 1

I need to get all the rows from result object. I’m trying to build a new array that will hold all rows.

Here is my code:

$sql = new mysqli($config['host'],$config['user'],$config['pass'],$config['db_name']);
if (mysqli_connect_errno())
{
    printf("Connect failed: %sn", mysqli_connect_error());
    exit();
}
$query = "SELECT domain FROM services";
$result = $sql->query($query);           
while($row = $result->fetch_row());
{
    $rows[]=$row;
}
$result->close();
$sql->close();
return $rows;

$rows is supposed to be the new array that contains all, rows but instead I get an empty array.

Any ideas why this is happening?

Question 2

You had a slight syntax problem, namely an errant semi-colon.

while($row = $result->fetch_row());

Notice the semi-colon at the end? It means the block following wasn't executed in a loop. Get rid of that and it should work.

Also, you may want to check that the query actually works:

$sql = new mysqli($config['host'], $config['user'], $config['pass'], $config['db_name']);
if (mysqli_connect_errno()) {
  printf("Connect failed: %sn", mysqli_connect_error());
  exit;
}
$query = "SELECT domain FROM services";
$result = $sql->query($query);     
if (!$result) {
  printf("Query failed: %sn", $mysqli->error);
  exit;
}      
while($row = $result->fetch_row()) {
  $rows[]=$row;
}
$result->close();
$sql->close();
return $rows;

Question 3

I went ahead and ran a test where one query uses a prepared statement, and the other builds the entire query then executes that. I'm probably not making what I'm wanting to know easy to understand.

Here's my test code. I was thinking prepared statements sort of held back execution until a $stmt->close() was called to optimize it or something. That doesn't appear to be the case though as the test that builds the query using real_escape_string is at least 10 times faster.

<?php

$db = new mysqli('localhost', 'user', 'pass', 'test');

$start = microtime(true);
$a = 'a';
$b = 'b';

$sql = $db->prepare('INSERT INTO multi (a,b) VALUES(?, ?)');
$sql->bind_param('ss', $a, $b);
for($i = 0; $i < 10000; $i++)
{
    $a = chr($i % 1);
    $b = chr($i % 2);
    $sql->execute();
}
$sql->close();

echo microtime(true) - $start;

$db->close();

?>

Question 4

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

If your data combined is under that "max_allowed_packet" threshold, just use "s" for the binding type for any text field. Infact, you can usually get away with using "s" for any field type at all (date, float, etc).

If your entire entry combined that you want to insert is over 1mb (or whatever you reset it to) in length, you'll want to use mysqli_stmt::send_long_data method and the "b" binding type to send this particular field in chunks.

Question 5

The mysqli_stmt_bind_result on this line:

call_user_func_array(mysqli_stmt_bind_result, $pointers);

should be quoted:

call_user_func_array('mysqli_stmt_bind_result', $pointers);

You could also use PDO, an untested example but it should work:

public function getImageResults ($search_term)
{
    $sql =
    "
        SELECT 
            Images.url as url
        FROM 
            Images, ImageSearch, ImageSearchResults 
        WHERE 
            ImageSearch.search_string = ? AND
            ImageSearchResults.search_id = ImageSearch.id AND
            ImageSearchResults.image_id = Images.id AND

            Images.deleted = 0 AND
            ImageSearch.deleted = 0 AND
            ImageSearchResults.deleted = 0
    ";
    $pdo = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'username', 'password');

    $sth = $pdo->prepare($sql);
    $sth->execute(array($search_term));

    $result = $sth->fetchAll(PDO::FETCH_CLASS, "ArrayObject");

    //var_dump($result);//debug

    return $result;
}

Question 6

PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0

You aren't fetching the row-count at all.

SELECT COUNT(*) FROM coursescompleted where person=:p

This query would return total rows in $rows[0];

EDIT: Please see @ray's answer. using count(id) is better than count(*) for InnoDB.

You could get row-count in the following manner, from your earlier query.

$row_count = $result->rowCount();

But be warned:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Documentation

get array of rows with mysqli result

Answers

php,mysqli