PHP: How to insert array value into mysql statement

Question

i have this array and get it from from an url. this array is member id that i need to pass to mysql.

$member_id = $_GET['member_id'];

the array like this : Array ( [0] => 1269 [1] => 385 )

how can i transfer this array into my mysql statement and make , become AND :

$answer_sql = mysql_query("SELECT tna_category. * , tna_question. *, tna_answer. * 
                        FROM tna_category, tna_question, tna_answer 
                        WHERE  tna_category.section_id = '$section_id1' 
                        AND tna_question.id = tna_answer.question_id AND tna_question.category_id = tna_category.id 
                        AND tna_answer.member_id = ['1269' , '385']
                        ORDER BY tna_answer.question_id");

should i put bracket?..

in this part : tna_answer.member_id = Array or $member_id

get teh array value by foreach and pass the query in foreach — Vikas Gautam, Feb 04 '14 at 05:36
[Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Phil, Feb 04 '14 at 05:37
Also, try `tna_answer_member_id IN (1269, 385)`. See [`IN`](http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_in) — Phil, Feb 04 '14 at 05:38
wait... i will test all of you guys answers... i do not know why intelligent people like to downgrade questions and answer. If they do not know just ignore my question. — airi, Feb 04 '14 at 05:44
possible duplicate of [PHP PDO: Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) — Phil, Feb 04 '14 at 05:50
@airi you can also use `mysql_real_escape_string` function like this `$member_id = mysql_real_escape_string($_GET['member_id']);` and then explode it just I did... check it here about `mysql_real_escape_string` `www.php.net/mysql_real_escape_string` — user2727841, Feb 04 '14 at 05:52
check member id like `$member_id = explode(",", $member_id);` `print_r($member_id);` and tell me what are you getting... — user2727841, Feb 04 '14 at 06:04
If you are getting comma seprated value in `$_GET["member_id"];` don't use explode just use implode. — user2727841, Feb 04 '14 at 06:11

Okonomiyaki3000 · Answer 1 · 2014-02-04T05:56:03.010

As others have said, you can use IN() but you are apparently open to SQL injection attacks as it is. You need to do this:

$escaped_ids = array_map('mysql_real_escape_string', $member_ids);

Or, if they are surely all integers

$escaped_ids = array_map('intval', $member_ids);

Then, you can write your query like:

$query = "SELECT tna_category. * , tna_question. *, tna_answer. * 
          FROM tna_category, tna_question, tna_answer 
          WHERE  tna_category.section_id = '" . mysql_real_escape_string($section_id1) . "' 
              AND tna_question.id = tna_answer.question_id 
              AND tna_question.category_id = tna_category.id 
              AND tna_answer.member_id IN (".implode(",", $escaped_ids).")
          ORDER BY tna_answer.question_id";

Never, never, never put unescaped values in your query.

Also, you should not be using the mysql_ functions anymore. Please consider using the mysqli_ functions instead.

Might as well sanitise `$section_id1` too – Phil Feb 04 '14 at 05:53 — Phil, Feb 04 '14 at 05:53
Thanks, @Phil, I didn't notice it! – Okonomiyaki3000 Feb 04 '14 at 05:53 — Okonomiyaki3000, Feb 04 '14 at 05:53

score 1 · Answer 2 · answered Feb 04 '14 at 05:39

1

First split the array value, get no. of rows in the array value and pass the value one by one into the query by using for or foreach loop.

answered Feb 04 '14 at 05:39

Phoenix

1,470
17
23

user2727841 · Answer 3 · 2014-02-04T09:41:24.637

1

try this

$member_id = $_GET['member_id'];

If you're already getting comma seprated values then there's no need to use explode function just use implode function in database query.

$member_id = explode(",", $member_id);

and then

answer_sql = mysql_query("SELECT tna_category. * , tna_question. *, tna_answer. * 
                        FROM tna_category, tna_question, tna_answer 
                        WHERE  tna_category.section_id = '$section_id1' 
                        AND tna_question.id = tna_answer.question_id AND tna_question.category_id = tna_category.id 
                        AND tna_answer.member_id IN (".implode(",", $member_id).")
                        ORDER BY tna_answer.question_id");

the explode function create array it depends on you explode value with comma OR space and then implode mean join these values with comma OR space. for more detail explode and implode.

edited Feb 04 '14 at 09:41

answered Feb 04 '14 at 05:39

user2727841

715
6
21

Considering the `member_id` array comes from external input, this is very unsafe – Phil Feb 04 '14 at 05:41
PDO or MySQLi, prepared statements and parameter binding. It's really the **only** safe way – Phil Feb 04 '14 at 05:44
Yet another reason **not** to use them – Phil Feb 04 '14 at 05:45
@Phil have you seen. OP using `mysql_query` so don't try to demotivate anyone else – Satish Sharma Feb 04 '14 at 05:46
@Phil you will protect them from mysql injection am i right? – Satish Sharma Feb 04 '14 at 05:46
@SatishSharma I'm in fact attempting to do the opposite; I'm trying to **motivate** people to stop using deprecated and unsafe libraries. – Phil Feb 04 '14 at 05:47
@SatishSharma I don't have to. It's already been answered [here](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) – Phil Feb 04 '14 at 05:51
Thanks to Satish Sharma and Phil I also learn here one more new thing thanks for all of you!!! – user2727841 Feb 04 '14 at 05:55
i got this : Warning: explode() expects parameter 2 to be string, array given – airi Feb 04 '14 at 05:58
You learnt a **new** thing?! This stuff has been around for almost 10 years! There's been a large banner on all the `mysql_*` functions recommending you use MySQLi or PDO for 10 years! – Phil Feb 04 '14 at 06:00

score -1 · Accepted Answer · answered Feb 04 '14 at 05:40

you can use IN clause of mysql like this

$your_array = array("0"=>"1269", "1"=>"385");


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

   $sql = "SELECT tna_category. * , tna_question. *, tna_answer. * 
        FROM tna_category, tna_question, tna_answer 
        WHERE  tna_category.section_id = '$section_id1' 
               AND tna_question.id = tna_answer.question_id 
               AND tna_question.category_id = tna_category.id  
               AND tna_answer.member_id IN ($in_text)
               ORDER BY tna_answer.question_id";

well.. this is offline website not online and it only used by one administrator.. cheers :) — airi, Feb 05 '14 at 03:45

PHP: How to insert array value into mysql statement

4 Answers4