SQL select everything in an array

Question

My homework has a problem for example there is a category array $cat=array('1','4','5','7'); now i need to select products from db based on the category that is

SELECT * FROM products WHERE catid='1'
SELECT * FROM products WHERE catid='4'
SELECT * FROM products WHERE catid='5'
SELECT * FROM products WHERE catid='7'

Is it possible to do this in a single query? as the final results of the four queries will be combined.

Himanshu · Accepted Answer · 2017-06-20 13:04:05Z

124

SELECT * FROM products WHERE catid IN ('1', '2', '3', '4')

edited Jun 20, 2017 at 13:04

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

answered Apr 1, 2011 at 0:54

Senthil

5,8722 gold badges24 silver badges11 bronze badges

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

2 Comments

user10207601 Over a year ago

is this query return data in this order only? like the first record is with id=1, second of 2 and so on?

mstolt Over a year ago

To ensure sorting, you have to add a ORDER BY to the query. SELECT * FROM products WHERE catid IN ('1', '2', '3', '4') ORDER BY catid

Alfan Zain · Accepted Answer · 2019-07-22 07:33:58Z

21

// array of $ids that you need to select
$ids = array('1', '2', '3', '4', '5', '6', '7', '8');

// create sql part for IN condition by imploding comma after each id
$in = '(' . implode(',', $ids) .')';

// create sql
$sql = 'SELECT * FROM products WHERE catid IN ' . $in;

// see what you get
var_dump($sql);

Update: (a short version and update missing comma)

$ids = array('1','2','3','4');
$sql = 'SELECT * FROM products WHERE catid IN (' . implode(',', $ids) . ')';

edited Jul 22, 2019 at 7:33

Alfan Zain

557 bronze badges

answered Jan 15, 2014 at 15:24

ustmaestro

1,30313 silver badges24 bronze badges

2 Comments

elliotching Over a year ago

your answer would be useful for very huge amount of values in an array/list

ustmaestro Over a year ago

@elliotching yes, you just must have an array with ids, everything else is generated by 1-2 lines of script, here is the long version just for readability and for comments on what each line do

Noor · Accepted Answer · 2011-04-01 01:12:30Z

-3

$SQL_Part="("
$i=0;
while ($i<length($cat)-1)
{
   $SQL_Part+=$cat[i]+",";
}
$SQL_Part=$SQL_Part+$cat[$i+1]+")"

$SQL="SELECT * FROM products WHERE catid IN "+$SQL_Part;

It's more generic and will fit for any array!!

answered Apr 1, 2011 at 1:12

Noor

20.2k41 gold badges147 silver badges264 bronze badges

Collectives™ on Stack Overflow

SQL select everything in an array

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related