PHP MySQLi Select Statement

In previous articles, you have learnt how to create database and table as well as inserting data. Now it's time to learn how to retrieve data. The MySQL SELECT statement is used to select records from the database tables.

Syntax

SELECT [Fields] FROM Tablename [WHERE CLAUSE] [LIMIT];

These are the following ways to select data from a table.

Object-oriented PHP MySQLi Select Data

The following example selects all columns from the 'employee' table in MySQLi object-oriented way and displays it on the page:

<?php
$conn = new mysqli('hostname', 'username', 'password', 'databasename');
//Check for connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
$select = "SELECT * FROM `employee` ";
$result = $conn->query($select);
if($result->num_rows > 0){
 echo '<table>';
 echo '<tr><td>Employee Name</td>'; 
 echo '<td>Email</td>'; 
 echo '<td>Phone</td>';  
 echo '<td>Address</td>';  
 echo '</tr>';
 while($row = $result->fetch_object()){
   echo '<tr>';  
   echo '<td>'. $row->emp_name.'</td>';
   echo '<td>'. $row->email.'</td>';
   echo '<td>'. $row->phone.'</td>';
   echo '<td>'. $row->address.'</td>';
   echo '</tr>';
 }
 echo '</table>';
}
?>

Procedural PHP MySQLi Select Data

The given example shows the same as the example above, in the MySQLi procedural way:

<?php
$conn = mysqli_connect('hostname', 'username', 'password', 'databasename');
//Check for connection error
if(mysqli_connect_error()){
  die("Error in DB connection: ".mysqli_connect_errno()." - ".mysqli_connect_error());
}
$select = "SELECT * FROM `employee` ";
$result = mysqli_query($conn, $select);
if(mysqli_num_rows($result)){
 echo '<table>';
 echo '<tr><td>Employee Name</td>'; 
 echo '<td>Email</td>'; 
 echo '<td>Phone</td>';  
 echo '<td>Address</td>';  
 echo '</tr>';
 while($row = mysqli_fetch_object($result)){
   echo '<tr>';  
   echo '<td>'. $row->emp_name.'</td>';
   echo '<td>'. $row->email.'</td>';
   echo '<td>'. $row->phone.'</td>';
   echo '<td>'. $row->address.'</td>';
   echo '</tr>';
 }
 echo '</table>';
}
?>   
    

Prepared PHP MySQLi Select Statement

The following example uses prepared statements. It selects the emp_id, emp_name, email, phone and address columns from the 'employee' table and displays it in an HTML table:

<?php
$conn = new mysqli('hostname', 'username', 'password', 'databasename');
//Check for connection error
if($conn->connect_error){
  die("Error in DB connection: ".$conn->connect_errno." : ".$conn->connect_error);    
}
$empname = 'John';
$query = "SELECT emp_id, emp_name, email, phone, address FROM employee WHERE emp_name =? ";
$select = $conn->prepare($query);
// bind parameters 
$select->bind_param('s',$empname);
//execute query
$select->execute();
//bind the result variables 
$select->bind_result($emp_id, $emp_name, $email, $phone, $address);
 echo '<table>';
 echo '<tr><td>Employee Id</td>'; 
 echo '<td>Employee Name</td>'; 
 echo '<td>Email</td>'; 
 echo '<td>Phone</td>';  
 echo '<td>Address</td>'; 
 echo '</tr>';
 while($select->fetch()){
   echo '<tr>';  
   echo '<td>'. $emp_id.'</td>';
   echo '<td>'. $emp_name.'</td>';
   echo '<td>'. $email.'</td>';
   echo '<td>'. $phone.'</td>'; 
   echo '<td>'. $address.'</td>'; 
   echo '</tr>';
 }
 echo '</table>';
?>