Connecting 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.Here is the code. <html> <head> <title></title> </head> <body > <?Php require "config.php"; // connection string is here ////////Query & Data Display is here///////// $q="select * from student where class='Four' order by id "; echo "<table>"; foreach ($dbo->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 records in page
Name | Krish Star |
Class | Four |
Mark | 60 |
Address | Krish Star_address |
Image | 4.jpg |
if($count->execute()){
echo " Success ";
$row = $count->fetch(PDO::FETCH_OBJ);
}
echo "<table>"; echo " <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> "; echo "</table>"; //////////////////// ?> </body> </html>
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 $count="SELECT name,id,class,mark,sex FROM student LIMIT 10"; if($stmt = $connection->query($count)){ 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 $connection->error; } ?>Displaying all details of single record
<?Php //////////////////////////////////////////// // Collecting data from query string $id=$_GET['id']; // Checking data it is a number or not if(!is_numeric($id)){ echo "Data Error"; exit; } // MySQL connection string require "config.php"; $count="SELECT * FROM student where id=?"; if($stmt = $connection->prepare($count)){ $stmt->bind_param('i',$id); $stmt->execute(); $result = $stmt->get_result(); echo "No of records : ".$result->num_rows."<br>"; $row=$result->fetch_object(); echo "<table>"; echo "<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> "; echo "</table>"; }else{ echo $connection->error; } ?> MySQLi connectionbind_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 )
PHP MySQL Displaying images from MySQL