MySql select field_1 where X = field_2 for all supplied Xs in a single query

Question

How do I get all the 'name' column corresponding to the supplied ids in a single query ?

Suppose I have a php array like this $ids = [ '1', '3', '7', '24', ... ]; then, is it possible to get the 'name' in the following table WHERE $ids[i]='id' in that table, all in a single query, or do I need to use for loop ?

CREATE TABLE `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `description` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`) USING BTREE
)

I am working with CodeIgniter.

possible duplicate of [PHP/MySQL using an array in WHERE clause](http://stackoverflow.com/questions/907806/php-mysql-using-an-array-in-where-clause) — Criesto, Sep 08 '15 at 05:04

Saty · Accepted Answer · 2015-09-08T05:06:05.477

2

You can use where_in for this

$ids = [ '1', '3', '7', '24', ... ];
$this->db->select('name');
$this->db->from('services');
$this->db->where_in('id', $ids);

edited Sep 08 '15 at 05:06

answered Sep 08 '15 at 05:05

Saty

22,443
7
33
51

score 1 · Answer 2 · answered Sep 08 '15 at 05:04

1

you can use IN clause

 SELECT * FROM `services` WHERE ID IN (".implode(',' , $ids).");

In case the dataType is varchar , make sure to add ' accordingly

 SELECT * FROM `services` WHERE ID IN ('".implode('','' , $ids)."');

answered Sep 08 '15 at 05:04

sanjeev

4,405
2
19
27

score 0 · Answer 3 · edited Sep 08 '15 at 06:10

0

you can use query bindings too, if the data array repeats :

<?php
    $sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";

    $this->db->query($sql, array(3, 'live', 'Rick'));   
?>

edited Sep 08 '15 at 06:10

Criesto

1,985
4
32
41

answered Sep 08 '15 at 05:34

Sagar Sawant

1
3

MySql select field_1 where X = field_2 for all supplied Xs in a single query

3 Answers3