SQL SELECT issue

ConsultClifford

hi

> using php & mysql
> ive got an array of numbers (from multiple select) passed through from a form
> i want to check if any of those numbers are present in a db field
> the numbers in that db field are comma seperated.

What im doing is im extracting the numbers from the array and adding a where clause for each


foreach ($_REQUEST as $value){
if($i<count($_REQUEST)){
$jobs.= $value.',';
$wheres3.= "($value IN a.job) AND ";
}else{
$jobs.= $value;
$wheres3.= "($value IN a.job) ";
}
$i++;
}


if i select the second and third options in my multiple select
the i get
WHERE ((1 IN a.job) AND (2 IN a.job) )
in my query


However this is not returning anything ... (wheres it should do)
Is the issue that the values in the db are comma seperated??


Tnx
Sean

jaggiebunnet

should the AND not be an OR ?

cgarvey

If $_REQUEST is indeed a comma-seperated list, then you can make it alot easier on yourself...

// add check that $_REQUEST['job'] contains something
$sql = "SELECT a.id FROM a WHERE a.job IN( $_REQUEST['job'] )";

.. and assuming that $_REQUEST contains "1,2,3", then your query will be
SELECT a.id FROM a WHERE a.job IN( 1,2,3 ) .. which should do the trick. You could also SELECT COUNT(*) instead of a.id, etc.

.cg

ConsultClifford

cgarvey wrote:

If $_REQUEST is indeed a comma-seperated list, then you can make it alot easier on yourself...

// add check that $_REQUEST['job'] contains something
$sql = "SELECT a.id FROM a WHERE a.job IN( $_REQUEST['job'] )";

.. and assuming that $_REQUEST contains "1,2,3", then your query will be
SELECT a.id FROM a WHERE a.job IN( 1,2,3 ) .. which should do the trick. You could also SELECT COUNT(*) instead of a.id, etc.

.cg

superb!!!
was wrecking my head for ages
Tnx

id buy u a pint if i was anywhere near by!!

rsynnott

This is horribly insecure code. It allows arbitrary SQL statements to be run by the user. Stop it and read about SQL injection immediately.

cgarvey

yes rsynott, it is insecure, but then if we were all to post totally "secure" and assume the poster knows nothing, then no-one would help. :rolleyes:

ConsultClifford, in the spirit of helping, rather than mouthing off, I'd first make sure that only numbers, commas and spaces were present in $_REQUEST .. you can easily check it against a simple regular expression "[0-9, ]".. rsynott is right, though it will allow injection attacts, but then most code snippets here will. I had rightly, or wrongly, assumed you'd be aware of this.

.cg

rsynnott

No, the correct thing to do is generally to use addslashes on each thing being put in the database, and stripslashes coming out. That way, you don't have to worry about the user having an apostrophy in their name and similar.

It's amazing how often people don't do this...

cgarvey

Are we not dealing with numbers/ids here?

ConsultClifford

indeed there is the injection issue with this code... looking into alternatives

.cg

Yes its ids we are dealing with

cgarvey wrote:

. and assuming that $_REQUEST contains "1,2,3", then your query will be
SELECT a.id FROM a WHERE a.job IN( 1,2,3 ) .. which should do the trick. You could also SELECT COUNT(*) instead of a.id, etc.
.cg

jost out of interest amd to note this does not work anyhow this only takes into account the first no. in the db field

ie if i have three entries in the db and $_REQUEST is selected to be 1 only:

id job
1 1
2 0,1,3,6
3 1,2,3,6

then it only returns the second and third ones.
(seems its looking for the $_REQUEST values in the first digit of db field only).....

Tnx

cgarvey

OK, that's not how I read the opening post .. so if the 'job' column is some sort of text column with sample content "0,1,3,6" (withouth the quotes), $_REQUEST contains "1", and you want to return all 3 sample rows, then you'd need something like ..

SELECT a.id FROM a WHERE a.job ="$_REQUEST['job']%"  OR a.job LIKE "$_REQUEST['job'],%" OR a.job LIKE "%,$_REQUEST['job'],%" OR a.job LIKE "%,$_REQUEST['job']"

So the 1st where clause deals with when job just contains one id, the 2nd deals with when the id being searched is at the beginning, the 3rd when it's in the middle, the 4th when it's at the end... I'm sure there's a more elegant solution, but that should get you started in the meantime.

rsynnott

Incidentally, you may want to consider rethinking that architecture.