MYSQLI SELECT query

<?Php
require "config.php";// Database connection details, $connection object

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;
}
?>

MySQLI database connection file

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

<?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;
}
?>

<?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;
}
?>

<?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

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

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

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: