PHP

PHP GridView Example

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:

  1. A computer with PHP>= 5.5 installed
  2. notepad++
  3. 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

Download
You can download the full source code of this example here: phpgridviewexample

Olayemi Odunayo

I am a programmer and web developer, who has experience in developing websites and writing desktop and mobile applications. I have worked with both schematic and schemaless databases. I am also familiar with third party API and working with cloud servers. I do programming on the client side with Java, JavaScript, html, Ajax and CSS while I use PHP for server side programming.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button