Display MySQL Records with PHP: MySQLi and PDO Examples

Display records with MySQLi and escape HTML output

The first example uses a prepared statement for a class filter and escapes database values before displaying them in HTML. The later examples demonstrate object-oriented MySQLi, procedural MySQLi, PDO and filtering variations. Each is independent: create a fresh MySQLi connection using the first example before trying another variation, or initialize PDO using the linked tutorial.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli('localhost', 'db_user', 'db_password', 'database_name');
$connection->set_charset('utf8mb4');
$class = 'Four';
$stmt = $connection->prepare('SELECT id, name, class, mark FROM student WHERE class = ? ORDER BY id');
$stmt->bind_param('s', $class);
$stmt->execute();
$result = $stmt->get_result(); // Requires mysqlnd.
while ($row = $result->fetch_assoc()) {
    echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . ' - ';
    echo htmlspecialchars($row['class'], ENT_QUOTES, 'UTF-8') . '<br>';
}
$result->free();
$stmt->close();
$connection->close();
?>

Displaying data from a table is a very common requirement and we can do this in various ways depending on the way it is required. We will start with a simple one to just display the records and then we will move to advance one like breaking the returned records to number of pages. We will start with simple displaying the records of this table.

id	name	class	mark
1	John Deo	Four	75
2	Max Ruin	Three	85
3	Arnold	Three	55
4	Krish Star	Four	60
5	John Mike	Four	60
6	Alex John	Four	55
7	My John Rob	Fifth	78

Watch this Video Tutorial about this script

Before starting please ensure that you have connected to MySql database and also check the article on PHP MySQL query to know how to execute MySql queries by using PHP

Let us first start by storing the query in a variable and then executing it
MySQLI database connection file
Example : Object Oriented Style

<?php
// First create $connection as shown in the complete example above.
$result_set = $connection->query('SELECT id, name, class, mark FROM student LIMIT 5');
while ($row = $result_set->fetch_assoc()) {
    echo (int)$row['id'] . ' - ';
    echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . ' - ';
    echo htmlspecialchars($row['class'], ENT_QUOTES, 'UTF-8') . ' - ';
    echo (int)$row['mark'] . '<br>';
}
$result_set->free();
?>

The object-oriented example below runs a fixed SELECT. If a filter or other value comes from a visitor, use the prepared-statement version above instead. Each independent fragment needs a new MySQLi connection created as in the first example; do not run the snippets sequentially after the first example closes its connection.

$connection	Connection object Declared inside config.php file
$result_set	For a successful SELECT, query() returns a mysqli_result. With strict error reporting, failures throw mysqli_sql_exception.
fetch_array	Returns row of data from result set as array of string. NULL is returned if no more row is available to return.

Used WHILE loop to display record by record. You can see our php While to learn about loops.

These examples display the name, class and mark for each record. The HTML <br> gives one line break after each record. This can be formatted well to display inside a table.

id	name	class	mark	sex
1	John Deo	Four	75	female
2	Max Ruin	Three	85	male
3	Arnold	Three	55	male
4	Krish Star	Four	60	female
5	John Mike	Four	60	female

Procedural MySQLi style (using the same initialized connection):

<?php
// First create $connection as shown in the complete example above.
$query = 'SELECT id, name, class, mark FROM student LIMIT 5';
$result_set = mysqli_query($connection, $query);
while ($row = mysqli_fetch_assoc($result_set)) {
    echo (int)$row['id'] . ' - ';
    echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . ' - ';
    echo htmlspecialchars($row['class'], ENT_QUOTES, 'UTF-8') . ' - ';
    echo (int)$row['mark'] . '<br>';
}
mysqli_free_result($result_set);
?>

Using MySQLi

MySQLi connection

<?php
// First create $connection as shown in the complete example above.
$result_set = $connection->query('SELECT id, name, class, mark FROM student');
echo 'Number of records: ' . $result_set->num_rows . '<br>';
while ($row = $result_set->fetch_assoc()) {
    echo (int)$row['id'] . ' - ';
    echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . ' - ';
    echo htmlspecialchars($row['class'], ENT_QUOTES, 'UTF-8') . ' - ';
    echo (int)$row['mark'] . '<br>';
}
$result_set->free();
?>

Using PDO

This separate example requires a PDO connection in $dbo; see the PDO connection tutorial. For queries with visitor-supplied values, use PDO prepared statements.

<?php
// $dbo must be an initialized PDO MySQL connection.
$sql = 'SELECT id, name, class, mark FROM student LIMIT 5';
echo '<table><tr><th>ID</th><th>Name</th><th>Class</th><th>Mark</th></tr>';
foreach ($dbo->query($sql, PDO::FETCH_ASSOC) as $row) {
    echo '<tr><td>' . (int)$row['id'] . '</td>';
    echo '<td>' . htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . '</td>';
    echo '<td>' . htmlspecialchars($row['class'], ENT_QUOTES, 'UTF-8') . '</td>';
    echo '<td>' . (int)$row['mark'] . '</td></tr>';
}
echo '</table>';
?>

MySQLi select query to get data

Displaying single record

From the above code we can create links to display full details of the record. We will carry unique id of the record through query string and then display the single record.
Link with name value pair

Displaying single record per page

Breaking number of records to multiple pages

When our output have more number of records to display and we want to display few records ( say 10 only ) per page then we can use Paging concept to limit the number of records per page. We will add navigational links to move between pages to display all records.
Breaking number of records by PHP paging

Filtering records.

By adding SQL commands like WHERE conditions we can filter data as per our requirements. Let us find out the records of class Four only.

// A fixed filter is safe as a literal.
$query = "SELECT id, name, class, mark FROM student WHERE class = 'Four'";
// If $class comes from user input, use the prepared statement shown first.

SQL WHERE Condition

PHP MySQL Query with Error message

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:

Subscribe to our YouTube Channel here

Atar	25-05-2012
Hi can you tell me how to insert date month and year combine in databese

Venedict Francisco	07-09-2013
how can i post or display data information from the two different table?

smo1234	09-06-2015
You can always combine more than one table and display information. You can select multiple tables in a select statement and join more than two tables by using LEFT join Check SQL section for more details.

mariel	16-02-2012
thank you...it works..

Simon	22-02-2012
Thanks dude, this tutorial helped me a lot.

Displaying records in PHP from MySQL table