PHP MySQLi Prepared Statement Select Multiple Rows

PHP MySQLi prepared statement select multiple rows

In this post we will give you information about PHP MySQLi prepared statement select multiple rows. Hear we will give you detail about PHP MySQLi prepared statement select multiple rowsAnd how to use it also give you demo for it if it is necessary.

In this post, i will tell you how to use MySQLi prepared statement to select or fetch multiple rows.

As you know very well that by using MySQLi prepared statements we make our database query more secure to normal database query.

If you are using query as procedural way then you really should use prepared statements.

<?php
functiongetData()
{
$parameters=array();
$arr_results=array();
$db=newmysqli('localhost','root','','database')ordie('Database connection failed');
$stmt=$db->prepare('SELECT name,details FROM products')ordie('Something wrong with prepare query');
$stmt->execute();
$meta=$stmt->result_metadata();
while($rows=$meta->fetch_field()){
$parameters[]=&$row[$rows->name];
}
call_user_func_array(array($stmt,'bind_result'),$parameters);
while($stmt->fetch()){
$x=array();
foreach($rowas$key=>$val){
$x[$key]=$val;
}
$arr_results[]=$x;
}
return$arr_results;
}
$arr_results=getData();
?>
<table>
<tr>
<th>Name</th>
<th>Details</th>
</tr>
<?phpforeach($arr_resultsas$row):?>
<tr>
<td><?phpecho$row['name'];?></td>
<td><?phpecho$row['details'];?></td>
</tr>
<?phpendforeach;?>
</table>

<?php
function getData()
{
   $parameters = array();
   $arr_results = array();
 
   $db = new mysqli('localhost', 'root', '', 'database') or die('Database connection failed');
   $stmt = $db->prepare('SELECT name,details FROM products') or die('Something wrong with prepare query');
   $stmt->execute();
 
   $meta = $stmt->result_metadata();
 
   while ( $rows = $meta->fetch_field() ) {
 
     $parameters[] = &$row[$rows->name]; 
   }
 
   call_user_func_array(array($stmt, 'bind_result'), $parameters);
 
   while ( $stmt->fetch() ) {
      $x = array();
      foreach( $row as $key => $val ) {
         $x[$key] = $val;
      }
      $arr_results[] = $x;
   }
 
   return $arr_results; 
}
 
$arr_results = getData();
?>
<table>
<tr>
<th>Name</th>
<th>Details</th>
</tr>
<?php foreach ($arr_results as $row) : ?>
 <tr>
 <td><?php echo $row['name']; ?></td>
 <td><?php echo $row['details']; ?></td>
 </tr>
<?php endforeach; ?>
</table>

SELECT COUNT Total records of a table

Sometime you need to know the total number of records of a table then you can get the total number of records by using this query.

<?php
$db=newmysqli('localhost','root','','database_name');
if($db->connect_error){
die('Error : ('.$db->connect_errno .') '.$db->connect_error);
}
$results=$db->query("SELECT COUNT(*) FROM products");
$total_records=$results->fetch_row();
$db->close();
?>

<?php

$db = new mysqli('localhost','root','','database_name');
if ($db->connect_error) {
    die('Error : ('. $db->connect_errno .') '. $db->connect_error);
}
$results = $db->query("SELECT COUNT(*) FROM products");
$total_records = $results->fetch_row();
$db->close();
?>

SELECT Multiple Records as Array OOP Method

fetch_array() method return an array response for both numeric key and associative string.

If you are comparing from fetch_array() vs fetch_assoc() then i always suggest you to use fetch_array() because you will never want to access records by index number if you know column name.

<?php
$db=newmysqli('host','username','password','database');
// if connection failed
if($db->connect_error){
die('Error : ('.$db->connect_errno .') '.$db->connect_error);
}
//Select Query
$results=$db->query("SELECT id, name, details FROM products");
echo'<table border="1">';
while($row=$results->fetch_array()){
echo'<tr>';
echo'<td>'.$row["id"].'</td>';
echo'<td>'.$row["name"].'</td>';
echo'<td>'.$row["details"].'</td>';
echo'</tr>';
}
echo'</table>';
// close connection 
$db->close();
?>

<?php
$db = new mysqli('host','username','password','database');

// if connection failed
if ($db->connect_error) {
    die('Error : ('. $db->connect_errno .') '. $db->connect_error);
}
//Select Query
$results = $db->query("SELECT id, name, details FROM products");
echo '<table border="1">';
while($row = $results->fetch_array()) {
    echo '<tr>';
    echo '<td>'.$row["id"].'</td>';
    echo '<td>'.$row["name"].'</td>';
    echo '<td>'.$row["details"].'</td>';
    echo '</tr>';
}   
echo '</table>';
// close connection 
$db->close();
?>

You can also get result set as an objects by using MySqli fetch_object methods.

Label :

MySQL

How To

Database

Hope this code and post will helped you for implement PHP MySQLi prepared statement select multiple rows. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs

For More Info See :: laravel And github