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
– Ja͢ck, Commented Aug 11, 2014 at 5: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
– Ja͢ck, Commented Aug 11, 2014 at 5:44
yes.. hehe.. i dunno anti pattern.. i saw one of the answer below.. nice approach.. i never though of that — airi
– airi, Commented Aug 11, 2014 at 5:46
See also this answer for more information on "Jaywalking" anti-pattern. — Ja͢ck
– Ja͢ck, Commented Aug 11, 2014 at 5:47

Satish Sharma · Accepted Answer · 2014-08-11 05:35:58Z

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, 2014 at 5:35

Satish Sharma

9,6356 gold badges31 silver badges52 bronze badges

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

2 Comments

Manwal Over a year ago

implode is useless for Array ( [0] => 257, 259, 261 ) its useful for Array ( [0] => 257,[1]=> 259,[2]=> 261 )

airi Over a year ago

thanks .. i just missing the implode .. $mid1 = implode(',', preg_replace('/\s+/', '', $smt_database));

Sadikhasan · Accepted Answer · 2014-08-11 05:38:33Z

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, 2014 at 5:38

Sadikhasan

18.6k23 gold badges84 silver badges126 bronze badges

1 Comment

Satish Sharma Over a year ago

bro , is missing in implode

Manwal · Accepted Answer · 2014-08-11 05:38:47Z

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, 2014 at 5:38

Manwal

23.9k15 gold badges65 silver badges95 bronze badges

1 Comment

airi Over a year ago

hi nice way to solve my problems.. thanks for the knowledge sharing.. never thought the way of solving this kind of problems like this..

Collectives™ on Stack Overflow

How to put array into MySQL?

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related