SELECT Query using mysqli functions to get records with binding Parameters

Select rows with a prepared statement

Use a prepared statement when a WHERE value comes from a variable. bind_result() works without requiring get_result().

<?php
$stmt = $connection->prepare('SELECT id, name, mark FROM student WHERE class = ?');
$class = 'Three';
$stmt->bind_param('s', $class);
$stmt->execute();
$stmt->bind_result($id, $name, $mark);

while ($stmt->fetch()) {
    echo "$id - $name - $mark<br>";
}
?>

Recommended: use the prepared-statement example above for variable input. Older direct-query examples below are retained for comparison and should not receive untrusted values through string concatenation.

Current example first; the detailed examples below are retained for additional learning and comparison.

Student Table : Sample Data and query to create table
config.php Database connection object $connection is taken from config.php file.
Multiple records without parameter checking

Current guidance: if query values come from variables, use the prepared-statement example above. Direct SQL examples below are retained for learning/reference.

<?php
require "config.php";// <span class='text-success'>Database connection details, $connection object</span>

if($stmt = $connection->query("SELECT id, name ,class, mark FROM student")){

  echo "No of records : ".$stmt->num_rows."<br>";

  while ($row = $stmt->fetch_assoc()) {
	echo $row['id'],$row['name'],$row['class'].$row['mark']."<br>";
  }
}else{
echo $connection->error;
}
?>

We will get error message if the query fails to execute.
$connection is the variable declared as connection string inside database connection file.

MySQLI database connection file

Table and bootstrap design

if($stmt = $connection->query("SELECT id, name ,class, mark FROM student")){

  echo "No of records : ".$stmt->num_rows."<br>";

  echo "<table class='table table-striped'>
<tr class='info'> <th> ID</th><th>Name</th><th>Class</th><th>Mark</th></tr>";
while ($row = $stmt->fetch_assoc()) {
        echo "<tr><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td><td>$row[mark] </td></tr>";
    }
echo "</table>";
}else{
echo $connection->error;
}

Single record with parameter checking using WHERE condition

With Parameters by using bind_param() , Collecting Single Record
Note the use of $connection->prepare() in place of $connection->query()

<?php
require "config.php";// Database connection having $connection
//////////////////////////////
$id=3;
if($stmt = $connection->prepare("SELECT id, name ,class, mark FROM student  WHERE id=?")){
  $stmt->bind_param('i',$id);
  $stmt->execute();
   
   $result = $stmt->get_result();
   echo "No of records : ".$result->num_rows."<br>";
   $row=$result->fetch_object();
   echo $row->name;
}else{
  echo $connection->error;
}
?>

Multiple records with string parameter

<?php
require "config.php";// Database connection having $connection
//////////////////////////////
$class='Three';
if($stmt = $connection->prepare("SELECT id, name ,class, mark FROM student  WHERE class=?")){
$stmt->bind_param('s',$class);
$stmt->execute();
   $result = $stmt->get_result();
   echo "No of records : ".$result->num_rows."<br>";
    while ($row = $result->fetch_assoc()) {
	echo $row['id'],$row['name'],$row['class'].$row['mark']."<br>";
	}
}else{
 echo $connection->error;
}
?>

Procedural style ( with two binding parameters )

<?php
require "config.php";// Database connection having $connection
//////////////////////////////
$class='Three';
$mark=60;
if ($stmt = mysqli_prepare($connection, "SELECT id, name ,class, mark 
		FROM student  WHERE class=? AND mark >?")) {
  mysqli_stmt_bind_param($stmt, "si", $class,$mark);
  mysqli_stmt_execute($stmt);
  mysqli_stmt_store_result($stmt);
  echo " No of records :".mysqli_stmt_num_rows($stmt)."<br>";
  mysqli_stmt_bind_result($stmt, $id,$name,$class,$mark);
  while (mysqli_stmt_fetch($stmt)) {
        echo "$id, $name,$class,$mark <br>";
    }
  mysqli_stmt_close($stmt);
}else{
	
 echo mysqli_error($connection);
}
?>

Binding Multiple Parameters Using Prepared Statements with Error Handling in a Try-Catch Block

More on Try - Catch - Finally code blocks to handle error

require "config.php";// Database connection having $connection
try {
$class = 'Three'; // Example data
$mark = 50;  // Wildcard for name search

// Prepare the SQL query
$query = "SELECT * FROM student WHERE class = ? AND mark >= ?";

// Prepare the statement
$step = $connection->prepare($query);

// Bind parameters to the placeholders (s = string, i=integer )
$step->bind_param("si", $class, $mark);

// Execute the query
$step->execute();

// Get the result
$result = $step->get_result();

// Fetch all results as an associative array
while ($row = $result->fetch_assoc()) {
    echo "ID: " . $row['id'] . " - Name: " . $row['name'] . " - Class: " . $row['class'] . "<br>";
}
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

Explanation of PHP MySQLi Code with Prepared Statements

Database Connection: The database connection is established using the config.php file, which contains the MySQLi connection object $connection.
Try Block: The try block contains the code that may throw an error. If any error occurs during the execution, the code inside the catch block will handle it.
Prepared Statement: The SQL query contains placeholders (?) where values will be inserted. This improves security and helps prevent SQL injection attacks.
Binding Parameters: $step->bind_param() binds the actual values to the placeholders in the SQL query. The "si" indicates that the first parameter is a string (class), and the second parameter is an integer (mark).
Executing the Query: The $step->execute() method executes the prepared statement with the bound parameters.
Fetching Results: After the query is executed, $result = $step->get_result() retrieves the result set, and fetch_assoc() fetches the results as an associative array.
Error Handling: If an error occurs, the catch block catches the exception and displays an error message. In this case, mysqli_sql_exception is used for handling MySQLi-specific errors.

Output

ID: 2 - Name: Max Ruin - Class: Three
ID: 3 - Name: Arnold - Class: Three
ID: 27 - Name: Big Nose - Class: Three

The script outputs the ID, name, and class of students who belong to class "Three" and have marks greater than or equal to 50.
If there are no matching records, no output will be shown. If an error occurs, the catch block will output the relevant error message.

MySQL DUMP of student table

Generate PHP code with MySQLi function to database

We can enter the select Query and generate the code for connecting and displaying records of our table.

PHP code generator using MySQLi functions to display records of a table

Podcast on MySQL database management using MySQLi connector

MYSQLI Functions mysqli_num_rows() Number of rows in result set UPDATE query

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: