Welcome Guest, Not a member yet? Register   Sign In
query with "IN" parameter
#1

Hi all,

I have a query like this:

Code:
SELECT x,y,x from TABLE WHERE x IN (?) and y=?

My first approach was just preparing the values of x as a string, considering the values are also strings, so I got something like:

Code:
$x = "'a','b','c'";
$y =3;
$sql =  'SELECT x,y,x from TABLE WHERE x IN (?) and y=?';
$result = $this->db->query($sql, array($x, $y));

That didn't work as codeigniter escapes the in string.

Then I read the manual and found In parameters should be passed as array, ok, new try:

Code:
$x =array('a','b','c');
$y =3;
$sql =  'SELECT x,y,x from TABLE WHERE x IN (?) and y=?';
$result = $this->db->query($sql, array($x, $y));

Then i got a mesage saying this query is invalid:


Code:
SELECT x,y,x from TABLE WHERE x IN (Array) and y=3

I'm user Code igniter 2.

Note: due to some restrictions in what I'm using this for, I cannot use other database features like using db->where_in() or similar, just db->query()

Thanks!
Am I doing anything wrong?
Reply
#2

@utodev,

Any reason why you don't upgrade to CI 3.1.x?
Anyway, my suggestion is to not use the ? but use a variable (of course not forgetting to vet the data prior to putting it in the query).

$x = "'a','b','c'";
$y =3;
$sql = 'SELECT x,y,x from TABLE WHERE x IN ('.$x.') and y=?';
$result = $this->db->query($sql, array($y));
Reply
#3

(05-23-2019, 06:55 AM)php_rocs Wrote: @utodev,

Any reason why you don't upgrade to CI 3.1.x?
Anyway, my suggestion is to not use the ? but use a variable (of course not forgetting to vet the data prior to putting it in the query).

$x = "'a','b','c'";
$y =3;
$sql =  'SELECT x,y,x from TABLE WHERE x IN ('.$x.') and y=?';
$result = $this->db->query($sql, array($y));

Thank you for your response.

I'm not upgrading cause it is not my server and not my decision to take. Regarding the variable, it isn't an option either. The solution reads this query and several others from an XML which includes also the parameters, it is generated by another application out of my control, and generates a report with the queries results.

Maybe I can use eval to do it with a variable but it would be changing the code too much. The thing is according documentation my second solution should be working, but it doesn't. Maybe that option to include an array as parameter is from CI 3.1 though.

Well, thanks anyway :-)
Reply
#4

(This post was last modified: 05-25-2019, 08:57 AM by dave friend.)

Don't put the first placeholder in parentheses.

What I mean is, instead of

PHP Code:
$sql  'SELECT x,y,z from TABLE WHERE x IN (?) and y=?'

try this

PHP Code:
$x =array('a','b','c');
$y =3;
$sql  'SELECT x,y,z from TABLE WHERE x IN ? and y=?';
$result $this->db->query($sql, array($x$y)); 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB