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
<?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
<?php
require_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
<?php
require_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
<?php
require_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
<?php
//this connects to the database
require_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
<?php
//this connects to the database
require_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
<?php
require_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
