zb7mba
Last Updated: May 09, 2019
·
143
· TerryStone

PHP and MYSQL DataGrid

DataGrid In PHP

Hi All,
I have created Hub for Php & Mysql datagrid, it is very rare to get combination of these both with Pagination you can download with free of cost.
I have done researched from past one month with this combination, i have got few programs but the function are abstract , to retrieve this function we need to pay.It is not free of cost, we can get only by using Master / Visa cards to retrieve this function.
I have developed this , as a quick reference with this combination hope it will be useful for all.Here we can do come impmentation for Add /Delete /Modify from the database... let me know if you need any so that i can also help you out.

Html page to add a New Record:

<html>
<head>
<title>Add Data</title>
</head>

<body>
<a href="index.php"><font color="#F4A460"><h4>Home</a>
<br/><br/>
<form action="add.php" method="post" name="form1">
<table width="25%" border="0">
<tr>
<td>RoleID</td>
<td><input type="text" name="Role
ID"></td>
</tr>
<tr>
<td>RoleName</td>
<td><input type="text" name="Role
Name"></td>
</tr>
<tr>
<td>CreatedBY</td>
<td><input type="text" name="Created
BY"></td>
</tr>
<tr>
<td>CreatedDatetime</td>
<td><input type="text" name="Created
Datetime"></td>
</tr>
<tr>
<td>UpdatedBy</td>
<td><input type="text" name="Updated
By"></td>
</tr>
<tr>
<td>UpdatedDatetime</td>
<td><input type="text" name="Updated
Datetime"></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="Submit" value="Add"></td>
</tr>

</table>

</form>
</body>
</html>

<html>
<head>
<title>Add Data</title>
</head>

<body>

<?php

include_once("config.php");

if(isset($_POST['Submit']))
{

$RoleID=$POST['RoleID'];
$Role
Name=$POST['RoleName'];
$CreatedBY=$POST['CreatedBY'];
$Created
Datetime=$POST['CreatedDatetime'];
$UpdatedBy=$POST['UpdatedBy'];
$Updated
Datetime=$POST['UpdatedDatetime'];

if(empty($RoleID) || empty($RoleName ) || empty($CreatedBY) || empty($CreatedDatetime) || empty($UpdatedBy) || empty($UpdatedDatetime))
{

if(empty($RoleID))
{
echo "<font color='red'>Role
ID field is empty.</font><br/>";
}

if(empty($RoleName))
{
echo "<font color='red'>Role
Name field is empty.</font><br/>";
}

if(empty($CreatedBY))
{
echo "<font color='red'>Created
BY field is empty.</font><br/>";
}
if(empty($CreatedDatetime))
{
echo "<font color='red'>Created
Datetime field is empty.</font><br/>";
}

if(empty($UpdatedBy))
{
echo "<font color='red'>Updated
By field is empty.</font><br/>";
}

if(empty($UpdatedDatetime))
{
echo "<font color='red'>Updated
Datetime field is empty.</font><br/>";
}

echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
}
else
{

$result=mysqlquery("INSERT INTO role(RoleID,RoleName,CreatedBY,CreatedDatetime,UpdatedBy,UpdatedDatetime) VALUES('$RoleID','$RoleName','$CreatedBY','$CreatedDatetime','$UpdatedBy','$Updated_Datetime')");
echo "<font color='#F4A460'><h4>Data added successfully.";
echo "<br/><a href='index.php'><font color='#F4A460'><h4>View Record</a>";
}
}
?>
</body>
</html>

Delete a Record:

<?php

include("config.php");
$id = $GET['id'];
$result=mysql
query("DELETE FROM role where id=$id");
header("Location:index.php");
?>

Edit a record:

<?php

include_once("config.php");

