How to use array values in where clause of select query?

Question

i know this is a duplicate one..

but i couldn't find what i wanted ..

I have an array value like this

$startpoint= (implode(",",$_SESSION['strtpnt1']));
// "chennai,madurai,tirunelveli,Kanyakumari"

I'm using a select query in a select box to fetch the datas from database which are not in array

My query looks like this:

$sql21 = "select start from tbl_place where start NOT IN='" . implode(",",$_SESSION['strtpnt1']) ."'";//i tried this and 

$sql21 = "select start from tbl_place where start NOT IN='" . $startpoint ."'";//this

You don't want to build a SQL query like this. Are you using PDO or mysqli? — Ja͢ck, Jun 18 '15 at 06:26
@jack ,i removed the = sign and tested the query in mysql loaclhost wampserver the query works fine...but when i implemented it in my code ..i couldnt get the values in the select box.... — user12688, Jun 18 '15 at 08:46
It may stem from the same thing you're working on, but as far as we're concerned, it's a different question. — Ja͢ck, Jun 18 '15 at 08:49

Sougata Bose · Answer 1 · 2015-06-18T06:39:33.410

0

You need to add 's as they are strings -

$startpoint= implode("','", $_SESSION['strtpnt1']); // Escape values if needed

And the query will be -

$sql21 = "select start from tbl_place where start NOT IN ('" . $startpoint ."')";

No = needed.

edited Jun 18 '15 at 06:39

answered Jun 18 '15 at 06:24

Sougata Bose

31,517
8
49
87

The reason for downvote? – Sougata Bose Jun 18 '15 at 06:28
1

I don't know why somebody downvoted your solution (looks good at the first sight), but he should escape the string before using $_SESSION vars in a SQL query. E.g., using MySQL: `$escaped = array_map('mysql_real_escape_string', $_SESSION['strtpnt1']);` `$startpoint= implode("','",$escaped);` – André Jun 18 '15 at 06:30
@André thats right if the values are taken from user. – Sougata Bose Jun 18 '15 at 06:31
@André your comment makes no sense who says he's using `mysql_*` ? – Daan Jun 18 '15 at 06:31
@Daan that's like a precaution. – Sougata Bose Jun 18 '15 at 06:32
3

Judging from the level of OP's expertise I would deem the risk quite high that `$_SESSION['strtpnt1'] = $_POST['strtpnt1'];` can be found elsewhere in the code. – Ja͢ck Jun 18 '15 at 06:33
But could any1 guess the reason of downvote? – Sougata Bose Jun 18 '15 at 06:34
1

@Daan: You are right, maybe he is not using mysql_*, so a different escape function may be the right one. I updated my comment with an "e.g." ;-) – André Jun 18 '15 at 06:37

score 0 · Answer 2 · answered Jun 18 '15 at 06:26

0

The correct syntax for using IN in mySQL is:

SELECT something FROM something WHERE something NOT IN (1, 2, 3, 4)

So don't use the =.

answered Jun 18 '15 at 06:26

Markus F

96
4

How to use array values in where clause of select query?

2 Answers2