PHP & MySQL - Select Data

The MySQL SELECT statement is used to select data from a database table and the selected data is returned in the form of a table.

The syntax for using SELECT statement in different scenarios are given below:

/*select one column*/
SELECT column1 FROM table_name;

/*select multiple columns*/
SELECT column1, column2, ... 
FROM table_name;

/*select all columns of a table*/
SELECT * FROM table_name;

Along with this, to connect to the MySQL server, mysqli_connect() function can be used. After establishing the connection, mysqli_query() function can be used to perform a query on the database.

The num_rows() function can be used to check if there are more than zero rows returned. Then, the fetch_assoc() function can be used to fetch the result set as an associative array. Later on the free_result() function can be used to free the memory associated with the result.

Select Data - Object-oriented style

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

The example below demonstrates how to select Name, Age and City records present in the Employee table using object-oriented style.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = new mysqli($servername, $username, $password, $dbname);

//checking connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//getting query result from the database
$sql = "SELECT Name, Age, Salary FROM Employee";
$result = $mysqli->query($sql);

//fetching associative array
while ($row = $result->fetch_assoc()) {
  printf("%s, %d, %s\n", $row["Name"], $row["Age"], $row["City"]);
}

//free result set
$result->free_result();

//closing the connection
$mysqli->close();
?>

The output of the above code will be:

John, 25, London
Marry, 24, New York
Jo, 27, Paris
Kim, 30, Amsterdam
Ramesh, 28, New Delhi
Huang, 28, Beijing

Select Data - Procedural style

To obtain the same result using procedural style, the following script can be used.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = mysqli_connect($servername, $username, $password, $dbname);

//checking connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//getting query result from the database
$sql = "SELECT Name, Age, Salary FROM Employee";
$result = mysqli_query($mysqli, $sql);

//fetching associative array
while ($row = mysqli_fetch_assoc($result)) {
  printf("%s, %d, %s\n", $row["Name"], $row["Age"], $row["City"]);
}

//free result set
mysqli_free_result($result);

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be:

John, 25, London
Marry, 24, New York
Jo, 27, Paris
Kim, 30, Amsterdam
Ramesh, 28, New Delhi
Huang, 28, Beijing

Complete PHP MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.