ajax powered mysql table editor

lhemingway · January 28, 2014

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 <info@sevenscript.net>*/

//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 <info@sevenscript.net>*/
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 <info@sevenscript.net>*/
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 <contact@sevenscript.net>
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?

Edited January 28, 2014 by lhemingway

Sign In

ajax powered mysql table editor

Recommended Posts

lhemingway

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information