How to use PHP Array in MySQL Select statement with BETWEEN?

Question

I am looking to use the contents of an array

$arr =array(24,28,30,34, 40);

and pass these into the where clause of a MySQL select statement, all my research has shown this done by using IN to pass in all the array values in one go.

I need to pass in each array element one at a time and then echo out the results of the SQL statement one at a time as the select statement is updated with the next array element.

New to programming and PHP so just need a little example to get me started...

Thanks to Zad highlighted the real issue

I need to pass each array value individually to a SQL statement as these need to be utilised in Where clause with BETWEEN, eg. WHERE age BETWEEN $array1 AND $array2 in order to determine count over an age range

thanks for all the input

So, you need ... WHERE age BETWEEN 24 AND 28, ... WHERE age BETWEEN 28 AND 30, etc? — Timur
– Timur, Commented Feb 21, 2012 at 7:23

Community · Accepted Answer · 2023-11-17 20:04:57Z

1

You can use the implode function to build the string that contains the list;

$arr =array(24,28,30,34, 40);
$query = 'SELECT * FROM mytable WHERE id IN (' .implode($arr, ', '). ' )';
echo $query;`

http://codepad.org/tLPZxq8P

https://www.php.net/manual/en/function.implode.php

edited Nov 17, 2023 at 20:04

CommunityBot

11 silver badge

answered Feb 21, 2012 at 6:08

zad

3,4352 gold badges26 silver badges25 bronze badges

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

10 Comments

user1222646 Over a year ago

Thanks.. but i need to pass in each element one at a time, I would use IN if i wanted to pass all array elements at once

zad Over a year ago

Why do you need to pass one by one?, using IN is a lot faster since only requires to query the database once.

user1222646 Over a year ago

because i want to do a count of the number of records with each loop through

user1222646 Over a year ago

eg. SELECT COUNT(id) FROM table WHERE age = $array

zad Over a year ago

after using the IN method you can just call the mysql_num_rows function and get the number of records. php.net/manual/en/function.mysql-num-rows.php

|

silly · Accepted Answer · 2012-02-21 06:11:31Z

1

try it with escaping the argument

foreach($arr as $array_element) {
    $query = 'SELECT * FROM table WHERE field = \''.mysql_real_escape_string($array_element).'\'';
    //your statement
}

answered Feb 21, 2012 at 6:11

silly

7,8972 gold badges27 silver badges37 bronze badges

Comments

Andrew Jackman · Accepted Answer · 2012-02-21 06:06:51Z

0

You can use a foreach function:

// make connection to mysql server
foreach ( $arr as $element ) {
    $statement = "SELECT whatever FROM wherever WHERE something = $element"; // maybe a little validation here wouldn't hurt either
    // execute statement
    // process results
} // end of foreach
// close connection

answered Feb 21, 2012 at 6:06

Andrew Jackman

14k8 gold badges38 silver badges44 bronze badges

Comments

Kavi Siegel · Accepted Answer · 2012-02-21 06:07:16Z

0

$arr =array(24,28,30,34, 40);
$a = 'SELECT * FROM foo WHERE bar IN('.implode(',',$arr).')';

Edit: I'll admit, I didn't fully read the question, the title is misleading - consider changing that.

I need to pass in each array element one at a time and then echo out the results of the SQL statement one at a time as the select statement is updated with the next array element.

Could you explain how the scenario a bit better?

answered Feb 21, 2012 at 6:07

Kavi Siegel

2,9942 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

How to use PHP Array in MySQL Select statement with BETWEEN?

4 Answers 4

10 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

10 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related