$g select_command MYSQL

Questions$g select_command MYSQL
joe asked 7 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 7 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 7 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 7 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 7 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 7 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 7 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

9 + 6 =

Login with your Social Id:

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 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

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

Let us improve this post!

Tell us how we can improve this post?