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.
\nFor this example we will use:<\/p>\n
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.
\n
\n
\n[ulp id=’8njY7i2QRy6sg8pg’]<\/p>\n
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.<\/p>\n
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\u2019t require any specific prior knowledge to get it working.<\/p>\n
This tutorial assumes you have php and mysql running. You should also create a database named authentication.<\/p>\n
db.php<\/em><\/span><\/p>\n 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. init.php<\/em><\/span><\/p>\n 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 \u201crecord\u201d in the database, this table will contain all the data that will be loaded into our gridview. 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.<\/p>\n insertdata.php<\/em><\/span><\/p>\n The above script shows us that we can insert mutiple records into a database with PHP, using PDO(PHP DATA OBJECTS). insertdata1.php<\/em><\/span><\/p>\n Now lets display the data stored in our database.<\/p>\n display.php<\/em><\/span><\/p>\n We used HTML table to display our data in a gridview. Lets add a delete functionality to our app.<\/p>\n display1.php<\/em><\/span><\/p>\n 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. delete.php<\/em><\/span><\/p>\n This script does the actual deletion and redirects the browser (A preffered way to call the “delete.php” is AJAX).<\/p>\n 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.<\/p>\n<?php\r\n$dsn = \"mysql:dbname=authentication\"; \r\n$username = \"root\"; \r\n$password = \"\"; \r\ntry{\r\n$conn = new PDO( $dsn, $username, $password ); \r\n$conn-<setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);\r\n}\r\ncatch(PDOException $pd){\r\necho $pd-<getMessage();\r\n}\r\n?>\r\n<\/pre>\n
\nWe 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.<\/p>\n<?php\r\nrequire_once(\"db.php\");\r\ntry{\r\n$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)\";\r\n$conn->exec($sql);\r\necho \"TABLE CREATED\";\r\n}\r\ncatch(PDOException $pd){\r\necho \"Error Creating Table: \" . $pd->getMessage();\r\n}\r\n$conn=null;\/\/close the database connection\r\n?>\r\n<\/pre>\n
\nIn line two we include the \u201cdb.php\u201d 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).<\/p>\n1.2 Inserting Data into the database<\/h3>\n
<?php\r\nrequire_once(\"db.php\");\r\ntry{\r\n\/\/begin the transaction\r\n$conn->beginTransaction();\r\n\/\/our sql statements\r\n$conn->exec(\"INSERT INTO record(name,email,department,position)VALUES('John Jack','e@example.com','accounting','accountant')\");\r\n$conn->exec(\"INSERT INTO record(name,email,department,position)VALUES('Mary Daniel','e@example.com','accounting','accountant')\");\r\n$conn->exec(\"INSERT INTO record(name,email,department,position)VALUES('Peace Daniel','e@example.com','accounting','accountant')\");\r\n$conn->exec(\"INSERT INTO record(name,email,department,position)VALUES('Sean Micheal','e@example.com','accounting','accountant')\");\r\n$conn->exec(\"INSERT INTO record(name,email,department,position)VALUES('Olive Sarah','e@example.com','accounting','accountant')\");\r\n$conn->exec(\"INSERT INTO record(name,email,department,position)VALUES('Micheal pater','e@example.com','I.T','Web Developer')\");\r\n$conn->exec(\"INSERT INTO record(name,email,department,position)VALUES('Ayo Brooks','e@example.com','I.T','Senior Web And Mobile Developer')\");\r\n$conn->exec(\"INSERT INTO record(name,email,department,position)VALUES('Bran Judge','e@example.com','I.T','Senior Web And Mobile Developer')\");\r\n\/\/we commit the transaction\r\n$conn->commit();\r\necho \"New Record created successfully\";\r\n}\r\ncatch(PDOException $e){\r\n\/\/roll back the transaction\r\n$conn->rollback();\r\necho \"An error occured:\".$e->getMessage();\r\n}\r\n$conn=null;\/\/close the database connection\r\n\r\n\r\n?>\r\n<\/pre>\n
\nThe second way to populate our table is by\u00a0using HTML forms (doing it manually).<\/p>\n<?php\r\nrequire_once(\"db.php\");\r\n?>\r\n<!DOCTYPE html> \r\n<html lang=en>\r\n\t<head>\r\n\t<style>\r\n\thtml, body{\r\n\twidth:100%;\r\n\theight:100%;\r\n\tmargin:0%;\r\n\tfont-family:\"helvetica\", \"verdana\", \"calibri\", \"san serif\";\r\n\toverflow: hidden;\r\n\tpadding: 0%;\r\n\tborder: 0%;\r\n\t}\r\n\t#hold{\r\n\t\tpadding: 20px;\r\n\t}\r\n\tinput[type= text]{\r\n\t\twidth: 20%;\r\n\t\theight: 30px;\r\n\t}\r\n\tinput[type= email]{\r\n\t\twidth: 20%;\r\n\t\theight: 30px;\r\n\t}\r\n\t\r\n\t<\/style>\r\n\t \t\t<meta charset=\"utf-8\" \/>\r\n\t\t<meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, target-densitydpi=device-dpi\"\/>\r\n\t\r\n\t<title>Populate Database<\/title>\r\n\t\r\n\t<\/head>\r\n\t<body>\r\n\t<?php\r\n\tif(isset($_POST['submit'])){\r\n\t\t$name=$_POST['firstName'];\r\n\t\t$email=$_POST['lastName'];\r\n\t\t$department=$_POST['dept'];\r\n\t\t$position=$_POST['pos'];\r\n\tif(empty(trim($name))||empty(trim($email))||empty(trim($department))||empty(trim($position))){\r\n\t\techo \"Data Not Saved: No field should be empty <br>\";\r\n\t\techo \"<a href=insertdata1.php>Insert Another Data<\/a>\";\r\n\t\t\r\n\t}\r\n\telse{\r\n\t\t$sql=\"INSERT INTO record(name, email, department, position)VALUES(:name, :email, :department, :position)\";\r\n\t\t$st=$conn->prepare($sql);\r\n\t\t$st->bindValue(\":name\", $name);\r\n\t\t$st->bindValue(\":email\", $email);\r\n\t\t$st->bindValue(\":department\", $department);\r\n\t\t$st->bindValue(\":position\", $position);\r\n\t\t$st->execute();\r\n\t\t$st=null;\r\n\t\techo \"Data Saved <br>\";\r\n\t\techo \"<a href=insertdata1.php> Insert Another Data <\/a>\";\r\n\t}\r\n\t}\r\n\telse{\r\n\t?>\r\n\t<div id= hold>\r\n\t<form action=insertdata1.php method=post> \r\n <label for = firstName> Name <\/label> <br> <input type=text name=firstName id=firstName required \/> <br> <br>\r\n <label for = lastName> Email <\/label> <br> <input type=email name=lastName id=lastName required\/> <br> <br>\r\n <label for = department> department <\/label> <br> <input type=text name=dept id=dept required \/> <br> <br> \r\n<label for = position> position <\/label> <br> <input type= text name=pos id=pos required \/> <br> <br> \r\n<input type=submit value= submit name= submit>\r\n<\/form>\r\n<\/div>\r\n<?php\r\n\t}\r\n?>\r\n\t<\/body>\r\n\t<\/html>\r\n<\/pre>\n1.3 Display Data<\/h3>\n
<?php\r\n\/\/this connects to the database\r\nrequire_once(\"db.php\");\r\n?>\r\n<!DOCTYPE html> \r\n<html lang=en>\r\n\t<head>\r\n\t<style>\r\n\thtml, body{\r\n\twidth:100%;\r\n\theight:100%;\r\n\tmargin:0%;\r\n\tfont-family:\"helvetica\", \"verdana\", \"calibri\", \"san serif\";\r\n\toverflow:hidden;\r\n\tpadding:0%;\r\n\tborder:0%;\r\n\t}\r\n\ttable{\r\n\t\tborder:2px solid black;\r\n\t\twidth:100%;\r\n\t}\r\n\tth,td{\r\n\t\twidth:20%;\r\n\ttext-align:center;\r\n border:2px solid black;\t\r\n\t}\r\n\t\r\n\t<\/style>\r\n\t \t\t<meta charset=\"utf-8\" \/>\r\n\t\t<meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, target-densitydpi=device-dpi\"\/>\r\n\t\r\n\t<title> Display Saved Data In GridView <\/title>\r\n\r\n\t<\/head>\r\n\t<body>\r\n\t<?php\r\n\t\/\/we create a table\r\n\techo \"<table>\";\r\n\t\/\/ create table th \r\n\techo \"<tr > <th> ID <\/th> <th> Name <\/th> <th> Departmant <\/th> <th> Position <\/th> <th> Email <\/th> <\/tr>\";\r\n\t$sql=\" select * from record \";\r\n\t$st=$conn->prepare($sql);\r\n\t$st->execute();\r\n\t$total=$st->rowCount();\/\/get the number of rows returned\r\n\tif($total < 1 ){\/\/if no row was returned\r\n\t\techo \"<tr> <td style> No Data: DataBase Empty <\/td> \";\/\/print out error message\r\n\t\techo \"<td> No Data: DataBase Empty <\/td> \";\/\/print out error message\r\n\t\techo \" <td> No Data: DataBase Empty <\/td>\";\/\/print out error message\r\n\t\techo \" <td> No Data: DataBase Empty <\/td>\";\/\/print out error message\r\n\t echo \"<td> No Data: DataBase Empty <\/td>\";\/\/print out error message\r\n\t\t\r\n\t}\r\n\t\telse{\r\n\twhile($res = $st->fetchObject()){\/\/loop through the returned rows\r\n\t\techo \"<tr>\";\r\n\t\techo \"<td> $res->id <\/td> <td> $res->name <\/td> <td> $res->department <\/td> <td> $res->position <\/td> <td> $res->email <\/td>\";\r\n\t\techo\"<\/tr>\";\r\n\t}\r\n\t}\r\n\t?>\r\n\t<\/table>\r\n\t<p>\r\n\t<a href=insertdata1.php> Insert Another Data <\/a>\r\n\t<\/p>\r\n\t<\/body>\r\n\t<\/html>\r\n<\/pre>\n<?php\r\n\/\/this connects to the database\r\nrequire_once(\"db.php\");\r\n?>\r\n<!DOCTYPE html> \r\n<html lang=en>\r\n\t<head>\r\n\t<style>\r\n\thtml, body{\r\n\twidth:100%;\r\n\theight:100%;\r\n\tmargin:0%;\r\n\tfont-family:\"helvetica\", \"verdana\", \"calibri\", \"san serif\";\r\n\toverflow:hidden;\r\n\tpadding:0%;\r\n\tborder:0%;\r\n\t}\r\n\ttable{\r\n\t\tborder:2px solid black;\r\n\t\twidth:100%;\r\n\t}\r\n\tth,td{\r\n\t\twidth:15%;\r\n\ttext-align:center;\r\n border:2px solid black;\t\r\n\t}\r\n\t\r\n\t<\/style>\r\n\t \t\t<meta charset=\"utf-8\" \/>\r\n\t\t<meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, target-densitydpi=device-dpi\"\/>\r\n\t\r\n\t<title> Display Saved Data In GridView <\/title>\r\n\r\n\t<\/head>\r\n\t<body>\r\n\t<script>\r\n\tfunction removes(id){\r\n\t\t\r\n\tvar ans = confirm(\"Are You Sure You Want To Delete This Row\");\r\n\tif(ans){\/\/if true delete row\r\n\t\twindow.location.assign(\"delete.php?id=\"+id);\r\n\t}\r\n\telse{\/\/if false \r\n\t\t\/\/ do nothing\r\n\t}\r\n\t}\r\n\t\r\n\t\r\n\t<\/script>\r\n\t<?php\r\n\t\/\/we create a table\r\n\techo \"<table>\";\r\n\t\/\/ create table th \r\n\techo \"<tr > <th> ID <\/th> <th> Name <\/th> <th> Departmant <\/th> <th> Position <\/th> <th> Email <\/th> <th> <\/th> <\/tr>\";\r\n\t$sql=\" select * from record \";\r\n\t$st=$conn->prepare($sql);\r\n\t$st->execute();\r\n\t$total=$st->rowCount();\/\/get the number of rows returned\r\n\tif($total < 1 ){\/\/if no row was returned\r\n\t\techo \"<tr> <td style> No Data: DataBase Empty <\/td> \";\/\/print out error message\r\n\t\techo \"<td> No Data: DataBase Empty <\/td> \";\/\/print out error message\r\n\t\techo \" <td> No Data: DataBase Empty <\/td>\";\/\/print out error message\r\n\t\techo \" <td> No Data: DataBase Empty <\/td>\";\/\/print out error message\r\n\t echo \"<td> No Data: DataBase Empty <\/td>\";\/\/print out error message\r\n\t\t\r\n\t}\r\n\t\telse{\r\n\twhile($res = $st->fetchObject()){\/\/loop through the returned rows\r\n\t\techo \"<tr>\";\r\n\t\techo \"<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>\";\r\n\t\techo\"<\/tr>\";\r\n\t}\r\n\t}\r\n\t?>\r\n\t<\/table>\r\n\t<p>\r\n\t<a href=insertdata1.php> Insert Another Data <\/a>\r\n\t<\/p>\r\n\t<\/body>\r\n\t<\/html>\r\n<\/pre>\n
\nAny click on delete, will call our function remove()<\/code>, Which takes the id of the row to delete as a parameter. The remove()<\/code> function loads the script “delete.php” Which does the actual deletion.<\/p>\n<?php\r\nrequire_once(\"db.php\");\r\nif(isset($_GET['id'])){\r\n$id=$_GET['id'];\t\r\n$sql=\"DELETE FROM record where id= :id\";\t\r\ntry{\r\n$st=$conn->prepare($sql);\r\n$st->bindValue(\":id\", $id);\r\n$st->execute();\r\nheader(\"Location:display.php\");\r\n}\r\ncatch(PDOException $e){\r\n\techo \"An Error Occured: \". $e->getMessage();\r\n}\r\n}\r\nelse{\r\n\techo \"<h1>Wrong Request<\/h1>\";\r\n}\r\n\r\n$conn=null;\r\n?>\r\n<\/pre>\n2. Summary<\/h2>\n
3. Download the source code<\/h2>\n