Displaying single record from MySQL table using PHP

We have seen how to display records of a mysql table here. Now we will learn how to display one record in a single page. This is required where full details of a record are to be shown in a page. Usually the records unique id is used to collect the details from the table. So same php page is used and the value of unique id of the record is taken as a variable. Before that links to different records are displayed and on click the full details are shown. So we will fist start with displaying a group of records with link to individual records.

Watch this Video Tutorial about this script

Connecting to database and executing Query 🔝

To manage data we have to connect to MySQL database and execute query to get our date. Here there are two ways to use PHP drivers to connect to MySQL and execute the functions for getting records.

One is using Portable Data Object ( PDO )
Second one is MySQLI ( MysQL Improved )

We will learn both here. We will first use PDO and at the end we will use MySQLI. You can use any one for your script.

Displaying Links to individual records 🔝

We will use our student table where we have added two more columns ( fields ) to store address and image. As we have more records so to restrict number of records we will display records of a particular class. The query is here.

select * from student where class='Four' order  by  id

Using the above query we will display the records by keeping them inside an html table. At the top of the script we will connect to MySQL database.
$my_conn is our connection object, declared inside config.php file.
Here is the code.

<html>
<head>
<title></title>
</head>
<body >

<?Php
require "config.php"; // connection string is here

////////Query & Data Display is here/////////

$sql="select * from student where class='Four' order  by  id ";
echo "<table>";
foreach ($my_conn->query($sql) as $row) {
echo "<tr><td><a href=details.php?id=$row[id]>$row[name]</a></td><td>$row[class]</td></tr>";
}
echo "</table>";
/////////////////////////////////////  
?>
</body>
</html>

You can see we have used the name field to display a hyper link and by clicking that the individual record can be displayed in details.php page. We have formatted the hyper link to carry the student id in query string.

<a href=details.php?id=$row[id]>$row[name]</a>

The output of above code is here

Krish Star	Four
John Mike	Four
Alex John	Four
Big John	Four
Tade Row	Four
Gimmy	Four
Babby John	Four
Marry Toeey	Four

Displaying individual record details in a page 🔝

By clicking the hyper link the address bar will carry the student id to details.php page to use inside our query. But before using this student id we have to sanitize this data as it can be changed to carry out injection attack. There are different ways to do this but we will check if the data is numeric or not and exit the page execution if we found non-numeric value is stored in id variable. Here is the code to this part.

$id=$_GET['id'];        // Collecting data from query string
if(!is_numeric($id)){ // Checking data it is a number or not
echo "Data Error";
exit;
}

Now we can use this id value inside our query.

select * from student where id=:id

In above query we have collected the data for the individual record. Now we will display all the fields of the record like this.

Name	Krish Star
Class	Four
Mark	60
Address	Krish Star_address
Image	4.jpg

Here is the code for this.

<html>
<head>
<title></title>

</head>
<body >

<?Php
require "config.php"; // database connection with $my_conn

//////Displaying Data/////////////
$id=$_GET['id'];        // Collecting data from query string
if(!is_numeric($id)){ // Checking data it is a number or not
echo "Data Error";    
exit;
}

$count=$my_conn->prepare("select * from student where id=:id ");
$count->bindParam(":id",$id,PDO::PARAM_INT,3);

if($count->execute()){
echo " Success ";
$row = $count->fetch(PDO::FETCH_OBJ);
}

echo "<table>
<tr bgcolor='#f1f1f1'><td><b>Name</b></td><td>$row->name</td></tr>
<tr><td><b>Class</b></td><td>$row->class</td></tr>
<tr bgcolor='#f1f1f1'><td><b>Mark</b></td><td>$row->mark</td></tr>
<tr><td><b>Address</b></td><td>$row->address</td></tr>
<tr bgcolor='#f1f1f1'><td><b>Image</b></td><td>$row->img</td></tr>
</table>";
////////////////////  
?>
</body>
</html>

Using SQLite Database 🔝

Configuring MySQL or SQLite database connection

Above code will work for both MySQL database with PHP PDO connection and SQLite database. The connection object is to be changed inside the config.php file.

One sample SQLite database student.db with student table is available inside zip file for download below.

OR you can create your SQLite database by using sqlite-student-dump.sql file available inside the zip file.

Following changes required inside config.php file to switch over to SQLite database.

<?Php
#### FOR SQLite Database ######
// This will create the database if not exists in the same location where the script is running.
// For a different location give the path details. 
//$my_conn = new PDO('sqlite:D:\\sqlite-data\\student.db');// different path
$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/student.db'); // same location
$my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
#### end of SQLite database connection #####


