Problem with sorting and pagination

[Fabrizio62]

Good day all,
If in a table i don't set pagination and sorting, all (select,update,create,delete) work good, if i set pagination and/or sorting, select works good, but update, create and delete return error after form save button clicked.
The query (php) works good, i can see the table updated from mysql client and if i reload the table the new query is showed.
Code:
jQuery:

$('#table-nomi-fondi').jtable({
		title: 'Nomi Fondi',
 		//paging: true, 
 		//pageSize: 10, 
	       //sorting: true,
 		//defaultSorting: 'nome ASC',
 		actions: {
 			listAction: '/php/ele-nomi-fondi.php?action=list',
 			createAction: '/php/ele-nomi-fondi.php?action=create',
 			updateAction: '/php/ele-nomi-fondi.php?action=update',
 			deleteAction: '/php/ele-nomi-fondi.php?action=delete'
 		},
 		fields: {
			id_fondo: {
 				key: true,
 				create: false,
 				edit: false,
 				list: false
 			},
 		
 			nome: {
			title: 'Nome',
 			},
 			quantita: {
 				title: 'quantita'
 			},
 			valore_carico: {
 				title: 'Valore carico'


 			},
		},
	});
	$('#table-nomi-fondi').jtable('load');

php:

 
 require 'db_connect.php'; // connect to db
 require 'funcs.php'; //function apri_file()
 
 try
 {
 	$con = apri_file();
 	if($_GET["action"] == "list") {
 		//$jtsorting = $_GET['jtSorting'];
 		$result = mysql_query(" SELECT * from nomi_fondi");
 				if (!$result) { error_log(date('Y/m/d H:i:s') . '  Query SELECT fallita = ' . mysql_error() . " on ele-nomi-fondi.php \n" , 3 , '../MYSQL-err.log'); }		
 
 		$rows = array();
		while($row = mysql_fetch_array($result))
 		{
 		    $rows[] = $row;
 		}
 		
		$jTableResult = array();
 		$jTableResult['Result'] = "OK";
 		$jTableResult['Records'] = $rows;
 		
 		$result = mysql_query("SELECT COUNT(*) FROM nomi_fondi ");
 							if (!$result) { error_log(date('Y/m/d H:i:s') . '  Query COUNT fallita = ' . mysql_error() . " on ele-nomi-fondi.php \n" , 3 , '../MYSQL-err.log'); }		
 
 		$row = mysql_fetch_array($result);
		$jTableResult['TotalRecordCount'] = $row[0];
 		print json_encode($jTableResult);
 		
 
 	}
	else if($_GET["action"] == "create")
 	{
		$nome = $_POST["nome"];
 		$quantita = $_POST["quantita"];				
 		$valore_carico = $_POST["valore_carico"];	
 		$result = mysql_query("INSERT INTO nomi_fondi (nome,quantita,valore_carico) VALUES('$nome',$quantita,$valore_carico)");
				if (!$result) { error_log(date('Y/m/d H:i:s') . '  Query CREATE fallita = ' . mysql_error() . " on ele-nomi-fondi.php \n" , 3 , '../MYSQL-err.log'); }		

 		$result = mysql_query("SELECT * FROM nomi_fondi WHERE id_fondo = LAST_INSERT_ID();");
 		$row = mysql_fetch_array($result);

 		$jTableResult = array();
 		$jTableResult['Result'] = "OK";
 		$jTableResult['Record'] = $row;
 		print json_encode($jTableResult);
 	}
 	else if($_GET["action"] == "update")
 	{
 		$id_fondo = $_POST['id_fondo'];
		$nome = $_POST["nome"];		
 		$quantita = $_POST["quantita"];				
		$valore_carico = $_POST["valore_carico"];	
		$result = mysql_query("UPDATE nomi_fondi SET nome='$nome',quantita=$quantita,valore_carico=$valore_carico WHERE id_fondo='$id_fondo' ");
				if (!$result) { error_log(date('Y/m/d H:i:s') . '  Query UPDATE fallita = ' . mysql_error() . " on ele-nomi-fondi.php \n" , 3 , '../MYSQL-err.log'); }		
 
 		$jTableResult = array();
 		$jTableResult['Result'] = "OK";
 		print json_encode($jTableResult);
 	}
 	else if($_GET["action"] == "delete")
	{
		$id_fondo = $_POST['id_fondo'];
 
 		//Delete from database
 		$result = mysql_query("DELETE FROM nomi_fondi WHERE id_fondo=$id_fondo ");
		//Return result to jTable
 		$jTableResult = array();
 		$jTableResult['Result'] = "OK";
 		print json_encode($jTableResult);
 	}
 
 	mysql_close($con);

 }
 
 catch(Exception $ex)
 {
 	$jTableResult = array();
 	$jTableResult['Result'] = "ERROR";
 	$jTableResult['Message'] = $ex->getMessage();
 	print json_encode($jTableResult);
 } 
 

MYSQL table structure (InnoDB UTF8)

+---------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------------------+------+-----+---------+----------------+
| id_fondo | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| nome | varchar(100) | YES | | NULL | |
| valore_carico | decimal(10,5) | YES | | NULL | |
| quantita | decimal(10,4) | YES | | NULL | |
+---------------+----------------------+------+-----+---------+----------------+
May be decimals number > 2 gets problems?
JTable 2.4.0 minified
Regards
Fabrizio

[misterparsons]

Hi,

All your problems are in your php list action processing.

Because when you are paging and/or sorting an edit, or create may or may not produce a record for your current page. The jTable processing for create and delete would break the number of rows per page. So after a good return, jtable performs an internal reload, which issues a new list request, to get a revised page worth of data.

Without wishing to give a lesson on php mysql prepared statements, here is a get function that I used to use. It is NOT suitable for a production application. It does use the php mysqli interface, but that would be a good thing for you to learn yourself.

function getForJTable ($sql) {
// Gets data and builds a response in standard jTable structure
//
	$mysqli = dbConnect();

	$sorting = isset($_REQUEST['jtSorting']);
	if ($sorting) {
		$sql .= " ORDER BY " . $_REQUEST['jtSorting'];
	}

	$paging = isset($_REQUEST['jtStartIndex']) AND isset($_REQUEST['jtPageSize']) ;
	if ($paging) {
		// very complicated writing a bulletproof editor, assume get queries begin with SELECT
		$i = stristr($sql,"SELECT");
		$sql = "SELECT SQL_CALC_FOUND_ROWS " . substr($sql,$i+6) . 
                           " LIMIT " . $_REQUEST['jtStartIndex']. ", ".$_REQUEST['jtPageSize'] .
                           "; SELECT FOUND_ROWS()";
	}
	$buffer = array();
	/* execute multi query */
	if ($mysqli->multi_query($sql)) {
		/*  first result set  has return data*/
		if ($result = $mysqli->store_result()) {
			$buffer['Result'] = "OK";
			$buffer['Records'] = array(); 
			while ($row = $result->fetch_assoc()) {
				$buffer['Records'][] = $row;
			}
			$result->free();
		}
		/* second result has total num of matched rows */
		if ($mysqli->next_result()) {
			$result = $mysqli->store_result();
			$row = $result->fetch_row();
			$buffer['TotalRecordCount'] = $row[0];
		}


	/* close connection */
	$mysqli->close();
	}
	else {
		throw new Exception ( "MySQL query error: " . $mysqli->error . $sql, $mysqli->errno); 
	}
	$buffer['sqlGet'] = $sql;
	return $buffer;
}
```