Json data for column totals

[nprioleau]

I am having some difficulties with the formatting of JSON. My code is below with the result:

Code:

	// total -------------
	$rs = $db->query(sprintf("SELECT COUNT(*) as trows from purchases")) or SQLError();
	while($rset = $rs->fetch_assoc()) {
		$r["total"] = $rset["trows"];
	}
	$result["total"] = $r["total"];
	// rows ---------------
	
	$getRows = $db->query(sprintf("SELECT *, c.cont_ID, c.cont_fullName, p.prod_productID, p.prod_productName FROM purchases inner join contacts c on pur_supplierID = c.cont_ID inner join products p on pur_productID = p.prod_productID GROUP BY pur_ID ORDER BY $sort $order")) or SQLError();
	while($row = $getRows->fetch_assoc()) {
		$result["rows"] = $row;
	}
	
	$getTotal = $db->query(sprintf("SELECT sum(pur_value) as Total from purchases")) or SQLError();
	while($row = $getTotal->fetch_assoc()) {
		$entry = array(	'name'	=> 'Total', 'Total Value'	=> $row['Total'], );
		$jsonData[] = $entry;
	}
	$result["footer"] = $jsonData;
	
	echo json_encode($result);

Code:

{"total":"3","rows":{"pur_ID":"7","pur_date":"2016-03-22","pur_dueDate":"2016-03-24","pur_productID":"6","pur_qty":"500","pur_supplierID":"45","pur_value":"5.00","pur_supplierInvoice":"444","pur_unitPrice":"10.00","pur_arrivalDate":null,"pur_ref":"4","pur_received":null,"cont_ID":"45","cont_contactGroup":"2","cont_fullName":"Corromaster","cont_countryID":"6","cont_comments":"","cont_deliveryAddressID":"0","cont_createBy":"Nic","cont_createTime":"2015-10-19 09:15:25","cont_updateTime":"2015-10-19 09:15:25","cont_updateBy":null,"prod_productID":"6","prod_productName":"Triacetin","prod_grossWeight":"240","prod_nettWeight":"0.00","prod_productType":"3","prod_productCost":"0.00","prod_uomID":"2","prod_canSell":"","prod_minLevel":"480.00"},"footer":[{"name":"Total","Total Value":"14.00"}]}

Really appreciate some help.

[jarry]

The 'rows' should be an array.

Code:

$rows = [];
$getRows = $db->query(sprintf("SELECT *, c.cont_ID, c.cont_fullName, p.prod_productID, p.prod_productName FROM purchases inner join contacts c on pur_supplierID = c.cont_ID inner join products p on pur_productID = p.prod_productID GROUP BY pur_ID ORDER BY $sort $order")) or SQLError();
while($row = $getRows->fetch_assoc()) {
	$rows[] = $row;
}
$result["rows"] = $rows;

[nprioleau]

Thank you. my Json Array now looks like this:

Code:

{"total":[{"total":"6"}],"rows":[{"total":"6"},{"pur_ID":"6","pur_date":"2016-03-22","pur_dueDate":"2016-03-24","pur_productID":"6","pur_qty":"500","pur_supplierID":"42","pur_value":"8.00","pur_supplierInvoice":"12","pur_unitPrice":"17.96","pur_arrivalDate":null,"pur_ref":"12","pur_received":"Received","cont_ID":"42","cont_contactGroup":"2","cont_fullName":"Henkel","cont_countryID":"6","cont_comments":null,"cont_deliveryAddressID":"0","cont_createBy":"Nic","cont_createTime":"2015-10-14 14:54:25","cont_updateTime":"2015-10-14 14:54:25","cont_updateBy":null,"prod_productID":"6","prod_productName":"Triacetin","prod_grossWeight":"240","prod_nettWeight":"0.00","prod_productType":"3","prod_productCost":"0.00","prod_uomID":"2","prod_canSell":"","prod_minLevel":"480.00"},{"pur_ID":"8","pur_date":"2016-03-09","pur_dueDate":"2016-03-22","pur_productID":"7","pur_qty":"500","pur_supplierID":"42","pur_value":"1.00","pur_supplierInvoice":"333","pur_unitPrice":"2000.00","pur_arrivalDate":null,"pur_ref":"33","pur_received":"Pending","cont_ID":"42","cont_contactGroup":"2","cont_fullName":"Henkel","cont_countryID":"6","cont_comments":null,"cont_deliveryAddressID":"0","cont_createBy":"Nic","cont_createTime":"2015-10-14 14:54:25","cont_updateTime":"2015-10-14 14:54:25","cont_updateBy":null,"prod_productID":"7","prod_productName":"Plug Wrap","prod_grossWeight":"0.00","prod_nettWeight":"0.00","prod_productType":"3","prod_productCost":"0.00","prod_uomID":"3","prod_canSell":"","prod_minLevel":"1200.00"},{"pur_ID":"7","pur_date":"2016-03-22","pur_dueDate":"2016-03-24","pur_productID":"6","pur_qty":"500","pur_supplierID":"45","pur_value":"5.00","pur_supplierInvoice":"444","pur_unitPrice":"10.00","pur_arrivalDate":null,"pur_ref":"4","pur_received":"Part Received","cont_ID":"45","cont_contactGroup":"2","cont_fullName":"Corromaster","cont_countryID":"6","cont_comments":"","cont_deliveryAddressID":"0","cont_createBy":"Nic","cont_createTime":"2015-10-19 09:15:25","cont_updateTime":"2015-10-19 09:15:25","cont_updateBy":null,"prod_productID":"6","prod_productName":"Triacetin","prod_grossWeight":"240","prod_nettWeight":"0.00","prod_productType":"3","prod_productCost":"0.00","prod_uomID":"2","prod_canSell":"","prod_minLevel":"480.00"}],"footer":[{"total_value":"14.00"}]}

With php as:

Code:

$rowz = [];
	$total = [];
	$footer = [];
	
	//Get the total 
	$getRows = $db->query(sprintf("SELECT COUNT(*) as total from purchases")) or SQLError();
	while($row = $getRows->fetch_assoc()) {
		$result[] = $row;	
	}
	$rowz["total"] = $result;
	
	// GET THE ROWS
	$getRows = $db->query(sprintf("SELECT *, c.cont_ID, c.cont_fullName, p.prod_productID, p.prod_productName FROM purchases inner join contacts c on pur_supplierID = c.cont_ID inner join products p on pur_productID = p.prod_productID ORDER BY $sort $order")) or SQLError();
	
	while($row = $getRows->fetch_assoc()) {
		$result[] = $row;
	}
	$rowz["rows"] = $result;
	
	// GET THE FOOTER
	$getRows = $db->query(sprintf("SELECT SUM(pur_value) as total_value from purchases")) or SQLError();
	while($row = $getRows->fetch_assoc()) {
		$total[] = $row;	
	}
	$rowz["footer"] = $total;
	$smarty->assign('rowz', $rowz);	
	echo json_encode($rowz);

I am getting some VERY strange results in the grid 

[jarry]

Your json data is wrong. It looks should be:

Code:

{"total":"3","rows":[{...},{...}],"footer":[{"total_value":"14.00"}]}

[nprioleau]

Thank you. I have a result now.