###### PHP PDO with MySQL ##### For php PDO use below lines ##
/*
$host_name = "localhost"; // or different host 
$database = "my_db";   // Change your database name
$username = "root";    // Your database user id 
$password = "password";// Your password

//////// Do not Edit below /////////
try {
$my_conn = new PDO('mysql:host='.$host_name.';dbname='.$database, $username, $password);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
*/
####### END of PHP PDO ######
?>

Displaying records using MySQLI functions 🔝

First we will display all records and then on user clicking of link the full details of the student can be displayed.

<?Php
require "config.php"; // MySQL connection string, $my_conn

$query="SELECT * FROM student LIMIT 10";

if($stmt = $my_conn->query($query)){
echo "<table>";

while ($row = $stmt->fetch_assoc()) {
echo "<tr><td><a href=details.php?id=$row[id]>$row[name]</a></td>
<td>$row[class]</td></tr>";
}

echo "</table>";
}else{
echo $my_conn->error;
}
?>

Displaying all details of single record 🔝

<?Php
// Collecting data from query string
$id=$_GET['id'];
// Checking data ,it is an integer or not
if(!is_numeric($id)){
echo "Data Error";
exit;
}
// MySQL connection string
require "config.php"; // $my_conn is here 

$query="SELECT *  FROM student where id=?";

if($stmt = $my_conn->prepare($query)){
  $stmt->bind_param('i',$id); // parameter checking 
  $stmt->execute();

 $result = $stmt->get_result();
 echo "No of records : ".$result->num_rows."<br>";
 $row=$result->fetch_object();
 echo "<table><tr ><td><b>Name</b></td><td>$row->name</td></tr>
<tr><td><b>Class</b></td><td>$row->class</td></tr>
<tr ><td><b>Mark</b></td><td>$row->mark</td></tr>
<tr><td><b>Address</b></td><td>$row->address</td></tr>
<tr ><td><b>Image</b></td><td>$row->img</td></tr>
</table><BR><BR><a href=index.php>Back to listing</a>";
}else{
echo $my_conn->error;
}
?>

MySQLi connection
bind_param()
MySQLi select query to get data

Displaying Image stored in MySQL table 🔝

Usually images are stored in any folder or directory but the name of the image file is stored in a field so to display the photo we will format the image file name by using html img tag. Here is an example to display the photo in place of file name ( replace $row->img with this code ).

<img src=dir_name/$row->img>

To know more about how to store file name in table and manage images along with thumbnail using MySQL database , read photo gallery script

Here is the dump of the student table ( with address and image column )

Download the zip file with records 🔝

Questions 🔝

How do you retrieve a single record from a MySQL table in PHP?
What are the different methods to fetch a single record from a MySQL table using PHP, such as mysqli and PDO?
How do you establish a database connection in PHP to retrieve a single record from a MySQL table?
What is the process of writing a SQL query to fetch a specific record from a MySQL table?
How do you handle scenarios where the record to be displayed is based on a specific condition or criteria?
How do you handle cases where the record being displayed is not found in the MySQL table?
What is the best practice for sanitizing user input when retrieving a single record from a MySQL table to prevent SQL injection?
How do you format and display the retrieved record in a visually appealing manner using HTML and PHP?
Can you display specific columns from the fetched record instead of displaying all columns?
How do you handle and display related data or foreign key references from other tables in the single record being displayed?

PHP MySQL Displaying images from MySQL

Subscribe to our YouTube Channel here

Eagertolearn	19-03-2013
Great tutorial. I was banging my head for more than 2 weeks on how to accomplish this task. Thanks for sharing. Well explained. I would like to go 1 step further. How can I get the database result opens in a new page. Thanks

Stanley	28-09-2013
Hey man I was looking for this, you have safe my life, thank you very much...

John	14-10-2013
Hi how can I fix the problem with Notice: Undefined index: id. I have tested with $id = isset($_GET['id']) ? $_GET['id'] : ''; but send a Data Error; but everything is OK what am I doing wrong?

smo1234	17-05-2017
database management using mysqli ( instead of PDO ) is added.

B K SAHU	05-10-2017
I want multiple drop down menu php code. plz help me

Rhylex	17-12-2018
how to link each data to each profile page using id

rhodz	04-02-2019
hi can I ask what are the url names of the codes like index.php? I didn't work because I don't know what will I put when I save it. Thank you

smo1234	08-02-2019
Each record can be linked by using userid or id and details of the profile can be shown as explained above select * from student where id=:id

smo1234	08-02-2019
File names can be anything, you must match the links to the file names.

effefef	19-07-2013
Simple and Good.

	06-10-2021
Good tutorial

All details of a single record of MySQL table in one page