how to SELECT mysql using array in database

Question

i have an array in databae like this :

    Field : smt_id
    Value in database : 261, 323
    type : varchar(100)

smt_id is not fix field. if i updated as 3 arrays it will be like this example : 261, 323, 111 and variable $mid will change to 261, 323, 111

now i want to do sql statement like this using this variable.

$mid = 261, 323

$sql = "SELECT first_name FROM ".TABLE_PREFIX."members WHERE member_id ='$mid'";
$result = mysql_query($sql,$db);

when i print the sql the result is like this:

SELECT first_name FROM AT_members WHERE member_id ='261, 323'

My question is, how i change above sql so it become like this :

SELECT first_name FROM AT_members WHERE member_id IN ('261', '323')

and how can i make every result break like this:

John 
Merry

Always just 2 values separated by a comma? varchar data type? — Brian DeMilia
– Brian DeMilia, Commented Aug 6, 2014 at 1:44
no i inserted the database as an array so it can be updated more than two — airi
– airi, Commented Aug 6, 2014 at 1:45
Assuming this $mid = 261, 323 is your actual code, you'd need to do $mid = array('261', '323'); and then explode on the array. That may also be implode or even in_array(). There are a many ways of doing this, but you get the general idea ;) — Funk Forty Niner
– Funk Forty Niner, Commented Aug 6, 2014 at 1:59
yup, creating a string with desired separator from an array is implode — epipav
– epipav, Commented Aug 6, 2014 at 2:01

Gras Double · Accepted Answer · 2014-08-06 02:11:20Z

1

$mid = explode(',', preg_replace('/\s+/', '', $mid));
$sql = 'SELECT first_name FROM '.TABLE_PREFIX.'members WHERE member_id IN ('.implode(',', $mid).')';

edited Aug 6, 2014 at 2:11

Gras Double

16.5k9 gold badges61 silver badges56 bronze badges

answered Aug 6, 2014 at 2:09

slik

5,2516 gold badges37 silver badges41 bronze badges

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

1 Comment

Gras Double Over a year ago

This is the best approach for the current case, as $mid is normalized in a separate step before being provided to the SQL query. But $mid should be an array beforehand, not at this last moment ;)

Gras Double · Accepted Answer · 2014-08-06 01:47:32Z

0

Asserting $mid is an array:

'WHERE member_id IN ('.implode(',', $mid).')'

It also works if the array has only one value.

Just as a reminder, code like that is vulnerable to SQL injection.

answered Aug 6, 2014 at 1:47

Gras Double

16.5k9 gold badges61 silver badges56 bronze badges

5 Comments

airi Over a year ago

hi thanks. your code ok but no row return.. MySQL returned an empty result set (i.e. zero rows)

epipav Over a year ago

because your variable $mid is not an array most likely

Gras Double Over a year ago

If $mid is a string like '261, 323' you are doing something wrong.

airi Over a year ago

hurmm.. maybe.. because when i echo it will be ex: 123, 234 ... HOW CAN I CHANGE '261, 323' <-- VARCHAR to ('261', '323') ... do you know how?

Gras Double Over a year ago

Don't confuse PHP types and MySQL types. There are 2 separate worlds. Then, you rather should use var_dump() for debugging. Here you'll likely see your variable is a string. So just put it like so: 'WHERE IN ('.$mid.')'. But this is dirty, $mid should be an array, consider reviewing your preceding code.

epipav · Accepted Answer · 2014-08-06 02:00:39Z

0

stop using mysql extension

it is deprecated. use mysqli or PDO instead(sql injection alert). Moreover don't push your variables directly to the query, use prepared statements. Check here. Imploding is well explained in Double Gras' answer. For your second question, if you want every result one by one, you should fetch them one by one. fetch_assoc is good here for mysqli extension.

simple usage of fetch_assoc:

if ($result = $mysqli->query($query)) {

        /* fetch associative array */
        while ($row = $result->fetch_assoc()) {
            printf ("%s (%s)\n", $row["column1"], $row["column2"]);
        }

edited Aug 6, 2014 at 2:00

answered Aug 6, 2014 at 1:52

epipav

3392 silver badges14 bronze badges

3 Comments

Gras Double Over a year ago

Indeed, running the query (which is done on the SQL server) then fetching the results (at the pace you want, consumes network traffic) are 2 distinct operations.

user2864740 Over a year ago

While I agree with the sentiments, this answer does not answer the OPs question - nor does it show proper multi-parameter binding. If an actual answer is not provided, consider a comment.

epipav Over a year ago

and how can i make every result break like this:

Collectives™ on Stack Overflow

how to SELECT mysql using array in database

3 Answers 3

1 Comment

5 Comments

stop using mysql extension

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

stop using mysql extension

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related