[SOLVED] Trying to IN but the damn wrapper wont let me

[emehrkay]

it is adding single quotes around the vars that i pass into it

 

we have this wrapper that replaces the ? in a query. we keep all of the queries in an xml file and the vars that you pass into it are defined as a question mark (i dont know if this is something they made up here or not) - that works fine, except for when i am trying to use IN and its pissing me off

 

is there anything that i could do within the query to get rid of the quotes?

 

here is what the wrapper is doing to my IN clause

 

pr.role_id IN ('2,4,12,14')

 

now if a quote were around each number, it would work fine

 

i get this error back

 

Truncated incorrect DOUBLE value: '2,4,12,13'

 

is there anything that i can do or do i need to modify my query to get rid of the IN and run it N times?

 

Thanks

 

 

[emehrkay]

this is kicking my ass

 

im pulling strings here, i just tried this

 

pr.role_id IN (TRIM(BOTH "'" FROM "'2,4,12,13'"))

[Wildbug]

Will this work?

 

... WHERE FIND_IN_SET(pr.role_id,'2,4,12,13')

 

FIND_IN_SET()

[fenway]

You mean pr.role_id IN ('2','4','12','14')

[Wildbug]

It sounds like he/his company is using prepared queries in some form or another where placeholders are represented as question marks, the query is "prepared," and then values are inserted on the fly, i.e., the query has limited flexibility, and the number of placeholders for a given query is static.  So when he's trying to substitute a list of values for a single question mark, it is being inserted as a single comma-delimited string.

 

For example,

SELECT * FROM pr WHERE role_id IN (?);
// becomes:
SELECT * FROM pr WHERE role_id IN ('1,2,3,4');
// instead of:
SELECT * FROM pr WHERE role_id IN ('1','2','3','4');
// or:
SELECT * FROM pr WHERE role_id IN (1,2,3,4);

[fenway]

Fine... but what is making that substitution? Something must know it's getting multiple values, otherwise, where did the commas come from?

[emehrkay]

Will this work?

 

... WHERE FIND_IN_SET(pr.role_id,'2,4,12,13')

 

FIND_IN_SET()

 

Ahhh thanks, I tried this but was using it wrong.

 

I am not too sure how the whole process works with replacing the question marks with the variables. I did some digging around and was able to echo out the last run query by the db, that is how i was able to see that it was putting quotes around every variable thta was replaced.

 

We're using Pear, I looked around and I wasnt able to find any info on the specific package, but FIND_IN_SET worked great.

 

Thanks guys, you saved me (again) from going MAD!!!

[fenway]

Has to do with prepared statements... but I guess that's a valid workaround, at least for numbers.