Jump to content

ajax powered mysql table editor

lhemingway

By lhemingway
in Third Party Scripts

 Share

Recommended Posts

lhemingway Newbie

lhemingway

Posted
Posted (edited)

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 by lhemingway
Link to comment
https://forums.phpfreaks.com/topic/285737-ajax-powered-mysql-table-editor/
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
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.