how to use array value in where IN clause in sql

Question

At where clause i am getting one id on $values variable, while there are four id exist in $rest_id. How to fix this problem that i could get all the four ids in where clause. Anyone please help to fix this issue please. p.

foreach ($rest_id as $value) 
{
$ids = array($value->id);
$nearest_rest = "SELECT *, (3956 * 2 * ASIN(SQRT( POWER(SIN(( 28.5812674 - lat) * pi()/180 / 2), 2) +COS( 28.5812674 * pi()/180) * COS(lat * pi()/180) * POWER(SIN(( 77.3181059 - lng) * pi()/180 / 2), 2) ))) as distance 
                   FROM restaurant_details 
                  WHERE id In ('" . implode("','",$ids) . "') 
                 HAVING distance 
               ORDER BY distance ASC 
                  LIMIT 1"; 
}

1

question is not much clear – JYoThI Sep 09 '16 at 07:19

score 0 · Answer 1 · answered Sep 09 '16 at 07:10

0

// first - take all ids to `$ids` array:
$ids = array();
foreach ($rest_id as $value) {
    $ids[] = $value->id;
}
// then do your query:
$nearest_rest = "SELECT *, (3956 * 2 * ASIN(SQRT( POWER(SIN(( 28.5812674 - lat) * pi()/180 / 2), 2) +COS( 28.5812674 * pi()/180) * COS(lat * pi()/180) * POWER(SIN(( 77.3181059 - lng) * pi()/180 / 2), 2) ))) as distance from restaurant_details where id In ('" . implode("','",$ids) . "') having distance order by distance asc limit 1";

answered Sep 09 '16 at 07:10

u_mulder

54,101
5
48
64

still its doing the same.. No any changes – prince Sep 09 '16 at 07:17
your limiting the out put by limit 1 look at the end of query @prince – JYoThI Sep 09 '16 at 07:20
but when i print the whole query then still at the place of $value there is only one id – prince Sep 09 '16 at 07:26
This means that you have one item in `$rest_id`. Have you checked it? – u_mulder Sep 09 '16 at 07:45
yup i have four ids in $rest_id – prince Sep 09 '16 at 08:49

score 0 · Answer 2 · answered Sep 09 '16 at 07:22

Hello to use array of ids in the query please try below solution.

foreach ($rest_id as $value) {
    $ids[] = $value->id;
}

//Than now write your query this way
$nearest_rest = "SELECT *, (3956 * 2 * ASIN(SQRT( POWER(SIN(( 28.5812674 - lat) * pi()/180 / 2), 2) +COS( 28.5812674 * pi()/180) * COS(lat * pi()/180) * POWER(SIN(( 77.3181059 - lng) * pi()/180 / 2), 2) ))) as distance from restaurant_details where id In (" . implode(",",$ids) . ") having distance order by distance asc limit 1";

how to use array value in where IN clause in sql

2 Answers2