if(isset($_POST['update']))
{

$id = $_POST['id'];

$RoleID=$POST['RoleID'];
$Role
Name=$POST['RoleName'];
$CreatedBY=$POST['CreatedBY'];
$Created
Datetime=$POST['CreatedDatetime'];
$UpdatedBy=$POST['UpdatedBy'];
$Updated
Datetime=$POST['UpdatedDatetime'];

if(empty($RoleID) || empty($RoleName ) || empty($CreatedBY) || empty($CreatedDatetime) || empty($UpdatedBy) || empty($UpdatedDatetime))
{

if(empty($Role_ID))
{
echo "<font color='red'>Name field is empty.</font><br/>";
}

if(empty($Role_Name))
{
echo "<font color='red'>Age field is empty.</font><br/>";
}

if(empty($CreatedBY))
{
echo "<font color='red'>Email field is empty.</font><br/>";
}
if(empty($Created
Datetime))
{
echo "<font color='red'>Name field is empty.</font><br/>";
}

if(empty($Updated_By))
{
echo "<font color='red'>Age field is empty.</font><br/>";
}

if(empty($Updated_Datetime))
{
echo "<font color='red'>Email field is empty.</font><br/>";
}
}
else
{

$result=mysqlquery("UPDATE role SET RoleID='$RoleID',RoleName='$RoleName',CreatedBY='$CreatedBY',CreatedDatetime='$CreatedDatetime',UpdatedBy='$UpdatedBy',UpdatedDatetime='$Updated_Datetime' WHERE id=$id");

header("Location: index.php");
}
}
?>
<?php

$id = $_GET['id'];

$result=mysql_query("select * from role where id=$id");

while($res=mysqlfetcharray($result))
{
$RoleID = $res['RoleID'];
$RoleName = $res['RoleName'];
$CreatedBY = $res['CreatedBY'];
$CreatedDatetime = $res['CreatedDatetime'];
$UpdatedBy = $res['UpdatedBy'];
$UpdatedDatetime = $res['UpdatedDatetime'];
}
?>
<html>
<title>Edit Data</title>
<body>
<a href="index.php">Home</a>
<br/><br/>
<form name="form1" method="post" action="edit.php">
<table border="0">
<tr>
<td>RoleID</td>
<td>
<input type="text" name="Role
ID" value=<?php echo $RoleID;?>> </td>
</tr>
<tr>
<td>Role
Name</td>
<td>
<input type="text" name="RoleName" value=<?php echo $RoleName;?>> </td>
</tr>
<tr>
<td>CreatedBY</td>
<td>
<input type="text" name="Created
BY" value=<?php echo $CreatedBY;?>> </td>
</tr>
<tr>
<td>Created
Datetime</td>
<td>
<input type="text" name="CreatedDatetime" value=<?php echo $CreatedDatetime;?>> </td>
</tr>
<tr>
<td>UpdatedBy</td>
<td>
<input type="text" name="Updated
By" value=<?php echo $UpdatedBy;?>> </td>
</tr>
<tr>
<td>Updated
Datetime</td>
<td>
<input type="text" name="UpdatedDatetime" value=<?php echo $UpdatedDatetime;?>> </td>
</tr>
<tr>
<td><input type="hidden" name="id" value=<?php echo $_GET['id'];?>> </td>
<td><input type="submit" name="update" value="Update"></td>
</tr>
</table>
</form>
</body>
</html>

Index Page with paging:

<?php

include_once('config.php');

$sql_statement = 'SELECT * FROM role ORDER BY id DESC';

$numArray = mysqlquery($sql_statement);

$totalrecords = mysqlnumrows($numArray);

if(isset($GET['page']))
$page = $
GET['page'];
else
$page = 1;

$offset = 3;

if ($page){
$from = ($page * $offset) - $offset;
}else{

$from = 0;
}

$sqlstatement = 'SELECT * FROM role ORDER BY RoleId ASC LIMIT ' . $from . ',' . $offset;

//So, here is the result array which will have all the rows to display on to buy essay club page
$result = mysqlquery($sqlstatement);

?>
<html>
<head>
<title>Homepage</title>

</head>

<body>
<a href="add.html"><font color="#F4A460"><h4>+Add New Data</a><br/><br/>

