.I would like to dynamically query data from a table and display it based on user logged in. I have 2 files : one is the index.php that catches the user’s admin products like :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
$currentUser = CurrentUser::getInstance(); $UserID = $currentUser->getId(); //Get admin products $que1 = mysql_query("SELECT productid from admin_levels WHERE level>1 AND userid=$UserID ORDER BY productid"); $op1=""; while($row1 = mysql_fetch_row($que1)){ foreach($row1 as $cell1){ $op1.=$cell1.","; } } $temp1=strlen($op1)-1; $op1=substr($op1,0,$temp1); //op1 contains admin products for sql command $_SESSION['w1']=$op1; The op1 string contains something similar to this: 16,17,20,114,125 Now i want to add it in my jqgrid query: //Get admin products $y1=$_SESSION['w1']; $opt1="WHERE issues.productid in (".$y1.")"; //Create query $grid->SelectCommand = 'SELECT CONCAT(issues.type,"0",issues.kbid) as KBID,issues.issuesummary as Summary,products.productname as Product,issue_priorities.description as Priority,date_format(issues.createddate, "%d/%m/%Y") as Reported,date_format(issues.lastupdated, "%d/%m/%Y") as Updated,issue_status.statusdescription as Status,issue_resolutions.resdescription as Resolution,users.logon as Assigned FROM issues INNER JOIN issue_priorities ON issue_priorities.VALUE = issues.PRIORITY - 1 INNER JOIN issue_resolutions ON issue_resolutions.RESID = issues.RESOLUTION INNER JOIN users ON users.ID = issues.ASSIGNEDUSERID INNER JOIN products ON products.PRODUCTID = issues.PRODUCTID INNER JOIN issue_status ON issue_status.STATUSID = issues.STATUS' .$opt1; |
On echoing the sql command i get this:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘issues.productid in (16,17,20,114,125) LIMIT 0, 0’ at line 6
How to solve this issue?
I tried something like this:
$opt1=” WHERE issues.productid in (’16’,’114′)”;
and it worked correctly with the following dump :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
Executed 2 query(s) - 2015-03-25 11:15:44 Array ( [0] => Array ( [time] => 2015-03-25 11:15:44 [query] => SELECT COUNT(*) AS COUNTR FROM issues INNER JOIN issue_priorities ON issue_priorities.VALUE = issues.PRIORITY - 1 INNER JOIN issue_resolutions ON issue_resolutions.RESID = issues.RESOLUTION INNER JOIN users ON users.ID = issues.ASSIGNEDUSERID INNER JOIN products ON products.PRODUCTID = issues.PRODUCTID INNER JOIN issue_status ON issue_status.STATUSID = issues.STATUS WHERE issues.productid in ('16','114') [data] => [types] => [fields] => [primary] => [input] => ) [1] => Array ( [time] => 2015-03-25 11:15:44 [query] => SELECT CONCAT(issues.type,'0',issues.kbid) as KBID,issues.issuesummary as Summary,products.productname as Product,issue_priorities.description as Priority,date_format(issues.createddate, '%d/%m/%Y') as Reported,date_format(issues.lastupdated, '%d/%m/%Y') as Updated,issue_status.statusdescription as Status,issue_resolutions.resdescription as Resolution,users.logon as Assigned FROM issues INNER JOIN issue_priorities ON issue_priorities.VALUE = issues.PRIORITY - 1 INNER JOIN issue_resolutions ON issue_resolutions.RESID = issues.RESOLUTION INNER JOIN users ON users.ID = issues.ASSIGNEDUSERID INNER JOIN products ON products.PRODUCTID = issues.PRODUCTID INNER JOIN issue_status ON issue_status.STATUSID = issues.STATUS WHERE issues.productid in ('16','114') ORDER BY KBID asc LIMIT 0, 20 [data] => [types] => [fields] => [primary] => [input] => ) ) |
But when i tried making it dynamic like :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
$opt1=" WHERE issues.productid in ".$op1;</blockquote> This is the dump i got: <blockquote>Executed 2 query(s) - 2015-03-25 11:21:11 Array ( [0] => Array ( [time] => 2015-03-25 11:21:11 [query] => SELECT COUNT(*) AS COUNTR FROM issues INNER JOIN issue_priorities ON issue_priorities.VALUE = issues.PRIORITY - 1 INNER JOIN issue_resolutions ON issue_resolutions.RESID = issues.RESOLUTION INNER JOIN users ON users.ID = issues.ASSIGNEDUSERID INNER JOIN products ON products.PRODUCTID = issues.PRODUCTID INNER JOIN issue_status ON issue_status.STATUSID = issues.STATUS WHERE issues.productid in [data] => [types] => [fields] => [primary] => [input] => ) [1] => Array ( [time] => 2015-03-25 11:21:11 [query] => SELECT CONCAT(issues.type,'0',issues.kbid) as KBID,issues.issuesummary as Summary,products.productname as Product,issue_priorities.description as Priority,date_format(issues.createddate, '%d/%m/%Y') as Reported,date_format(issues.lastupdated, '%d/%m/%Y') as Updated,issue_status.statusdescription as Status,issue_resolutions.resdescription as Resolution,users.logon as Assigned FROM issues INNER JOIN issue_priorities ON issue_priorities.VALUE = issues.PRIORITY - 1 INNER JOIN issue_resolutions ON issue_resolutions.RESID = issues.RESOLUTION INNER JOIN users ON users.ID = issues.ASSIGNEDUSERID INNER JOIN products ON products.PRODUCTID = issues.PRODUCTID INNER JOIN issue_status ON issue_status.STATUSID = issues.STATUS WHERE issues.productid in ORDER BY KBID asc LIMIT 0, 20 [data] => [types] => [fields] => [primary] => [input] => ) ) |
I echoed contents of $op1 this is what it gave:
(’16’,’114′)
I echoed $grid->SelectCommand, it gave:
|
1 2 |
SELECT CONCAT(issues.type,'0',issues.kbid) as KBID,issues.issuesummary as Summary,products.productname as Product,issue_priorities.description as Priority,date_format(issues.createddate, '%d/%m/%Y') as Reported,date_format(issues.lastupdated, '%d/%m/%Y') as Updated,issue_status.statusdescription as Status,issue_resolutions.resdescription as Resolution,users.logon as Assigned FROM issues INNER JOIN issue_priorities ON issue_priorities.VALUE = issues.PRIORITY - 1 INNER JOIN issue_resolutions ON issue_resolutions.RESID = issues.RESOLUTION INNER JOIN users ON users.ID = issues.ASSIGNEDUSERID INNER JOIN products ON products.PRODUCTID = issues.PRODUCTID INNER JOIN issue_status ON issue_status.STATUSID = issues.STATUS WHERE issues.productid in ('16','17','20','114','125') |
Can you please have a look into it?
I tried the link u mentioned and also used this http://www.trirand.net/forum/default.aspx?g=posts&m=5425 but i got a page with 0 rows. Though the error disappeared.
Here is my index.php file where sessioning is done :
Here is my full grid.php file :
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top
