In this example we will introduce the concept of gridviews. We will also learn how to display data in a gridview with PHP and HTML.
For this example we will use:
- A computer with PHP>= 5.5 installed
- notepad++
- Mysql Database
A grid view or a data grid is a graphical control element that displays data in a tabular view. Gridview in PHP is especially used to display data pulled from a database.
1. Getting Started
There are some commercial gridview software’s built to work with PHP but in this example we will build ours. We will create a simple web app that pulls data from a database and displays it in a gridview.
If you are developing on your local machine, you can download and install wamp. Wamp installs both php, phpmyadmin and mysql on your local machine. Furthermore, wamp doesn’t require any specific prior knowledge to get it working.
1.1 Initializing the database
This tutorial assumes you have php and mysql running. You should also create a database named authentication.
db.php
01 02 03 04 05 06 07 08 09 10 11 12 | <?php$dsn = "mysql:dbname=authentication"; $username = "root"; $password = ""; try{$conn = new PDO( $dsn, $username, $password ); $conn-<setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);}catch(PDOException $pd){echo $pd-<getMessage();}?> |
This script creates a connection to the database. We are using PDO (Php data objects) to access our database. The connection is stored in the $conn object and it is made available to manipulate the database.
We create a new PDO object in line 6 and surround it with a try/catch statement, so as to catch any error that might occur while trying to connect to the database.
init.php
01 02 03 04 05 06 07 08 09 10 11 12 | <?phprequire_once("db.php");try{$sql="create Table record(id smallint unsigned not null AUTO_INCREMENT PRIMARY KEY, name VARCHAR(265) NOT NULL,email VARCHAR(265) NOT NULL,department VARCHAR(265) NOT NULL,position varchar(255) NOT NULL)";$conn->exec($sql);echo "TABLE CREATED";}catch(PDOException $pd){echo "Error Creating Table: " . $pd->getMessage();}$conn=null;//close the database connection?> |
Before we start loading data into our gridview, you should load this script(init.php) into your browser (just once). The script creates a table that is called “record” in the database, this table will contain all the data that will be loaded into our gridview.
In line two we include the “db.php” script to login to the database. In line 5 we create a table in our database by calling the $conn->exec($sql) method, which takes a string as a parameter (the string holds the sql query).
1.2 Inserting Data into the database
Let’s populate our database. There are two ways we can populate our database. One way to do it, is to load “insertdata.php” in our browser. It would populate our record table.
insertdata.php
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | <?phprequire_once("db.php");try{//begin the transaction$conn->beginTransaction();//our sql statements$conn->exec("INSERT INTO record(name,email,department,position)VALUES('John Jack','e@example.com','accounting','accountant')");$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Mary Daniel','e@example.com','accounting','accountant')");$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Peace Daniel','e@example.com','accounting','accountant')");$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Sean Micheal','e@example.com','accounting','accountant')");$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Olive Sarah','e@example.com','accounting','accountant')");$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Micheal pater','e@example.com','I.T','Web Developer')");$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Ayo Brooks','e@example.com','I.T','Senior Web And Mobile Developer')");$conn->exec("INSERT INTO record(name,email,department,position)VALUES('Bran Judge','e@example.com','I.T','Senior Web And Mobile Developer')");//we commit the transaction$conn->commit();echo "New Record created successfully";}catch(PDOException $e){//roll back the transaction$conn->rollback();echo "An error occured:".$e->getMessage();}$conn=null;//close the database connection?> |
The above script shows us that we can insert mutiple records into a database with PHP, using PDO(PHP DATA OBJECTS).
The second way to populate our table is by using HTML forms (doing it manually).
insertdata1.php
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | <?phprequire_once("db.php");?><!DOCTYPE html> <html lang=en> <head> <style> html, body{ width:100%; height:100%; margin:0%; font-family:"helvetica", "verdana", "calibri", "san serif"; overflow: hidden; padding: 0%; border: 0%; } #hold{ padding: 20px; } input[type= text]{ width: 20%; height: 30px; } input[type= email]{ width: 20%; height: 30px; } </style> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, target-densitydpi=device-dpi"/> <title>Populate Database</title> </head> <body> <?php if(isset($_POST['submit'])){ $name=$_POST['firstName']; $email=$_POST['lastName']; $department=$_POST['dept']; $position=$_POST['pos']; if(empty(trim($name))||empty(trim($email))||empty(trim($department))||empty(trim($position))){ echo "Data Not Saved: No field should be empty <br>"; echo "<a href=insertdata1.php>Insert Another Data</a>"; } else{ $sql="INSERT INTO record(name, email, department, position)VALUES(:name, :email, :department, :position)"; $st=$conn->prepare($sql); $st->bindValue(":name", $name); $st->bindValue(":email", $email); $st->bindValue(":department", $department); $st->bindValue(":position", $position); $st->execute(); $st=null; echo "Data Saved <br>"; echo "<a href=insertdata1.php> Insert Another Data </a>"; } } else{ ?> <div id= hold> <form action=insertdata1.php method=post> <label for = firstName> Name </label> <br> <input type=text name=firstName id=firstName required /> <br> <br> <label for = lastName> Email </label> <br> <input type=email name=lastName id=lastName required/> <br> <br> <label for = department> department </label> <br> <input type=text name=dept id=dept required /> <br> <br> <label for = position> position </label> <br> <input type= text name=pos id=pos required /> <br> <br> <input type=submit value= submit name= submit></form></div><?php }?> </body> </html> |
1.3 Display Data
Now lets display the data stored in our database.
display.php
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | <?php//this connects to the databaserequire_once("db.php");?><!DOCTYPE html> <html lang=en> <head> <style> html, body{ width:100%; height:100%; margin:0%; font-family:"helvetica", "verdana", "calibri", "san serif"; overflow:hidden; padding:0%; border:0%; } table{ border:2px solid black; width:100%; } th,td{ width:20%; text-align:center; border:2px solid black; } </style> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, target-densitydpi=device-dpi"/> <title> Display Saved Data In GridView </title> </head> <body> <?php //we create a table echo "<table>"; // create table th echo "<tr > <th> ID </th> <th> Name </th> <th> Departmant </th> <th> Position </th> <th> Email </th> </tr>"; $sql=" select * from record "; $st=$conn->prepare($sql); $st->execute(); $total=$st->rowCount();//get the number of rows returned if($total < 1 ){//if no row was returned echo "<tr> <td style> No Data: DataBase Empty </td> ";//print out error message echo "<td> No Data: DataBase Empty </td> ";//print out error message echo " <td> No Data: DataBase Empty </td>";//print out error message echo " <td> No Data: DataBase Empty </td>";//print out error message echo "<td> No Data: DataBase Empty </td>";//print out error message } else{ while($res = $st->fetchObject()){//loop through the returned rows echo "<tr>"; echo "<td> $res->id </td> <td> $res->name </td> <td> $res->department </td> <td> $res->position </td> <td> $res->email </td>"; echo"</tr>"; } } ?> </table> <p> <a href=insertdata1.php> Insert Another Data </a> </p> </body> </html> |
We used HTML table to display our data in a gridview. Lets add a delete functionality to our app.
display1.php
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | <?php//this connects to the databaserequire_once("db.php");?><!DOCTYPE html> <html lang=en> <head> <style> html, body{ width:100%; height:100%; margin:0%; font-family:"helvetica", "verdana", "calibri", "san serif"; overflow:hidden; padding:0%; border:0%; } table{ border:2px solid black; width:100%; } th,td{ width:15%; text-align:center; border:2px solid black; } </style> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, target-densitydpi=device-dpi"/> <title> Display Saved Data In GridView </title> </head> <body> <script> function removes(id){ var ans = confirm("Are You Sure You Want To Delete This Row"); if(ans){//if true delete row window.location.assign("delete.php?id="+id); } else{//if false // do nothing } } </script> <?php //we create a table echo "<table>"; // create table th echo "<tr > <th> ID </th> <th> Name </th> <th> Departmant </th> <th> Position </th> <th> Email </th> <th> </th> </tr>"; $sql=" select * from record "; $st=$conn->prepare($sql); $st->execute(); $total=$st->rowCount();//get the number of rows returned if($total < 1 ){//if no row was returned echo "<tr> <td style> No Data: DataBase Empty </td> ";//print out error message echo "<td> No Data: DataBase Empty </td> ";//print out error message echo " <td> No Data: DataBase Empty </td>";//print out error message echo " <td> No Data: DataBase Empty </td>";//print out error message echo "<td> No Data: DataBase Empty </td>";//print out error message } else{ while($res = $st->fetchObject()){//loop through the returned rows echo "<tr>"; echo "<td> $res->id </td> <td> $res->name </td> <td> $res->department </td> <td> $res->position </td> <td> $res->email </td> <td> <a href=# onclick=removes($res->id)> Delete </a> </td>"; echo"</tr>"; } } ?> </table> <p> <a href=insertdata1.php> Insert Another Data </a> </p> </body> </html> |
We updated our code to include the ability to delete any row, by simply clicking on the delete link at the end of each row.
Any click on delete, will call our function remove(), Which takes the id of the row to delete as a parameter. The remove() function loads the script “delete.php” Which does the actual deletion.
delete.php
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 | <?phprequire_once("db.php");if(isset($_GET['id'])){$id=$_GET['id']; $sql="DELETE FROM record where id= :id"; try{$st=$conn->prepare($sql);$st->bindValue(":id", $id);$st->execute();header("Location:display.php");}catch(PDOException $e){ echo "An Error Occured: ". $e->getMessage();}}else{ echo "<h1>Wrong Request</h1>";}$conn=null;?> |
This script does the actual deletion and redirects the browser (A preffered way to call the “delete.php” is AJAX).
2. Summary
In this example we learnt about gridviews, what are they and how to create them in PHP with HTML tables. We also added delete function to our gridview.
3. Download the source code
