Viewed   71 times

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());
$query = "SELECT domain FROM services";
$result = $sql->query($query);           
while($row = $result->fetch_row());
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?



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());
$query = "SELECT domain FROM services";
$result = $sql->query($query);     
if (!$result) {
  printf("Query failed: %sn", $mysqli->error);
while($row = $result->fetch_row()) {
return $rows;
Sunday, October 30, 2022

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.


$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);

echo microtime(true) - $start;


Sunday, November 6, 2022

This actually depends on the Mysql server. The default max size for all data combined in the entire query is 1mb. See:

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.

Wednesday, August 24, 2022

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 =
            Images.url as url
            Images, ImageSearch, ImageSearchResults 
            ImageSearch.search_string = ? AND
            ImageSearchResults.search_id = AND
            ImageSearchResults.image_id = AND

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

    $sth = $pdo->prepare($sql);

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


    return $result;
Monday, November 14, 2022

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.


Friday, October 7, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :