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.

do you mean: WHERE room_no != $cid_room AND roomtype='".$roomtype."'"; ??? — Nikola Drosakis
– Nikola Drosakis, Commented May 14, 2015 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
– user12688, Commented May 14, 2015 at 17:27
My comment is not related to your question. it is a offer for the security of your code — user4795756
– user4795756, Commented May 14, 2015 at 17:28

Nikola Drosakis · Accepted Answer · 2015-05-14 21:50:41Z

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, 2015 at 21:50

answered May 14, 2015 at 17:32

Nikola Drosakis

2,3581 gold badge22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Misunderstood · Accepted Answer · 2015-05-14 17:59:03Z

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, 2015 at 17:59

Misunderstood

5,6791 gold badge20 silver badges25 bronze badges

Comments

Master Yoda · Accepted Answer · 2015-05-14 18:03:44Z

0

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."'";

answered May 14, 2015 at 18:03

Master Yoda

5318 silver badges23 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related