How to Fetch Data From Database In PHP?

Problem:

You may have established connection with MySQL database. Now, you want to fetch data from the database (more specifically from a table).

Solution:

You know PHP provide three APIs to connect with MySQL. In the following, we’ll discuss how to fetch information (We’ll fetch Name and email of the students whose class is 7) from the following table using these APIs. Here are the APIs-

PHP Data Objects(PDO)
PHP’s MySQLi Extension
PHP’s MySQL Extension

database table
1. Using PDO

There are two methods we can use to fetch information using PDO.

Query Method
Prepared Method

1. a. Query Method

The following code shows how to use query method for fetching information.

<?php
// Using the PDO Query Method
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

$class = 7;

try {
    // Establish server connection and select database
    $dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // run query to select records
    $rows = $dbh->query("SELECT * FROM students WHERE class = ". $dbh->quote($class));
    foreach($rows as $row){
        echo $row['Name'] . "  -- " . $row['Email'] . '<br />';
    }
}catch(PDOException $e){
    echo "Error occurs:". $e->getMessage();
}
?>

[wpdm_file id=2]

Explanation:

Line 12	This line makes a connection with the mysql database(db_student).
Line 13	setAttribute() method sets a predefined PDO attribute PDO::ATTR_ERRMODE which reports errors. The value of the attribute is PDO::EXCEPTION which fire up any errors occurs with the query.
Line 15	The query() method executes the SQL statement inside it and returns the result as a PDOStatement object and we store it in $rows. Here, the quote() function places quotes around the string(here, $class) and escapes any special characters within the string.
Line 16-18	The foreach loop prints Name and Email of all the students whose class is 7.
Line 20	If there is any error occurs, the getMessage() function return that exception as a string and it will be printed here.

Please note that in the previous example, we’re still using the manual escaping method using quote() method which is not very nice. In this case prepare method is better as it escapes any special characters from user inputs automatically.

When to use query method

When you’re not using any user-supplied information in the query string then it is a good choice to use.
When you’re executing a query once, then it is a good choice too.

When not to use query method

When you’re using user supplied information in the query string, then it is recommended to use prepare method.
When you’re executing a query more than once then use prepare method. As it uses fewer resources, so runs faster than the query method. If you still want to use query method for multiple call, then call PDOStatement::closeCursor() to release any database resources associated with the current PDOStamenet object before calling the next query. Your next execution of query method may fail if you don’t fetch all the data from the result set returning by the previous query method.

If you’re binding user-supplied data (ex, form inputs) in the query, it is strongly recommended to use prepare method (which we’ll discuss next) instead of the query method.

1. b. Prepare method

Here, we’ll see how to use prepared method to fetch information.

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

$class = 7;

try {
    // Establish server connection and select database
    $dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // run query to select records
    $stmt = $dbh->prepare("SELECT Name, Email FROM students WHERE Class = :Class");
    $stmt->bindParam(':Class', $class, PDO::PARAM_STR);
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['Name'] . "  -- " . $row['Email'] . '<br />';
    }
}catch(PDOException $e){
    echo "Error occurs:". $e->getMessage();
}
?>

[wpdm_file id=1]

Output:
Joseph B. Guidry — JosephB@yahoo.com
Mario D. Pierce — Mario@yahoo.com

Explanation:

Line 11	This line makes a connection with the database(db_student) using mysql.
Line 12	setAttribute() method sets a predefined PDO attribute PDO::ATTR_ERRMODE which reports errors. The value of the attribute is PDO::EXCEPTION which fire up any errors occurs with the query.
Line 14	As the name implies the prepare() method prepares query to be executed by the execute() method. Here, it takes the SQL statement as a parameter. The SQL statement contains a named parameter marker(:class). When we’ll execute the query, we’ll substitute this parameter marker by the real value. If the SQL statement is prepared successfully, the prepare method returns PDOStatement object. We receive it in the $stmt variable.
Line 15	On the PDOStatement object($stmt), we call the bindParam method to bind the user supplied variable($class) to the named parameter(:Class). here, PDO::PARAM_STR indicates data type for the parameter.
Line 16	On the PDOStatement object($stmt), we execute the prepare statement here.
Line 17-21	On the PDOStatement object($stmt), we call the fetch method to fetche a row from the result set associated with $stmt. The constant PDO::FETCH_ASSOC indicates that the row is receiving as associative array. And this array is stored in the $row variable (see line 17) After fetching each array, the while loop prints line 17-19

In the above example, we used named parameter marker(:Class) in the SQL statement. Instead of this named parameter, we could also used question mark(?) parameter marker. Like following-

<?php
$stmt = $dbh->prepare("SELECT * FROM students WHERE Class = ?");
?>

for better reading experience, you can use named parameter marker over question mark parameter marker.
The user supplied data is not inserted in the SQL statement when we prepare the query in the prepare method. So, this method is safe from SQL injection.

If you want to use question mark parameter in place of named parameter markers, the same code could be written as-

