- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming
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>Role_ID</td>
<td><input type="text" name="Role_ID"></td>
</tr>
<tr>
<td>Role_Name</td>
<td><input type="text" name="Role_Name"></td>
</tr>
<tr>
<td>Created_BY</td>
<td><input type="text" name="Created_BY"></td>
</tr>
<tr>
<td>Created_Datetime</td>
<td><input type="text" name="Created_Datetime"></td>
</tr>
<tr>
<td>Updated_By</td>
<td><input type="text" name="Updated_By"></td>
</tr>
<tr>
<td>Updated_Datetime</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']))
{
$Role_ID=$_POST['Role_ID'];
$Role_Name=$_POST['Role_Name'];
$Created_BY=$_POST['Created_BY'];
$Created_Datetime=$_POST['Created_Datetime'];
$Updated_By=$_POST['Updated_By'];
$Updated_Datetime=$_POST['Updated_Datetime'];
if(empty($Role_ID) || empty($Role_Name ) || empty($Created_BY) || empty($Created_Datetime) || empty($Updated_By) || empty($Updated_Datetime))
{
if(empty($Role_ID))
{
echo "<font color='red'>Role_ID field is empty.</font><br/>";
}
if(empty($Role_Name))
{
echo "<font color='red'>Role_Name field is empty.</font><br/>";
}
if(empty($Created_BY))
{
echo "<font color='red'>Created_BY field is empty.</font><br/>";
}
if(empty($Created_Datetime))
{
echo "<font color='red'>Created_Datetime field is empty.</font><br/>";
}
if(empty($Updated_By))
{
echo "<font color='red'>Updated_By field is empty.</font><br/>";
}
if(empty($Updated_Datetime))
{
echo "<font color='red'>Updated_Datetime field is empty.</font><br/>";
}
echo "<br/><a href='javascript:self.history.back();'>Go Back</a>";
}
else
{
$result=mysql_query("INSERT INTO role(Role_ID,Role_Name,Created_BY,Created_Datetime,Updated_By,Updated_Datetime) VALUES('$Role_ID','$Role_Name','$Created_BY','$Created_Datetime','$Updated_By','$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'];
$Role_ID=$_POST['Role_ID'];
$Role_Name=$_POST['Role_Name'];
$Created_BY=$_POST['Created_BY'];
$Created_Datetime=$_POST['Created_Datetime'];
$Updated_By=$_POST['Updated_By'];
$Updated_Datetime=$_POST['Updated_Datetime'];
if(empty($Role_ID) || empty($Role_Name ) || empty($Created_BY) || empty($Created_Datetime) || empty($Updated_By) || empty($Updated_Datetime))
{
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($Created_BY))
{
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=mysql_query("UPDATE role SET Role_ID='$Role_ID',Role_Name='$Role_Name',Created_BY='$Created_BY',Created_Datetime='$Created_Datetime',Updated_By='$Updated_By',Updated_Datetime='$Updated_Datetime' WHERE id=$id");
header("Location: index.php");
}
}
?>
<?php
$id = $_GET['id'];
$result=mysql_query("select * from role where id=$id");
while($res=mysql_fetch_array($result))
{
$Role_ID = $res['Role_ID'];
$Role_Name = $res['Role_Name'];
$Created_BY = $res['Created_BY'];
$Created_Datetime = $res['Created_Datetime'];
$Updated_By = $res['Updated_By'];
$Updated_Datetime = $res['Updated_Datetime'];
}
?>
<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>Role_ID</td>
<td>
<input type="text" name="Role_ID" value=<?php echo $Role_ID;?>> </td>
</tr>
<tr>
<td>Role_Name</td>
<td>
<input type="text" name="Role_Name" value=<?php echo $Role_Name;?>> </td>
</tr>
<tr>
<td>Created_BY</td>
<td>
<input type="text" name="Created_BY" value=<?php echo $Created_BY;?>> </td>
</tr>
<tr>
<td>Created_Datetime</td>
<td>
<input type="text" name="Created_Datetime" value=<?php echo $Created_Datetime;?>> </td>
</tr>
<tr>
<td>Updated_By</td>
<td>
<input type="text" name="Updated_By" value=<?php echo $Updated_By;?>> </td>
</tr>
<tr>
<td>Updated_Datetime</td>
<td>
<input type="text" name="Updated_Datetime" value=<?php echo $Updated_Datetime;?>> </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';
$num_Array = mysql_query($sql_statement);
$total_records = mysql_num_rows($num_Array);
if(isset($_GET['page']))
$page = $_GET['page'];
else
$page = 1;
$offset = 3;
if ($page){
$from = ($page * $offset) - $offset;
}else{
$from = 0;
}
$sql_statement = 'SELECT * FROM role ORDER BY Role_Id ASC LIMIT ' . $from . ',' . $offset;
//So, here is the result array which will have all the rows to display on to page
$result = mysql_query($sql_statement);
?>
<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'>Role_ID</td>";
echo "<td align='center'>Role_Name</td>";
echo "<td align='center'>Created_BY</td>";
echo "<td align='center'>Created_Datetime</td>";
echo "<td align='center'>Updated_By</td>";
echo "<td align='center'>Updated_Datetime</td>";
echo "<td align='center'>Update</td>";
echo "</tr>";
while ($res = mysql_fetch_array($result)) {
echo "<tr bgcolor='#FFFFF0' >";
echo "<td>".$res['Role_ID']."</td>";
echo "<td>".$res['Role_Name']."</td>";
echo "<td>".$res['Created_BY']."</td>";
echo "<td>".$res['Created_Datetime']."</td>";
echo "<td>".$res['Updated_By']."</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]\"><img src='index2.gif' height=15 width=15 title='Delete record' style='cursor:pointer; vertical-align: middle;'></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 check_integer($which) {
if(isset($_REQUEST[$which])){
if (intval($_REQUEST[$which])>0) {
return intval($_REQUEST[$which]);
} else {
return false;
}
}
return false;
}
function get_current_page() {
if(($var=check_integer('page'))) {
return $var;
} else {
//return 1, if it wasn't set before, page=1
return 1;
}
}
function doPages($page_size, $thepage, $query_string, $total=0) {
$index_limit = 4;
$query='';
if(strlen($query_string)>0){
$query = "&".$query_string;
}
$current = get_current_page();
$total_pages=ceil($total/$page_size);
$start=max($current-intval($index_limit/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($total_pages > $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 '<p id="total_count"><font color="#F4A460"<h4>(Total '.$total.' Records)</h></p></div>';
}
}
?>
Table:
create table Role( id int(11) NOT NULL auto_increment,Role_ID int NOT NULL,Role_Name varchar(100) NOT NULL,Created_BY varchar(100) NOT NULL,Created_Datetime datetime NOT NULL,Updated_By varchar(100),Updated_Datetime datetime,PRIMARY KEY (id));
NOTE: select an image for Delete option.