I bought the following script http://www.sevenscript.net/ajax-powered-mysql-table-editor/.
It is a ajax table editor with in-line editing
It comes with the following files.
ajaxtable.js
/*Sevenscript <http://sevenscript.net> - Copyright (c) 2010 <
[email protected]>*/
//Declare Some Global Variables
var cancelString;
var editTblRow;
var editTblCell;
var editSQLRowID;
var editKey;
var editCancel;
function showDelete(id) {
$("#del_" + id).toggle();
}
function loadTable(){
$("#ajaxTable").load("table.php");
}
function insertRow(){
$("#ajaxTable").load("dbFunc.php?action=new");
}
function selectTable(optvalue){
editTblRow = null;
editTblCell = null;
$("#active_table").val(optvalue);
$("#ajaxTable").load("table.php?table=" + optvalue);
}
function loadingTimer(){
$("#ajax_loading_div")
.bind("ajaxSend", function(){
$(this).show();
})
.bind("ajaxComplete", function(){
$(this).hide();
});
}
function delRow(thisRow,rowID){
var delConfirm = confirm("Delete this row?");
if (delConfirm){
$.get("dbFunc.php", { action: 'delete', rowid: rowID },
function(data){
$(thisRow).parent().fadeOut(500, function() {$(thisRow).remove(); });
}); }
}
function cancelInsert(rowID){
$.get("dbFunc.php", { action: 'delete', rowid: rowID },
function(data){
loadTable();
});
}
function toggleDelRow(rowid){
$("#"+rowid).toggle();
}
function searchTable(searchTerm){
loadingTimer();
$("#ajaxTable").load("table.php?search=" + encodeURI(searchTerm) + "&table=" + $('#active_table').val());
}
function gotoStart(start){
loadingTimer();
var search_text = '';
if($("#searchText").val()!='Search'){search_text=$("#searchText").val()}
$("#ajaxTable").load("table.php?start=" + start + "&search=" + search_text + "&table=" + $('#active_table').val());
}
function sortTable(order_by,direction){
loadingTimer();
var search_text = '';
if($("#searchText").val()!='Search'){search_text=$("#searchText").val()}
$("#ajaxTable").load("table.php?start=0&search=" + search_text + "&order_by=" + order_by + "&direction=" + direction + "&table=" + $('#active_table').val());
$("#" + order_by).attr("src","img' + '../img/ajax/arrow_down.gif");
}
this.label2value = function(){
var inactive = "inactive";
var active = "active";
var focused = "focused";
$("label").each(function(){
obj = document.getElementById($(this).attr("for"));
if($(this).attr("for")=="searchText"){
if(($(obj).attr("type") == "text") || (obj.tagName.toLowerCase() == "textarea")){
$(obj).addClass(inactive);
var text = $(this).text();
$(this).css("display","none");
$(obj).val(text);
$(obj).focus(function(){
$(this).addClass(focused);
$(this).removeClass(inactive);
$(this).removeClass(active);
if($(this).val() == text) $(this).val("");
});
$(obj).blur(function(){
$(this).removeClass(focused);
if($(this).val() == "") {
$(this).val(text);
$(this).addClass(inactive);
} else {
$(this).addClass(active);
};
});
};
};
});
};
var t = document.getElementsByTagName("tr");
for(var i=0;i<t.length;i++) {
var ocn = t[i].className;
t[i].onmouseover = function() { t[i].className = "hovered" };
t[i].onmouseout = function() { t[i].className = ocn };
}
function editCell(rowid,cellid,sqlrowid,key,id,type,fieldtype,updatestring) {
if(type=='cancel'){
$($('#ajaxtb')[0].rows[editTblRow].cells[editTblCell]).hide().html(cancelString).fadeIn('slow');
$($('#ajaxtb')[0].rows[editTblRow].cells[editTblCell]).removeClass('tdHover');
showDelete(editTblRow-1);
editCancel=true;
} else if(type=='save'){
$.ajax({
url: 'database.php',
type: 'GET',
data: "text=" + $('#edit_box').val() + "&sqlrowid=" + editSQLRowID + "&field=" + editKey + "&table=" + $('#active_table').val() + "&updatestring=" + escape(updatestring),
success: function(response){
if(updatestring){$("#ajaxTable").load("table.php");} else {
$($('#ajaxtb')[0].rows[editTblRow].cells[editTblCell]).html(response);
}
editTblRow = null;
editTblCell = null;
}
});
$($('#ajaxtb')[0].rows[editTblRow].cells[editTblCell]).removeClass('tdHover');
showDelete(editTblRow-1);
} else {
if(editTblRow==null && editTblCell==null){
if(fieldtype!='blob'){
$($('#ajaxtb')[0].rows[rowid].cells[cellid]).html('<input type="text" id="edit_box" class=\"edit_input\" value=\"' + id + '\" /> <div class=\"cell_opts\"><a href=\"javascript:return false;\" onclick=\"' + "editCell('','','','','','save','','" + updatestring + "');" + '\">Save</a> - <a href=\"javascript:return false;\" onclick=\"' + "editCell('','','','','','cancel');" + '\">Cancel</a></div>');
} else {
$($('#ajaxtb')[0].rows[rowid].cells[cellid]).html('<textarea id="edit_box" class=\"edit_input\" value=\"' + id + '\" >' + id + '</textarea> <div class=\"cell_opts\"><a href=\"javascript:return false;\" onclick=\"' + "editCell('','','','','','save','','" + updatestring + "');" + '\">Save</a> - <a href=\"javascript:return false;\" onclick=\"' + "editCell('','','','','','cancel');" + '\">Cancel</a></div>');
}
editTblRow = rowid;
editTblCell = cellid;
editSQLRowID = sqlrowid;
editKey = key;
cancelString = id;
} else {
if(editTblRow==rowid && editTblCell==cellid){
//We are currently editing this cell. Do nothing if clicked.
if(editCancel==true){
editTblRow = null;
editTblCell = null;
}
editCancel=false;
return false;
} else {
//Load a new edit box on a new cell
$($('#ajaxtb')[0].rows[editTblRow].cells[editTblCell]).hide().html(cancelString).fadeIn('slow');
if(fieldtype!='blob'){
$($('#ajaxtb')[0].rows[rowid].cells[cellid]).html('<input type="text" id="edit_box" class=\"edit_input\" value=\"' + id + '\" /> <div class=\"cell_opts\"><a href=\"javascript:return false;\" onclick=\"' + "editCell('','','','','','save','','" + updatestring + "');" + '\">Save</a> - <a href=\"javascript:return false;\" onclick=\"' + "editCell('','','','','','cancel');" + '\">Cancel</a></div>');
} else {
$($('#ajaxtb')[0].rows[rowid].cells[cellid]).html('<textarea id="edit_box" class=\"edit_input\" value=\"' + id + '\" >' + id + '</textarea> <div class=\"cell_opts\"><a href=\"javascript:return false;\" onclick=\"' + "editCell('','','','','','save','','" + updatestring + "');" + '\">Save</a> - <a href=\"javascript:return false;\" onclick=\"' + "editCell('','','','','','cancel');" + '\">Cancel</a></div>');
}
editTblRow = rowid;
editTblCell = cellid;
editSQLRowID = sqlrowid;
editKey = key;
cancelString = id;
}
}
}
}
DATABASE.PHP
<?php
/*Sevenscript <http://sevenscript.net> - Copyright (c) 2010 <
[email protected]>*/
require_once('config.php');
$update_text = $_GET['text'];
$sqlrowid = $_GET['sqlrowid'];
$field = $_GET['field'];
$updatestring = $_GET['updatestring'];
if($updatestring){
$result = @mysql_query("SHOW COLUMNS FROM $table");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$field_list .= $row['Field'] . ",";
}
$field_list = substr($field_list,0,-1);
}
$sql = "UPDATE $table SET $field= '$update_text' WHERE CONCAT_WS('',$field_list) = '$updatestring' LIMIT 1";
if(mysql_query ( $sql )){
echo $update_text;
}
}
$sql = "UPDATE $table SET $field= '$update_text' WHERE _rowid = '$sqlrowid'";
if(mysql_query ( $sql )){
echo $update_text;
}
?>
DBFUNC.PHP
<?php
Header('Cache-Control: no-cache'); //IE Fix
/*Sevenscript <http://sevenscript.net> - Copyright (c) 2010 <
[email protected]>*/
require_once('config.php');
$action = $_GET['action'];
$rowid = $_GET['rowid'];
if($action=="delete"){
$sql = "DELETE FROM $table WHERE _rowid='$rowid' LIMIT 1";
mysql_query ( $sql );
}
if($action=="new"){
//Insert a new row into the database
mysql_query("INSERT INTO $table VALUES()");
$new_row = mysql_insert_id();
if(!$fields){
$result = @mysql_query("SHOW COLUMNS FROM $table");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$fields[$row['Field']] = $row['Field'];
$field_list .= $row['Field'] . ",";
}
$field_list = substr($field_list,0,-1);
}
}
?>
<div class="tableBorder">
<table class="ajaxTable" cellpadding="0" cellspacing="0" border="0" id="ajaxtb">
<thead>
<tr>
<?php
foreach($fields as $key => $val){
if($key == $get_order_by){ $imgsrc = "img/ajax/$direction.gif"; $sort = $direction; } else { $imgsrc = "img/ajax/arrows_updown.gif"; $sort = "DESC"; }
echo "<th width=\"$width[$val]\">$val</th>";
}
?>
<th> </th></tr>
</thead>
<tbody>
<?php
$sql = "SELECT count(*) AS num FROM $table $search_sql $order_by LIMIT $pageLimit";
$result = mysql_query ( $sql );
$rowCount = mysql_result($result,0,"num");
foreach($fields as $key => $val){
$select_fields .= $key . ",";
}
$select_fields = substr($select_fields,0,-1);
$sql = "SELECT _rowid as rowid,$select_fields FROM $table $search_sql $order_by WHERE _rowid=$new_row LIMIT 1";
$row = mysql_query ( $sql );
while ( $result = mysql_fetch_array ( $row ) ) {
echo "<tr class=\"tdOdd\" onmouseout=\"showDelete('$x');\" onmouseover=\"showDelete('$x');\">";
$fieldval=1;
while($fieldval <= sizeof($fields)){
$fieldtype[$fieldval] = @mysql_field_type($row,$fieldval);
$fieldval++;
}
$fieldpos=1;
foreach($fields as $key => $val){
$filteredKey = htmlspecialchars(addslashes($result[$key]));
echo "<td class=\"edit_cell\" style=\"$style[$key]\" id=\"$result[$key]\" onmouseover=\"$(this).addClass('tdHover');\" onmouseout=\"$(this).removeClass('tdHover');\" onclick=\"editCell(this.parentNode.rowIndex,this.cellIndex,'$result[rowid]','$key',this.innerHTML,'','$fieldtype[$fieldpos]','$result[updatestring]');\">" . $result[$key] . "</td>";
$fieldpos++;
}
?>
<td style="width: 28px; height: 28px;" onclick="delRow(this,'<?php echo $result['rowid']; ?>');"><div id="del_<?php echo $x; ?>" style="display: none;"><img src="img/ajax/delete_icon.gif" /></div></td></tr>
<?php
$x++;
}
?>
</tbody>
<tfoot>
<tr><td colspan="30"><input type="button" value="Done" onclick="loadTable();" /><input type="button" value="Cancel" onclick="cancelInsert('<?php echo $new_row; ?>');" /></td></tr>
</tfoot>
</table>
<?php
}
?>
TABLE.PHP
<?php
Header('Cache-Control: no-cache'); //IE Fix
//Sevenscript <http://sevenscript.net> - Copyright (c) 2009 Stuart Rutter <
[email protected]>
require_once('config.php');
if(isset($_GET['table'])){
//$table = $_GET['table'];
}
if(!$fields){
$result = @mysql_query("SHOW COLUMNS FROM $table");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$fields[$row['Field']] = $row['Field'];
$field_list .= $row['Field'] . ",";
}
$field_list = substr($field_list,0,-1);
}
}
$get_search = mysql_real_escape_string($_GET['search']);
$get_start = mysql_real_escape_string($_GET['start']);
$get_direction = mysql_real_escape_string($_GET['direction']);
$get_order_by = mysql_real_escape_string($_GET['order_by']);
if($get_search){
if(!$searchField){
$search_sql = " WHERE ";
foreach($fields as $key=>$val){
$search_sql .= "$key LIKE '%$get_search%' OR ";
}
$search_sql = substr($search_sql,0,-4);
} else {
$search_sql = " WHERE $searchField LIKE '%$get_search%' ";
}
}
if($get_start){ $start = $get_start; } else { $start = "0"; }
if($get_direction){ $direction = $get_direction; }
if($direction=="ASC"){$direction="DESC";} else { $direction="ASC";}
if($get_order_by){ $order_by = " ORDER BY $get_order_by " . $direction; } else { $order_by = " "; }
if($sortField && !$get_order_by){
$get_order_by = $sortField;
$direction = $sortOrder;
$order_by = " ORDER BY $get_order_by " . $direction;
}
?>
<div class="tableBorder">
<table class="ajaxTable" cellpadding="0" cellspacing="0" border="0" id="ajaxtb">
<thead>
<tr>
<?php
foreach($fields as $key => $val){
if($key == $get_order_by){ $imgsrc = "img/ajax/$direction.gif"; $sort = $direction; } else { $imgsrc = "img/ajax/arrows_updown.gif"; $sort = "DESC"; }
echo "<th width=\"$width[$val]\"><a href=\"#\" onclick=\"javascript:sortTable('$key','$sort');\">$val<img id=\"$key\" src=\"$imgsrc\" /></a></th>";
}
?>
<th> </th></tr>
</thead>
<tbody>
<?php
$sql = "SELECT count(*) AS num FROM $table $search_sql $order_by LIMIT $pageLimit";
$result = mysql_query ( $sql );
$rowCount = mysql_result($result,0,"num");
foreach($fields as $key => $val){
$select_fields .= $key . ",";
}
$select_fields = substr($select_fields,0,-1);
//Check if there is a primary key on the table
$sql = "SELECT _rowid FROM $table";
$row= mysql_query ($sql);
if(!$row){
$sql = "SELECT CONCAT_WS('',$field_list) as updatestring,$select_fields FROM $table $search_sql $order_by LIMIT $start, $pageLimit";
} else {
$sql = "SELECT _rowid as rowid,$select_fields FROM $table $search_sql $order_by LIMIT $start, $pageLimit";
}
$row = mysql_query ( $sql );
while ( $result = mysql_fetch_array ( $row ) ) {
if ( $x&1 ){ echo "<tr class=\"tdOdd\" onmouseover=\"showDelete('$x');\" onmouseout=\"showDelete('$x');\">"; } else { echo "<tr class=\"tdEven\" onmouseout=\"showDelete('$x');\" onmouseover=\"showDelete('$x');\">"; }
$fieldval=1;
while($fieldval <= sizeof($fields)){
$fieldtype[$fieldval] = @mysql_field_type($row,$fieldval);
$fieldval++;
}
$fieldpos=1;
foreach($fields as $key => $val){
$filteredKey = htmlspecialchars(addslashes($result[$key]));
echo "<td class=\"edit_cell\" style=\"$style[$key]\" id=\"$result[$key]\" onmouseover=\"$(this).addClass('tdHover');\" onmouseout=\"$(this).removeClass('tdHover');\" onclick=\"editCell(this.parentNode.rowIndex,this.cellIndex,'$result[rowid]','$key',this.innerHTML,'','$fieldtype[$fieldpos]','$result[updatestring]');\">" . $result[$key] . "</td>";
$fieldpos++;
}
?>
<td style="width: 28px; height: 28px;" onclick="delRow(this,'<?php echo $result['rowid']; ?>');"><div id="del_<?php echo $x; ?>" style="display: none;"><img src="img/ajax/delete_icon.gif" /></div></td></tr>
<?php
$x++;
}
$numberOfPages = ceil($rowCount / $pageLimit);
?>
</tbody>
<tfoot>
<tr><td colspan="30"><h2><?php echo $rowCount . " Results - " . $numberOfPages . " Pages"; ?></h2></td></tr>
</tfoot>
</table>
</div>
<div class="pagination">
<?php
$current_page = ceil($start / $pageLimit) + 1;
$x=1;
$start=0;
if($current_page<=9){
while($x<=$numberOfPages){
if($x<=10){
if($current_page == $x){$class="paginationSelected";} else { $class="paginationNotSelected";}
echo "<a href=\"javascript: void(0)\" class=\"$class\" onclick=\"javascript:gotoStart($start);\">$x</a> ";
}
if($x>10 && $x == $numberOfPages){
echo " ... <a href=\"javascript: void(0)\" class=\"$class\" onclick=\"javascript:gotoStart($start);\">$x</a> ";
}
$start = $start + $pageLimit;
$x++;
}
}
$x=1;
if($current_page>=10){
$pageCounter = $current_page - 5;
while($x<=10){
$pageNumber = $pageCounter * $pageLimit - $pageLimit;
if($pageCounter<=$numberOfPages){
if($current_page == $pageCounter){$class="paginationSelected";} else { $class="paginationNotSelected";}
echo "<a href=\"javascript: void(0);\" class=\"$class\" onclick=\"javascript:gotoStart($pageNumber);\">$pageCounter</a> ";
}
$pageCounter++;
$x++;
}
}
?>
<div class="newRowBtn"><input type="image" onclick="insertRow();" src="img/ajax/new_row_btn.png" value="Insert New Row" /></div>
</div>
<!--
<div class="tableBorder">
<table class="ajaxTable" cellpadding="0" cellspacing="0" border="0" id="ajaxtb">
<thead>
<tr>
<th></th>
</tr>
</thead>
<tbody>
<tr><td class="tdOdd"><div class="table_msg">Not Connected. Please use the login form.</div></td></tr>
</tbody>
</table>
</div>
-->
And then my file
process.php
<?php
include('core/config.php');
include ('includes/header.php');
if(!isset($_SESSION['user'])) {
header('Location: ' . $url . '/login.php');
die();
}
<div id="container">
<?php
$con=mysqli_connect("localhost","user","pass!","db");
if(mysqli_errno($con))
{
echo "Can't Connect to mySQL:".mysqli_connect_error();
}
$fetch="SELECT ".$_SESSION['user']."_temp_table_1.UFP_CODE
FROM ".$_SESSION['user']."_temp_table_1
LEFT OUTER JOIN
InvManCen
ON
".$_SESSION['user']."_temp_table_1.UFP_CODE = InvManCen.UFPCODE
WHERE InvManCen.BLOCK = '2'";
$result = mysqli_query($con,$fetch);
if(!$result)
{
echo "Error:".(mysqli_error($con));
}
echo '<table class="center" align=center>'//.'<tr>'.'<td align="center">'. 'From Database'. '</td>'.'</tr>'
;
echo '<tr>'.'<td>'.'<table>'.'<tr>'.'<td align=center>'.'Important Information'.'</td>'.'</tr>';
while($data=mysqli_fetch_row($result))
{
echo ("<tr><td align=center><div class='alert-box notice'>Product with UFP Code $data[0] Is Blocked</div></td></tr>");
}
echo '</table>'.'</td>'.'</tr>'.'</table>';
//<td>$data[2]</td><td>$data[3]</td><td>$data[4]</td>
?>
<div class="ajaxTableHeader"></span>
<label for="searchText">Search</label>
<input type="text" id="searchText" class="tableSearch_input" name="searchText" value="" size="30" onkeyup="javascript:searchTable(this.value);" />
<div id="ajax_loading_div" style="display:none;"><img src="img/ajax/ajax-loader.gif" alt="Loading" /></div>
</div>
</br></br>
<div id="ajaxTable"></div>
</div>
<button onclick="window.location.href='process3.php?&start=0&s=&f=&sort=id&ad=a'">Generate Quote</button>
<button onclick="window.location.href='refresh.php'">Refresh</button>
<?php
include ('includes/footer.php');
?>
Is there any way we can make it so only one specific column is editable as at the moment all the values are editable..
when updating a column is there any way we can press enter rather than pressing the save button?
Is it possible to get data from two tables. Like using a LEFT OUTER JOIN in mysql.
Can someone help with this?
