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
– Vikas Gautam, Commented Feb 4, 2014 at 5:36

Okonomiyaki3000 · Accepted Answer · 2014-02-04 05:56:03Z

2

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.

edited Feb 4, 2014 at 5:56

answered Feb 4, 2014 at 5:50

Okonomiyaki3000

3,70625 silver badges23 bronze badges

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

2 Comments

Phil Over a year ago

Might as well sanitise $section_id1 too

Okonomiyaki3000 Over a year ago

Thanks, @Phil, I didn't notice it!

Phoenix · Accepted Answer · 2014-02-04 05:39:39Z

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

Phoenix

1,52817 silver badges23 bronze badges

Comments

user2727841 · Accepted Answer · 2014-02-04 09:41:24Z

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 4, 2014 at 9:41

answered Feb 4, 2014 at 5:39

user2727841

7256 silver badges22 bronze badges

10 Comments

Phil Over a year ago

Considering the member_id array comes from external input, this is very unsafe

Phil Over a year ago

PDO or MySQLi, prepared statements and parameter binding. It's really the only safe way

Phil Over a year ago

Yet another reason not to use them

Satish Sharma Over a year ago

@Phil have you seen. OP using mysql_query so don't try to demotivate anyone else

Satish Sharma Over a year ago

@Phil you will protect them from mysql injection am i right?

|

Satish Sharma · Accepted Answer · 2014-02-04 05:40:44Z

-1

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";

answered Feb 4, 2014 at 5:40

Satish Sharma

9,6356 gold badges31 silver badges52 bronze badges

1 Comment

airi Over a year ago

well.. this is offline website not online and it only used by one administrator.. cheers :)

Collectives™ on Stack Overflow

PHP: How to insert array value into mysql statement

4 Answers 4

2 Comments

Comments

10 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related