MySQLI Prepared Statement: num_rows & fetch_assoc

Question

Below is some poorly written and heavily misunderstood PHP code with no error checking. To be honest, I'm struggling a little getting my head around the maze of PHP->MySQLi functions! Could someone please provide an example of how one would use prepared statements to collect results in an associative array whilst also getting a row count from $stmt? The code below is what I'm playing around with. I think the bit that's throwing me off is using $stmt values after store_result and then trying to collect an assoc array, and I'm not too sure why...

$mysqli = mysqli_connect($config['host'], $config['user'], $config['pass'], $config['db']);
$stmt = $mysqli->prepare("SELECT * FROM licences WHERE generated = ?");
$stmt->bind_param('i', $core['id']);
$result = $stmt->execute();
$stmt->store_result();

if ($stmt->num_rows >= "1") {

    while($data = $result->fetch_assoc()){ 
        //Loop through results here $data[] 
    }

}else{

    echo "0 records found";

}

I feel a little cheeky just asking for code, but its a working demonstration of my circumstances that I feel I need to finally understand what's actually going on. Thanks a million!

Answer 1

I searched for a long time but never found documentation needed to respond correctly, but I did my research.

$stmt->get_result() replace $stmt->store_result() for this purpose. So, If we see

$stmt_result = $stmt->get_result();
var_dump($stmt_result);

we get

object(mysqli_result)[3]
  public 'current_field' => int 0
  public 'field_count' => int 10
  public 'lengths' => null
  public 'num_rows' => int 8  #That we need!
  public 'type' => int 0

Therefore I propose the following generic solution. (I include the bug report I use)

#Prepare stmt or reports errors
($stmt = $mysqli->prepare($query)) or trigger_error($mysqli->error, E_USER_ERROR);

#Execute stmt or reports errors
$stmt->execute() or trigger_error($stmt->error, E_USER_ERROR);

#Save data or reports errors
($stmt_result = $stmt->get_result()) or trigger_error($stmt->error, E_USER_ERROR);

#Check if are rows in query
if ($stmt_result->num_rows>0) {

  # Save in $row_data[] all columns of query
  while($row_data = $stmt_result->fetch_assoc()) {
    # Action to do
    echo $row_data['my_db_column_name_or_ALIAS'];
  }

} else {
  # No data actions
  echo 'No data here :(';
}
$stmt->close();

Answer 2

$result = $stmt->execute(); /* function returns a bool value */

reference : http://php.net/manual/en/mysqli-stmt.execute.php

so its just sufficient to write $stmt->execute(); for the query execution.

---

The basic idea is to follow the following sequence :
1. make a connection. (now while using sqli or PDO method you make connection and connect with database in a single step)
2. prepare the query template
3. bind the the parameters with the variable
4. (set the values for the variable if not set or if you wish to change the values) and then Execute your query.
5. Now fetch your data and do your work.
6. Close the connection.

---

/*STEP 1*/
$mysqli = mysqli_connect($servername,$usrname,$pswd,$dbname);
/*STEP 2*/
$stmt = $mysqli->prepare("SELECT * FROM licences WHERE generated = ?");
/*Prepares the SQL query, and returns a statement handle to be used for further operations on the statement.*/
//mysqli_prepare() returns a statement object(of class mysqli_stmt) or FALSE if an error occurred.
/* STEP 3*/
$stmt->bind_param('i', $core['id']);//Binds variables to a prepared statement as parameters
/* STEP 4*/
$result = $stmt->execute();//Executes a prepared Query
/* IF you wish to count the no. of rows only then you will require the following 2 lines */
$stmt->store_result();//Transfers a result set from a prepared statement
$count=$stmt->num_rows;
/*STEP 5*/
//The best way is to bind result, its easy and sleek
while($data = $stmt->fetch()) //use fetch() fetch_assoc() is not a member of mysqli_stmt class
{ //DO what you wish
  //$data is an array, one can access the contents like $data['attributeName']
}

One must call mysqli_stmt_store_result() for (SELECT, SHOW, DESCRIBE, EXPLAIN), if one wants to buffer the complete result set by the client, so that the subsequent mysqli_stmt_fetch() call returns buffered data.
It is unnecessary to call mysqli_stmt_store_result() for other queries, but if you do, it will not harm or cause any notable performance in all cases.
--reference: php.net/manual/en/mysqli-stmt.store-result.php
and http://www.w3schools.com/php/php_mysql_prepared_statements.asp
One must look up the above reference who are facing issue regarding this, My answer may not be perfect, people are welcome to improve my answer...

Answer 3

Your problem here is that to do a fetch->assoc(), you need to get first a result set from a prepared statement using:

http://php.net/manual/en/mysqli-stmt.get-result.php

And guess what: this function only works if you are using MySQL native driver, or "mysqlnd". If you are not using it, you'll get the "Fatal error" message.

Answer 4

You can try this using the mysqli_stmt function get_result() which you can use to fetch an associated array. Note get_result returns an object of type mysqli_result.

$stmt->execute();
$result = $stmt->get_result(); //$result is of type mysqli_result
$num_rows = $result->num_rows;  //count number of rows in the result

// the '=' in the if statement is intentional, it will return true on success or false if it fails.
if ($result_array = $result->fetch_assoc(MYSQLI_ASSOC)) { 
       //loop through the result_array fetching rows.
       // $ rows is an array populated with all the rows with an associative array with column names as the key 
        for($j=0;$j<$num_rows;$j++)
            $rows[$j]=$result->fetch_row();
        var_dump($rows);
   }
else{
   echo 'Failed to retrieve rows';
}

Answer 5

If you would like to collect mysqli results into an associative array in PHP you can use fetch_all() method. Of course before you try to fetch the rows, you need to get the result with get_result(). execute() does not return any useful values.

For example:

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($config['host'], $config['user'], $config['pass'], $config['db']);
$mysqli->set_charset('utf8mb4'); // Don't forget to set the charset!

$stmt = $mysqli->prepare("SELECT * FROM licences WHERE generated = ?");
$stmt->bind_param('i', $core['id']);
$stmt->execute(); // This doesn't return any useful value
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);

if ($data) {
    foreach ($data as $row) {
        //Loop through results here
    }
} else {
    echo "0 records found";
}

I am not sure why would you need num_rows, you can always use the array itself to check if there are any rows. An empty array is false-ish in PHP.

Answer 6

True, the Databasefunctions are a bit weird. You'll get there.

The code looks a bit iffy, but heres how it works:

A connection is build, a statement prepared, a parameter bound and it's executed, all well.

$result = $stmt->execute(); //execute() tries to fetch a result set. Returns true on succes, false on failure.
$stmt->store_result(); //store_result() "binds" the last given answer to the statement-object for... reasons. Now we can use it!

if ($stmt->num_rows >= "1") { //Uses the stored result and counts the rows.

  while($data = $result->fetch_assoc()){ 
        //And here, the answer-object is turned into an array-(object)
        // which can be worked with nicely.
        //It loops trough all entries in the array.
  }

}else{

   echo "0 records found";
}