How to use array in select query where statement in php?

Question

I have code like this:

$sql11 = "select room_no from guestrocordtransc where roomtype='".$roomtype."'";
$retval11 = mysql_query( $sql11, $conn );
while($row11 = mysql_fetch_array($retval11,MYSQL_ASSOC))
{ 
    $cid_room=$row11['roomno']; 
}

Here I'm getting an array of room numbers, which is stored in $cid_room.

Now, I need this $cid_room in select query to fetch a particular room_no which is not in guestrocordtransac.

My second query looks like this:

$sql = "select room_no 
FROM roominfo WHERE room_no 
NOT IN(
select roomno 
from guestrocordtransac 
where roomtype='".$roomtype."' 
between '".$check_in."' 
and '".$check_out."' 
and cid='".$cid_room."') 
and roomtype='".$roomtype."'";

Is it possible to give the array value ($cid_room) like this in query, will it check all the value.

Don't use mysql_* http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — , May 14 '15 at 17:25
do you mean: WHERE room_no != $cid_room AND roomtype='".$roomtype."'"; ??? — Nik Drosakis, May 14 '15 at 17:26
rooinfo table is like this: id,room_no,roomtype,cid .......for eg:(1st row) 1 302 a/c 1....(2nd row)2 201 non_a/c 2.....I hope you understand — user12688, May 14 '15 at 17:27
My comment is not related to your question. it is a offer for the security of your code — , May 14 '15 at 17:28

Nik Drosakis · Answer 1 · 2015-05-14T21:50:41.623

1

if $cid_room is not a multi-dimensional array, you use array in select query like that:

"SELECT room_no FROM roominfo WHERE room_no NOT IN (".implode(',',$cid_room).") AND roomtype='".$roomtype."'";

But I ' m not sure that works in your case.

edited May 14 '15 at 21:50

answered May 14 '15 at 17:32

Nik Drosakis

2,258
21
30

score 0 · Answer 2 · answered May 14 '15 at 17:59

0

You can convert the array to an IN() clause.

$in = '(';
foreach($cid_room as $num){
  $in.= "'$num',"
}
$in = substr($sql,0,-1) . ') ';  // remove trailing comma and close

answered May 14 '15 at 17:59

Misunderstood

5,534
1
18
25

score 0 · Answer 3 · answered May 14 '15 at 18:03

1: while($row11 = mysql_fetch_array($retval11,MYSQL_ASSOC)) { $cid_room=$row11['roomno']; }

by this u are not getting a array of $cid_room

u have to use $cid_room[] instead of $cid_room

$sql = "select room_no FROM roominfo WHERE room_no NOT IN( select roomno from guestrocordtransac where roomtype='".$roomtype."' between '".$check_in."' and '".$check_out."' and cid in '".$cid_room."') and roomtype='".$roomtype."'";

How to use array in select query where statement in php?

3 Answers3