Jump to content

ajax powered mysql table editor

lhemingway

By lhemingway
in Third Party Scripts

Recommended Posts

lhemingway Newbie

lhemingway

Posted
Posted

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?

Link to comment
https://forums.phpfreaks.com/topic/285737-ajax-powered-mysql-table-editor/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.