$g select_command MYSQL

Questions$g select_command MYSQL
joe asked 6 years ago

Not sure about this but if I get a variable called $category from _post and use it in the $g select_command the sql produced looks correct and works ok in a MYSQL window but does not produce any values when i use it in a select_command query on my grid.

The $category is a varchar variable.
if (!empty($_POST["Category"]))
$_SESSION["Category"] = $_POST['Category'];
$category = $_SESSION['Category'];
$no = $_REQUEST["rowid"] ;
$g select_command = "SELECT l.`No`,l.`Category`, l.`lens` FROM `Lens` i INNER JOIN `lines` l ON i.No = l.`No` WHERE i.`No` = " .$no . " and i.`Category` = '" . $category . "'";

6 Answers
Abu Ghufran answered 6 years ago

Hello,

Please send me the code for review (both main-sub grid).
Difficult to say by just this input as this looks fine.

joe answered 6 years ago

subgrid code is
<?php
error_reporting(E_ALL & ~E_NOTICE);

$conn = mysql_connect("localhost", "root", "root");
mysql_select_db("discount");

if (!isset($_POST['No'])) {
$_SESSION['No'] =($_POST['No']);
$l_No = $_SESSION['No'];;
}

if (!empty($_POST['Category']))
{
$_SESSION['Category'] = $_POST['Category'];
}
$Category = $_SESSION['Category'];

$col = array();
$col["title"] = "No"; // caption of column
$col["name"] = "No"; // field name, must be exactly same as with SQL prefix or db field
$col["width"] = "10";
$cols[] = $col;

$col = array();
$col["title"] = "lens";
$col["name"] = "lens";
$col["width"] = "10";
$cols[] = $col;

$col = array();
$col["title"] = "Category";
$col["name"] = "Category";
$col["width"] = "10";
$cols[] = $col;

$g = new jqgrid();

$grid["sortorder"] = "desc"; // ASC or DESC
$grid["height"] = ""; // autofit height of subgrid
$grid["caption"] = "Invoice Data"; // caption of grid
$grid["autowidth"] = true; // expand grid to screen width
$grid["multiselect"] = true; // allow you to multi-select through checkboxes
$grid["export"] = array("filename"=>"my-file", "sheetname"=>"test"); // export to excel parameters
$grid["subGrid"] = true;
$grid["subgridurl"] = "subgrid_sub_detail.php";
$g->set_options($grid);
$g->set_actions(array(
"add"=>true, // allow/disallow add
"edit"=>true, // allow/disallow edit
"delete"=>true, // allow/disallow delete
"rowactions"=>true, // show/hide row wise edit/del/save option
"export"=>true, // show/hide export to excel option
"autofilter" => true, // show/hide autofilter for search
"search" => "advance" // show single/multi field search condition (e.g. simple or advance)
)
);

if (!empty($_POST["Category"]))
$_SESSION["Category"] = $_POST['Category'];
$category = $_SESSION['Category'];

$no = $_REQUEST["rowid"] ;

$g->select_command = "SELECT l.`No`,l.`Category`, l.`lens` FROM `Lens` i INNER JOIN `lines` l ON i.No = l.`No` WHERE i.`No` = " .$no . " and i.`Category` = '" . $category . "'";

$sql = "SELECT l.`No`,l.`Category`, l.`lens` FROM `Lens` i INNER JOIN `lines` l ON i.No = l.`No` WHERE i.`No` = " .$no . " and i.`Category` = '" . $category . "'";

$g->table = "lines";

$g->set_columns($cols);

$out = $g->render("sub1");
echo $out;
echo "No is: " . $no . " Number is: " . $l_no . " Category is : " . $category . " " . $sql;
?>

subgrid detail is
<?php
error_reporting(E_ALL & ~E_NOTICE);

$conn = mysql_connect("localhost", "root", "root");
mysql_select_db("discount");

include("lib/inc/jqgrid_dist.php");

$grid = new jqgrid();

$opt["caption"] = "Stock";

$opt["subGrid"] = true;
$opt["subgridurl"] = "subgrid_detail.php";

$opt["subgridparams"] = "No,Category,Lens";
$grid->set_options($opt);

$grid->table = "Lens";
$grid->select_command = "select No, Category , Lens, Old, New from Lens where Category = 'st'";
$outst = $grid->render("list1");

$grid = new jqgrid();

$opt["caption"] = "RX";

$opt["subGrid"] = true;
$opt["subgridurl"] = "subgrid_detail.php";

$grid->set_options($opt);

$grid->table = "Lens";
$grid->select_command = "select No, Category , Lens, Old, New from Lens where Category = 'rx'";
$outrx = $grid->render("list2");

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"&gt;
<html>
<head>
<link rel="stylesheet" type="text/css" media="screen" href="lib/js/themes/start/jquery-ui.custom.css"></link>
<link rel="stylesheet" type="text/css" media="screen" href="lib/js/jqgrid/css/ui.jqgrid.css"></link>

<script src="lib/js/jquery.min.js" type="text/javascript"></script>
<script src="lib/js/jqgrid/js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="lib/js/jqgrid/js/jquery.jqGrid.min.js" type="text/javascript"></script>
<script src="lib/js/themes/jquery-ui.custom.min.js" type="text/javascript"></script>
</head>
<body>
<div style="margin:10px">
Subgrid example … this file will load subgrid defined in 'subgrid_detail.php'
<br>
<br>
<?php echo $outst?>
<br>
<br>
<?php echo $outrx?>
</div>
</body>
</html>

joe answered 6 years ago

it is now wierder.

I know the SQL code is ok as if i hard code the values in the select_command it works ok.
$g->select_command = "SELECT l.`No`,l.`Category`, l.`lens` FROM `Lens` i INNER JOIN `lines` l ON i.No = l.`No` WHERE i.`No` = '1' and i.`Category` = '" . $category . 'st'";

If i try to get the values as below it does not work.
if (!empty($_POST['Category']))
{
$_SESSION['Category'] = $_POST['Category'];
$category = $_SESSION['Category'];
}
$no = $_REQUEST["rowid"] ;

$g->select_command = "SELECT l.`No`,l.`Category`, l.`lens` FROM `Lens` i INNER JOIN `lines` l ON i.No = l.`No` WHERE i.`No` = '1' and i.`Category` = '" . $category . "'";

I am not sure and am slowly going mad.

Abu Ghufran answered 6 years ago

Hello Joe,

I'm sorry to hear that it's not resolved yet.
Can you email me code files along with sample db dump, so that i can generate the case.

You can email me at [email protected].

Darcy answered 6 years ago

There is some kind of bug when using POST variables within a query string. I'm not sure how to get around it quite yet. I've come across this a few times.

Abu Ghufran answered 6 years ago

Hello Darcy,

Please checkout the faq item.

##### Q) How to load grid based on $_POST data from other page?

How-Tos & FAQs

Your Answer

12 + 2 =

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating / 5. Vote count:

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!