Mysqli - Bind results to an Array

Question

I recently switched to mysqli from mysql and started using prepared statements. in mysql we do

$result = mysql_query("SELECT * FROM table WHERE id = ?");

So we get the array of whole table in one variable.

But in mysqli we do

mysqli_stmt_bind_result($stmt, $result);

so basically here only one variable gets bind to variable result. How can we get the same variable(array) that we got from mysql?
PS - Hope my ques is clear enough. I know their are not many methods possible but i am luking for the best one.
PSS - I am more comfortable with the procedural way.

Answer 1

Finally this code is Working !!!!!

  <?php 
    $host = 'localhost'; 
    $user = 'root'; 
    $pass = '1234'; 
    $data = 'test'; 

    $mysqli = new mysqli($host, $user, $pass, $data); 
    /* check connection */ 
    if (mysqli_connect_errno()) { 
        printf("Connect failed: %s\n", mysqli_connect_error()); 
        exit(); 
    } 

    if ($stmt = $mysqli->prepare("SELECT * FROM sample WHERE t2 LIKE ?")) { 
        $tt2 = '%'; 

        $stmt->bind_param("s", $tt2); 
        $stmt->execute(); 

        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) 
        { 
            $params[] = &$row[$field->name]; 
        } 

        call_user_func_array(array($stmt, 'bind_result'), $params); 

        while ($stmt->fetch()) { 
            foreach($row as $key => $val) 
            { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 

        $stmt->close(); 
    } 
    $mysqli->close(); 
    print_r($result); 
    ?>

Answer 2

Use prepared statement as..

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();

printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));

Answer 3

Use Simply Like this

$con=mysqli_connect( "$host", "$mysql_u", "$mysql_p","$mysql_db");/*these variables are ur host,username, password and db name*/
    $val="SELECT * FROM table";
    $ex=mysqli_query($con,$val);
    while ($row = mysqli_fetch_assoc($ex)) {

    $ans=$row['col1'];
    ....
    }

Answer 4

mysql:

$result = mysql_query("SELECT * FROM table");

mysqli:

$result = mysqli_query("SELECT * FROM table");

Please note the example you provided is not using prepared statement.

But if you ever want to use mysqli_stmt_bind_result and return an array instead of one column at the time, you might need to wrap it your own custom function.

The good news there are few examples at the bottom of the docs, but they are in OOP, (time to switch?).

---

$result = mysql_query("SELECT * FROM table WHERE id = ?");


/* create a prepared statement */
if ($stmt = mysqli_prepare($link, "SELECT col1, col2  FROM table WHERE id = ?")) {

    /* bind parameters for markers */
    mysqli_stmt_bind_param($stmt, "s", $city);

    /* execute query */
    mysqli_stmt_execute($stmt);


    /* fetch associative array */
    while ($row = mysqli_fetch_assoc($stmt)) {
        printf ("%s (%s)\n", $row["col1"], $row["col2"]);
    }


}else{
    echo "prepared failed";
}

Answer 5

PHP's variable argument list (ellipsis) operator comes in handy for dynamic number of prepared statement replacements:

mysqli_stmt_bind_param($stmt, $params_types, ...$params);

An example:

$sql = 'SELECT * FROM Table WHERE one = ? AND two = ?';
$params_types = 'ii';
$params = array(1, 2);

// prepare
$stmt = mysqli_stmt_init($dbconn_read);
if ( mysqli_stmt_prepare($stmt, $sql) ) {

  // bind and execute
  mysqli_stmt_bind_param($stmt, $params_types, ...$params); // unpack array into arguments
  mysqli_stmt_execute($stmt);

  // get column names for binding return results
  $resultmeta = mysqli_stmt_result_metadata($stmt);
  list($columns, $columns_vars) = array(array(), array());
  while ( $field = mysqli_fetch_field($resultmeta) ) {
    $columns[] = $field->name;
    $columns_vars[] = &${'column_' . $field->name};
  }

  // call bind function with arguments in array
  call_user_func_array('mysqli_stmt_bind_result', array_merge(array($stmt), $columns_vars));

  // get return results
  $return_array = array();
  while ( mysqli_stmt_fetch($stmt) ) {
    $row = array();
    foreach ( $columns as $col ) {
      $row[$col] = ${'column_' . $col}; // populate assoc. array with data
    }
    $return_array[] = $row; // push row data onto return array
  }
}

Even better incorporated in a function:

function db_query(&$dbconn, $sql, $params_types, ...$params) { // pack dynamic number of remaining arguments into array

  // GET QUERY TYPE
  $query_type = strtoupper(substr(trim($sql), 0, 4));

  $stmt = mysqli_stmt_init($dbconn);
  if ( mysqli_stmt_prepare($stmt, $sql) ) {
    mysqli_stmt_bind_param($stmt, $params_types, ...$params); // unpack
    mysqli_stmt_execute($stmt);

    if ( 'SELE' == $query_type || '(SEL' == $query_type ) {
      $result = mysqli_stmt_result_metadata($stmt);
      list($columns, $columns_vars) = array(array(), array());
      while ( $field = mysqli_fetch_field($result) ) {
        $columns[] = $field->name;
        $columns_vars[] = &${'column_' . $field->name};
      }
      call_user_func_array('mysqli_stmt_bind_result', array_merge(array($stmt), $columns_vars));
      $return_array = array();
      while ( mysqli_stmt_fetch($stmt) ) {
        $row = array();
        foreach ( $columns as $col ) {
          $row[$col] = ${'column_' . $col};
        }
        $return_array[] = $row;
      }

      return $return_array;
    } // end query_type SELECT

    else if ( 'INSE' == $query_type ) {
      return mysqli_insert_id($dbconn);
    }
    return 1;
  }
}

Call it like this:

$records = db_query(
  $dbconn_read,
  'SELECT * FROM Table WHERE one = ? AND two = ?',
  'ii', 1, 2
);