jQuery Grid Plugin - jqGrid - Topic: SQL Command

aznan on SQL Command

Thu, 06 May 2010 11:35:38 +0300

problem solve… my fault… everything is a.o.k.. I just rewrite or copy .. and voila it works GREAT!!! cant really explain .. Jqgrid ROCK!

hv I one last problem though, my search didnt work…. any idea where i should put the ".$wh." in my code???

$SQL = "SELECT tbl_po.int_poid, tbl_supplier.txt_supplier, CONCAT(tbl_item.txt_itemcode,':',tbl_item.txt_itemdescription) AS item, SUM(tbl_podetail.int_qty*tbl_podetail.fl_unitprice) AS amount, tbl_po.dt_appdate FROM tbl_podetail INNER JOIN tbl_po ON tbl_podetail.int_poid = tbl_po.int_poid INNER JOIN tbl_supplier ON tbl_supplier.int_supplierid = tbl_po.int_supplierid INNER JOIN tbl_item ON tbl_item.int_itemid = tbl_podetail.int_itemid INNER JOIN tbl_metric ON tbl_metric.int_metricid = tbl_item.int_metricid GROUP BY tbl_po.int_poid, tbl_supplier.txt_supplier, tbl_podetail.fl_unitprice, tbl_metric.txt_metric, tbl_po.dt_appdate ORDER BY ".$sidx." ". $sord." LIMIT ".$start." , ".$limit;

or do i need to do something else…

aznan on SQL Command

Thu, 06 May 2010 11:20:03 +0300

i manage to use SUM.. but still can't understand why INNER JOIN wont work... but my major problem is still givin me headache..!!!

HELLPPP!!

aznan on SQL Command

Wed, 05 May 2010 11:19:59 +0300

I hv 2 tables on purchase order. Main table: tbl_po which contain [int_poid, tbl_po.dt_appdate etc] and detail table: tbl_podetail which contain [int_podetail, int_po, int_qty, fl_unitpric] .

I try to put this inside my data url for my grid.. select option

SELECT tbl_po.int_poid, tbl_supplier.txt_supplier, SUM(tbl_podetail.int_qty*tbl_podetail.fl_unitprice) AS amount, tbl_po.dt_appdate FROM tbl_podetail INNER JOIN tbl_po ON tbl_podetail.int_poid = tbl_po.int_poid INNER JOIN tbl_supplier ON tbl_supplier.int_supplierid = tbl_po.int_supplierid INNER JOIN tbl_item ON tbl_item.int_itemid = tbl_podetail.int_itemid INNER JOIN tbl_metric ON tbl_metric.int_metricid = tbl_item.int_metricid GROUP BY tbl_po.int_poid, tbl_supplier.txt_supplier, tbl_podetail.fl_unitprice, tbl_metric.txt_metric, tbl_po.dt_appdate ORDER BY tbl_po.int_poid ASC

it runs OK if i omit some code and become like this

"SELECT tbl_po.int_poid, tbl_po.dt_appdate, tbl_supplier.txt_supplier FROM tbl_po, tbl_supplier WHERE tbl_po.int_supplierid = tbl_supplier.int_supplierid AND int_poid".$wh." ORDER BY ".$sidx." ". $sord." LIMIT ".$start." , ".$limit;

or is it not possible to run SQL command in jqgrid like SUM, MIN, AVG, INNER JOIN etc.. I run the same query inside phpmyadmin and it return some output. I look through some of the sample in demo, master detail, subgrid.. but I cant find the solution.. coz in the demo there is a field for amount in the master table.. in my case the amount is not exist in my tbl_po. I can create the (tbl_podetail.int_qty*tbl_podetail.fl_unitprice) as amount only on the "tbl_podetail" jqgrid like this

SELECT tbl_podetail.int_podetailid, tbl_podetail.int_poid, (tbl_podetail.fl_unitprice*tbl_podetail.int_qty) AS amount, tbl_podetail.dt_require FROM tbl_podetail WHERE int_poid=".$apid." ".$wh." ORDER BY ".$sidx." ". $sord." LIMIT ".$start." , ".$limit;

but this does not settle my problems.. i wish to create a grnd total to be passed to the amount.. just like the masted detail example…. coz i want to create some sort of summary..

hellpp pleaseee…