<table>
<?php
echo "<table width='80%' border=2>";

echo "<tr bgcolor='#e2e0cb'>";
echo "<td align='center'>RoleID</td>";
echo "<td align='center'>Role
Name</td>";
echo "<td align='center'>CreatedBY</td>";
echo "<td align='center'>Created
Datetime</td>";
echo "<td align='center'>UpdatedBy</td>";
echo "<td align='center'>Updated
Datetime</td>";
echo "<td align='center'>Update</td>";
echo "</tr>";

while ($res = mysqlfetcharray($result)) {

echo "<tr bgcolor='#FFFFF0' >";
echo "<td>".$res['RoleID']."</td>";
echo "<td>".$res['Role
Name']."</td>";
echo "<td>".$res['CreatedBY']."</td>";
echo "<td>".$res['Created
Datetime']."</td>";
echo "<td>".$res['UpdatedBy']."</td>";
echo "<td>".$res['Updated
Datetime']."</td>";
echo "<td><a href=\"edit.php?id=$res[id]\">Edit</a> | <a href=\"delete.php?id=$res[id]\"></a></td>";

}
?>
<tr>
<td align="center" colspan="8" class="white">
<?PHP
doPages($offset, 'index.php', '', $total_records);
?>
</td>
</tr>

</table>

<?php
echo "</table>";
?>
</body>
</html>
<?php
function checkinteger($which) {
if(isset($
REQUEST[$which])){
if (intval($_REQUEST[$which])>0) {

return intval($_REQUEST[$which]);
} else {
return false;
}
}
return false;
}

function getcurrentpage() {
if(($var=check_integer('page'))) {
return $var;
} else {
//return 1, if it wasn't set before, page=1
return 1;
}
}

function doPages($pagesize, $thepage, $querystring, $total=0) {

$index_limit = 4;

$query='';

if(strlen($querystring)>0){
$query = "&".$query
string;
}

$current = getcurrentpage();

$totalpages=ceil($total/$pagesize);
$start=max($current-intval($indexlimit/2), 1);
$end=$start+$index
limit-1;

echo '<div class="paging">';

if($current==1) {
echo '<span class="prn">< Previous</span> ';
} else {
$i = $current-1;
echo '<a href="'.$thepage.'?page='.$i.$query.'" class="prn" rel="nofollow" title="go to page '.$i.'">< Previous</a> ';
echo '<span class="prn">...</span> ';
}

if($start > 1) {
$i = 1;
echo '<a href="'.$thepage.'?page='.$i.$query.'" title="go to page '.$i.'">'.$i.'</a> ';
}

for ($i = $start; $i <= $end && $i <= $total_pages; $i++){
if($i==$current) {
echo '<span>'.$i.'</span> ';
} else {
echo '<a href="'.$thepage.'?page='.$i.$query.'" title="go to page '.$i.'">'.$i.'</a> ';
}
}

if($totalpages > $end){
$i = $total
pages;
echo '<a href="'.$thepage.'?page='.$i.$query.'" title="go to page '.$i.'">'.$i.'</a> ';
}

if($current < $total_pages) {
$i = $current+1;
echo '<span class="prn">...</span> ';
echo '<a href="'.$thepage.'?page='.$i.$query.'" class="prn" rel="nofollow" title="go to page '.$i.'">Next ></a> ';
} else {
echo '<span class="prn">Next ></span> ';
}

if ($total != 0){
//prints the total result count just below the paging
echo '

<font color="#F4A460"<h4>(Total '.$total.' Records)</h></p></div>';
}

}

?>
Table:
create table Role( id int(11) NOT NULL autoincrement,RoleID int NOT NULL,RoleName varchar(100) NOT NULL,CreatedBY varchar(100) NOT NULL,CreatedDatetime datetime NOT NULL,UpdatedBy varchar(100),Updated_Datetime datetime,PRIMARY KEY (id));

NOTE: select an image for Delete option.