Guriddo » Topic: Dynamically form SQL Query

Dynamically form SQL Query

Guriddo
jqgrid, php
Dynamically form SQL Query

Tagged: jqgrid, php

This topic has 10 replies, 2 voices, and was last updated 8 years, 6 months ago by tony.

Creator

Topic

March 24, 2015 at 3:08 pm #123612

helloman2504

Participant

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 :

$currentUser = CurrentUser::getInstance();
$UserID = $currentUser-&gt;getId();

//Get admin products
$que1 = mysql_query(&quot;SELECT productid from admin_levels WHERE level&gt;1 AND userid=$UserID ORDER BY productid&quot;);
$op1=&quot;&quot;;

while($row1 = mysql_fetch_row($que1)){
foreach($row1 as $cell1){
$op1.=$cell1.&quot;,&quot;;
}
}

$temp1=strlen($op1)-1;
$op1=substr($op1,0,$temp1);
//op1 contains admin products for sql command
$_SESSION[&#039;w1&#039;]=$op1;

&amp;nbsp;

The op1 string contains something similar to this:

16,17,20,114,125

&amp;nbsp;

Now i want to add it in my jqgrid query:

//Get admin products
$y1=$_SESSION[&#039;w1&#039;];
$opt1=&quot;WHERE issues.productid in (&quot;.$y1.&quot;)&quot;;

//Create query
$grid-&gt;SelectCommand = &#039;SELECT CONCAT(issues.type,&quot;0&quot;,issues.kbid) as KBID,issues.issuesummary as Summary,products.productname as Product,issue_priorities.description as Priority,date_format(issues.createddate, &quot;%d/%m/%Y&quot;) as Reported,date_format(issues.lastupdated, &quot;%d/%m/%Y&quot;) 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&#039;
.$opt1;

$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;

&nbsp;

The op1 string contains something similar to this:

16,17,20,114,125

&nbsp;

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?

This topic was modified 8 years, 6 months ago by helloman2504.
This topic was modified 8 years, 6 months ago by tony. Reason: Put code tag to be read better

Creator

Topic

Viewing 10 replies - 1 through 10 (of 10 total)

Author

Replies

March 24, 2015 at 6:51 pm #123618

tony

Keymaster

Hello,

Check your query again. Check if you have such name generated in the SQL.
Use
$grid->debug = true;

and see jqGrid.log file what query is builded.

Regards,

March 25, 2015 at 1:25 pm #123637

helloman2504

Participant

I tried something like this:

$opt1=” WHERE issues.productid in (’16’,’114′)”;

and it worked correctly with the following dump :

Executed 2 query(s) - 2015-03-25 11:15:44
Array
(
[0] =&gt; Array
(
[time] =&gt; 2015-03-25 11:15:44
[query] =&gt; 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 (&#039;16&#039;,&#039;114&#039;)
[data] =&gt;
[types] =&gt;
[fields] =&gt;
[primary] =&gt;
[input] =&gt;
)

[1] =&gt; Array
(
[time] =&gt; 2015-03-25 11:15:44
[query] =&gt; SELECT CONCAT(issues.type,&#039;0&#039;,issues.kbid) as KBID,issues.issuesummary as Summary,products.productname as Product,issue_priorities.description as Priority,date_format(issues.createddate, &#039;%d/%m/%Y&#039;) as Reported,date_format(issues.lastupdated, &#039;%d/%m/%Y&#039;) 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 (&#039;16&#039;,&#039;114&#039;) ORDER BY KBID asc LIMIT 0, 20
[data] =&gt;
[types] =&gt;
[fields] =&gt;
[primary] =&gt;
[input] =&gt;
)

)

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 :

$opt1=&quot; WHERE issues.productid in &quot;.$op1;&lt;/blockquote&gt;
This is the dump i got:
&lt;blockquote&gt;Executed 2 query(s) - 2015-03-25 11:21:11
Array
(
[0] =&gt; Array
(
[time] =&gt; 2015-03-25 11:21:11
[query] =&gt; 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] =&gt;
[types] =&gt;
[fields] =&gt;
[primary] =&gt;
[input] =&gt;
)

[1] =&gt; Array
(
[time] =&gt; 2015-03-25 11:21:11
[query] =&gt; SELECT CONCAT(issues.type,&#039;0&#039;,issues.kbid) as KBID,issues.issuesummary as Summary,products.productname as Product,issue_priorities.description as Priority,date_format(issues.createddate, &#039;%d/%m/%Y&#039;) as Reported,date_format(issues.lastupdated, &#039;%d/%m/%Y&#039;) 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] =&gt;
[types] =&gt;
[fields] =&gt;
[primary] =&gt;
[input] =&gt;
)

)

$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

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:


SELECT CONCAT(issues.type,&#039;0&#039;,issues.kbid) as KBID,issues.issuesummary as Summary,products.productname as Product,issue_priorities.description as Priority,date_format(issues.createddate, &#039;%d/%m/%Y&#039;) as Reported,date_format(issues.lastupdated, &#039;%d/%m/%Y&#039;) 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 (&#039;16&#039;,&#039;17&#039;,&#039;20&#039;,&#039;114&#039;,&#039;125&#039;)

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?

This reply was modified 8 years, 6 months ago by tony. Reason: add code tags

March 26, 2015 at 6:13 am #123648

helloman2504

Participant

Can you please help me out

March 26, 2015 at 8:38 am #123656

tony

Keymaster

Hello,

This is so named passing feature.

Look into the old forum on how to pass a parameters in the grid.
You can start from here

Not that the script is executed in two diffrent ways: first time the script creates the grid and get your initial variables passed to the script.
Second time grid made a ajax call to the same link to obtain a data, but this time your custom params are lost.

Look into the last link that I have post here. It will help you.

Regards,

March 26, 2015 at 4:01 pm #123662

helloman2504

Participant

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.

This reply was modified 8 years, 6 months ago by helloman2504.

March 26, 2015 at 5:12 pm #123664

helloman2504

Participant

I got it working now but still get this error message:

QLSTATE[HY093]: Invalid parameter number: no parameters were bound

March 27, 2015 at 3:48 am #123666

helloman2504

Participant

kindly help me remove the error sir.

March 27, 2015 at 8:54 am #123667

tony

Keymaster

Hello,

Can you please post your code?
Please in the future do this, since we can not determine where the problem is.
I hope you understand – this will save your and our time.

Kind Regards,

March 27, 2015 at 4:17 pm #123672

helloman2504

Participant

Here is my index.php file where sessioning is done :

http://pastebin.com/zLz4yqrn

Here is my full grid.php file :

http://pastebin.com/Rcx27VtG

This reply was modified 8 years, 6 months ago by helloman2504.

March 30, 2015 at 9:13 am #123681

tony

Keymaster

Hello,

The problem (error) you have is that you pass 3 parameter , but you set two in the Sql command
Here is what you do:

$_SESSION[&#039;krish&#039;]=&quot;114,16,17&quot;;

session_start();
        if(isset($_SESSION[&#039;krish&#039;]))
        {
                $kk = $_SESSION[&#039;krish&#039;];
        }
$temp = explode(&quot;,&quot;,$kk);
for($i=0;$i&lt;2;$i++)
{
$params[$i]=$temp[$i];
}
...

$sql=&#039;SELECT CONCAT ...
...
WHERE issues.productid in ( ? , ? &#039; . &#039;)&#039;;

...
$grid-&gt;setColModel(null,$params);
...
$grid-&gt;renderGrid(&#039;#grid&#039;,&#039;#pager&#039;,true, null, $params, true,true);

$_SESSION['krish']="114,16,17";

session_start();

if(isset($_SESSION['krish']))

{

$kk = $_SESSION['krish'];

}

$temp = explode(",",$kk);

for($i=0;$i<2;$i++)

{

$params[$i]=$temp[$i];

}

...

$sql='SELECT CONCAT ...

...

WHERE issues.productid in ( ? , ? ' . ')';

...

$grid->setColModel(null,$params);

...

$grid->renderGrid('#grid','#pager',true, null, $params, true,true);

I think that you must look carfully how you pass parameters.
Actually we pass parameters in a way that PDO driver do.

I recommend you to search on how to pass a parameters in in IN WHERE clause.

Dynamically form SQL Query

Search Forum

Recent Replies

Forum Statistics

UserOnline