0

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
airi
  • 585
  • 5
  • 21
  • Always just 2 values separated by a comma? varchar data type? – Brian DeMilia Aug 06 '14 at 01:44
  • no i inserted the database as an array so it can be updated more than two – airi Aug 06 '14 at 01:45
  • what is the data type of the column smt_id? – Brian DeMilia Aug 06 '14 at 01:46
  • 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 Aug 06 '14 at 01:59
  • yup, creating a string with desired separator from an array is implode – epipav Aug 06 '14 at 02:01
  • @epipav Depends on the function used. As I said, there are multiple ways of doing this. It's a bit unclear though as to how OP is iterating over the variable/numbers. – Funk Forty Niner Aug 06 '14 at 02:02
  • @Fred-ii- i just want to change 123, 234 to "123", "234" so my sql works.. you know how? – airi Aug 06 '14 at 02:05
  • Read: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?lq=1 , http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – user2864740 Aug 06 '14 at 05:10
  • possible duplicate of [PHP/MYSQL using an array in WHERE clause](http://stackoverflow.com/questions/907806/php-mysql-using-an-array-in-where-clause) – outis Aug 06 '14 at 09:34

3 Answers3

1
$mid = explode(',', preg_replace('/\s+/', '', $mid));
$sql = 'SELECT first_name FROM '.TABLE_PREFIX.'members WHERE member_id IN ('.implode(',', $mid).')';
Gras Double
  • 15,901
  • 8
  • 56
  • 54
slik
  • 5,001
  • 6
  • 34
  • 40
  • 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 Aug 06 '14 at 02:15
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.

Gras Double
  • 15,901
  • 8
  • 56
  • 54
  • hi thanks. your code ok but no row return.. MySQL returned an empty result set (i.e. zero rows) – airi Aug 06 '14 at 01:55
  • because your variable $mid is not an array most likely – epipav Aug 06 '14 at 01:59
  • If $mid is a string like `'261, 323'` you are doing something wrong. – Gras Double Aug 06 '14 at 02:00
  • 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? – airi Aug 06 '14 at 02:02
  • Don't confuse PHP types and MySQL types. There are 2 separate worlds. Then, you rather should use [var_dump()](http://php.net/manual/en/function.var-dump.php) 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. – Gras Double Aug 06 '14 at 02:07
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"]);
        }
epipav
  • 339
  • 2
  • 14
  • 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. – Gras Double Aug 06 '14 at 01:58
  • 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. – user2864740 Aug 06 '14 at 05:09
  • _and how can i make every result break like this:_ – epipav Aug 06 '14 at 07:22