<?php
$stmt = $dbh->prepare("SELECT * FROM students WHERE Name = ? AND Email =?");
$stmt->bindParam(1, $name, PDO::PARAM_STR);
$stmt->bindParam(2, $email, PDO::PARAM_STR);
$stmt->execute();
?>

2. Using MySQLi Extension

We can write MySQLi code to fetch data in two ways-

Procedural style and
Object oriented style

2. a. Procedural style

Here, we’ll see how to use procedural style in MySQLi to fetch database table information.

<?php
// MySQLi Procedural Style
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

$class = 7;

// Establish server connection and select database
$dbh = mysqli_connect($host, $user, $password, $database);

if (mysqli_connect_errno()) {
    die('Unable to connect to database '. mysqli_connect_error());
}else{
    $class = mysqli_real_escape_string($dbh, $class);
    // run query to fetch records
    $result = mysqli_query($dbh, "SELECT Name, Email FROM students WHERE Class = '$class'");
    /* fetch associative array */
    while ($row = mysqli_fetch_assoc($result)) {
		echo $row['Name'] . "  -- " . $row['Email'] . '<br />';
    }
}
?>

[wpdm_file id=3]

Output:
Joseph B. Guidry — JosephB@yahoo.com
Mario D. Pierce — Mario@yahoo.com

Explanation:

Line 18	mysqli_query() function runs the SQL statement existed in its second parameter. It runs the query against the database which link identifier is the first parameter(here $dbh). After executing the query, the function returns mysqli_result object(here $result).
Line 20	Using the mysqli_result object(here $result) which is the first parameter of the mysqli_fetch_assoc() function, it fetches each row of the result as an associative array and returns it. The while loop runs as long it finds match.

2. b. Object oriented style

Here, we’ll see how to fetch data in MySQLi using object oriented style.

<?php
// MySQLi Object Oriented Style
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

$class = 7;

// Establish server connection and select database
$dbh  = new mysqli($host, $user, $password,  $database);

if ($dbh->connect_error) {
    echo 'Unable to connect to database '. $dbh->connect_error;
}else{
    if ($stmt = $dbh->prepare("SELECT Name, Email FROM students WHERE Class = ?")) {
        $class = $dbh->real_escape_string($class);
        if ($stmt->bind_param("i", $class)) {
            if ($stmt->execute()) {
                $stmt->bind_result($Name, $Email);
                /* fetch associative array */
                while ($stmt->fetch()) {
                    echo $Name. ' -- '. $Email. '<br />';
                }
            }else{
                echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }
        }else{
            echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
        }
    }else{
        echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}
?>

[wpdm_file id=4]

Output:
Joseph B. Guidry — JosephB@yahoo.com
Mario D. Pierce — Mario@yahoo.com

Explanation:

Line 11	This line establishes a connection with mysql database(db_student).
Line 13-14	If there is any error in connection, $dbh->connect_error returns that and prints in line 13. Otherwise program control enters into else statement.(Line 14)
Line 16	The prepare() method prepares the SQL statement which is used as its parameter. In the parameter parameter marker(question mark – ?) is included. The method returns statement object(here $stmt).
Line 17	real_escape_string() method escapes any special character found in the user supplied variable($class).
Line 18	bind_param() method binds the variable $class for the parameter marker (?) we used in the SQL statement in the prepare() method in line 15. Here “i” in the first parameter indicates that type of the bind variable($class) is integer.
Line 19	execute() method executes the query prepared by the prepare() method.
Line 20	bind_result() method binds the columns(here Name, Email) in the result set to the variables mentioned in its parameters($Name, $Email).
Line 22	fetch() method fetches the result that has been returned by the prepare method() into the bound variables.
Line 23	Here we’re printing Name and Email of the student which class number is 7.

3. Using MySQL Extension

<?php
// MySQL
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

$class = 7; // User input

// Establish server connection
$link = mysql_connect($host, $user, $password);
if(!$link) {
    die("Not connected: ". mysql_error());
}else{
    // Select Database
    mysql_select_db($database, $link);
    // escape any special character
    $class = mysql_real_escape_string($class);
    // run query to fetch records
    $result = mysql_query("SELECT Name, Email FROM students WHERE Class = '$class'");
    /* fetch associative array */
    while ($row = mysql_fetch_assoc($result)) {
        echo $row["Name"], " -- ".$row["Email"]. "<br />";
    }
}
?>

[wpdm_file id=5]

Output:
Joseph B. Guidry — JosephB@yahoo.com
Mario D. Pierce — Mario@yahoo.com

Explanation:

Line 17	mysql_real_escape_string() method escapes any special character found in the user supplied variable($class).
Line 19	mysql_query() method sends the query to the database. After successful running of the query, the function returns a resource(here $result)
Line 21	Using the resource($result), mysql_fetch_assoc() function fetches each result row as an associative array. The while loop runs as long it finds rows.

This method is discouraged to use as from PHP 5.5.0, it is deprecated. Please use either PHP’s MySQLi Extension or PHP Data Objects (PDO).