PHP and MySQLi Fetch Record from Table

« Previous Tutorial Next Tutorial »

Here in this tutorial, you will learn how to fetch record from MySQL table and display that record on the web using PHP and MySQLi.

What to do Before Fetching Record from Table

Before fetching the record from table and displaying it on web using PHP, be sure that you have already learned our previous tutorial, and created a database and a table namely codescrackerTwo and codescrackerTable (with 4 columns).

PHP and MySQLi Fetch Record from Table Example

Here is an example demonstrates how to fetch record from table to display on the web using PHP.

<?php 
	$server = "localhost";
	$user = "root";
	$pass = "";
	$database = "codescrackerTwo";
	$conn = mysqli_connect($server, $user, $pass, $database);
	if(mysqli_connect_error())
	{
		echo "<p>Some error occurred during database connection.</p>";
		echo "<p>Try after some time...exiting...</p>";
		exit();
	}
?>
<html>
<head>
	<title>PHP and MySQLi Fetching and Displaying Record from Table Example</title>
</head>
<body>
<?php
	$table = "codescrackerTable";
	// following code is used to select all the data or row from the table
	// named codescrackerTable
	$sqlQ = "SELECT * FROM $table";
	$res = $conn->query($sqlQ);
	if($res)
	{
		while($row = mysqli_fetch_row($res))
		{
			// here $row[0] contains first column's value, that is, id
			// $row[1] contains second column's value, that is, datetime
			// $row[2] contains third column's value, that is userid
			// and $row[3] contains fourth column's value, that is usercomment
			echo "<b>$row[2]:</b> $row[3]<br/>";
		}
	}
	else 
	{
		echo "<p>Error occurred...exiting...</p>";
		exit();
	}
?>
</body>
</html>

Here is the sample output produced after running the above example code of PHP and MySQLi to fetch and display record from the MySQL table.

If you want to fetch only first 5 records from the table, then replace following code:

$sqlQ = "SELECT * FROM $table";

with

$sqlQ = "SELECT * FROM $table LIMIT 0, 5";

Here is the output produced after replacing the code and re-running the above example:

If you want to fetch and display first 10 records from the table, then replace 5 with 10.

Now here is the optimized version of the above example code to fetch and display record from MySQL table using PHP:

<?php 
	$server = "localhost";
	$user = "root";
	$pass = "";
	$database = "codescrackerTwo";
	$conn = mysqli_connect($server, $user, $pass, $database);
	if(mysqli_connect_error())
	{
		echo "<p>Some error occurred during database connection.</p>";
		echo "<p>Try after some time...exiting...</p>";
		exit();
	}
?>
<html>
<head>
	<title>PHP and MySQLi Fetching and Displaying Record from Table Example</title>
	<style>
		table th{text-align:left;padding:5px;border:2px solid black;background-color:black;color:white;}
		table td{padding:5px;border:1px solid green;}
	</style>
</head>
<body>
<?php
	$table = "codescrackerTable";
	$sqlQ = "SELECT * FROM $table LIMIT 0, 5";
	$res = $conn->query($sqlQ);
	if($res)
	{
		echo "<table>";
		echo "<tr>";
			echo "<th>S.No.</th>";
			echo "<th>User ID</th>";
			echo "<th>Comment</th>";
			echo "<th>Date</th>";
		echo "</tr>";
		while($row = mysqli_fetch_row($res))
		{
			echo "<tr>";
				echo "<td>$row[0]</td>";
				echo "<td>$row[2]</td>";
				echo "<td>$row[3]</td>";
				echo "<td>$row[1]</td>";
			echo "</tr>";
		}
		echo "</table>";
	}
	else 
	{
		echo "<p>Error occurred...exiting...</p>";
		exit();
	}
?>
</body>
</html>

In the above example, you can see that the tubular (table) form is used in displaying the record on the web and we have applied some style to the table, you can learn about styling the HTML element on CSS Tutorial.

Here is the sample output produced by the above example of PHP and MySQLi to fetching record from table.

php mysqli get display record from table

If you want to fetch and display last 5 records or all the latest 5 comments from the table, then you can add ORDER BY command in between the query. Or simply replace the following code:

$sqlQ = "SELECT * FROM $table LIMIT 0, 5";

with

$sqlQ = "SELECT * FROM $table ORDER BY datetime DESC LIMIT 0, 5";

After replacing the code in the above example and re-running the same program, then here is the output that will come out or produce into your browser:

PHP Online Test

« Previous Tutorial Next Tutorial »