How to put array into MySQL?

Question

i have an array

variable $smt_database = 257, 259, 261

Array ( [0] => 257, 259, 261 )

how can i insert this variable $smt_database in database like this :

$sql = "SELECT first_name FROM '.TABLE_PREFIX.'members 'WHERE member_id IN ($smt_database)";

when i echo the sql it show array like below :

SELECT first_name FROM '.TABLE_PREFIX.'members 'WHERE member_id IN (Array)

how can i change so it can like sql below :

SELECT first_name FROM '.TABLE_PREFIX.'members 'WHERE member_id IN (257, 259, 261)

i know we must use implode and explode but i do not know how to implement them.

Where does the `$smt_database` variable come from? Is it user supplied data? — Ja͢ck, Aug 11 '14 at 05:41
You store comma separated data in your columns? That's a SQL anti-pattern ... besides that, you can just use `${stm_database[0]}`. — Ja͢ck, Aug 11 '14 at 05:44
yes.. hehe.. i dunno anti pattern.. i saw one of the answer below.. nice approach.. i never though of that — airi, Aug 11 '14 at 05:46
See also [this answer](http://stackoverflow.com/questions/738133/comma-separated-values-in-a-database-field) for more information on "Jaywalking" anti-pattern. — Ja͢ck, Aug 11 '14 at 05:47
@airi If you got answer from following answer then accept it for future reference — Sadikhasan, Aug 11 '14 at 05:47
@Sadikhasan FYI .. i need to wait 10 minute to accept any answer — airi, Aug 11 '14 at 05:49

score 2 · Accepted Answer · answered Aug 11 '14 at 05:35

2

simple use implode

$in_text = implode(",", $smt_database);

// now use this variable in sql like

$sql = "SELECT first_name FROM '.TABLE_PREFIX.'members 'WHERE member_id IN ($in_text)";

above will work if the values in array are numeric

So if value in array are not numeric you need to single quotes on your value try

update 2 :

$new_array = array();
foreach($your_array as $val)
{
    $new_array[] = "'".$val."'";
}

// now use implode

 $in_text = implode(",", $new_array);

  // now use this variable in sql

answered Aug 11 '14 at 05:35

Satish Sharma

9,475
6
27
51

implode is useless for `Array ( [0] => 257, 259, 261 )` its useful for `Array ( [0] => 257,[1]=> 259,[2]=> 261 )` – Manwal Aug 11 '14 at 05:44
thanks .. i just missing the implode .. $mid1 = implode(',', preg_replace('/\s+/', '', $smt_database)); – airi Aug 11 '14 at 05:51

score 1 · Answer 2 · answered Aug 11 '14 at 05:38

1

Use with impload and FIND_IN_SET because IN operator do not work on String value.

$your_text = implode(",",$smt_database);

$sql = "SELECT first_name FROM '.TABLE_PREFIX.'members 'WHERE FIND_IN_SET(member_id,$your_text)";

answered Aug 11 '14 at 05:38

Sadikhasan

17,858
20
77
117

bro `,` is missing in `implode` – Satish Sharma Aug 11 '14 at 05:40

score 1 · Answer 3 · answered Aug 11 '14 at 05:38

1

Use this:

$sql = "SELECT first_name FROM '.TABLE_PREFIX.'members 'WHERE member_id IN (".$smt_database[0].")";

assuming you have array $smt_database = Array ( [0] => 257, 259, 261 )

answered Aug 11 '14 at 05:38

Manwal

22,994
11
59
91

hi nice way to solve my problems.. thanks for the knowledge sharing.. never thought the way of solving this kind of problems like this.. – airi Aug 11 '14 at 05:55

How to put array into MySQL?

3 Answers3