Inserting array values into MySQL

Question

I tried to find another question with the answer to this but I've had no luck. My question is basically...will this work?

$insert_tweets = "INSERT INTO tweets (
  'id',
  'created_at',
  'from_user_id',
  'profile_image',
  'from_user',
  'from_user_name',
  'text' 
  ) VALUES (
  {$user_data[$i]["id"]},
  {$user_data[$i]["created_at"]},
  {$user_data[$i]["from_user_id"]},
  {$user_data[$i]["profile_image"]},
  {$user_data[$i]["from_user"]},
  {$user_data[$i]["from_user_name"]},
  {$user_data[$i]["text"]}
)"

        for($i=0;$i<count($user_data);$i++){
            mysqli_query($mysqli,$insert_tweets);
        }

$user_data is a multi-dimensional array, the first level of which is numeric, the subsequent level is associative.

Also, what would be the best way to "database prepare"/sanitize the associative array variables prior to insertion? I don't anticipate any malicious data but it is always possible.

Yes it will work if you fix the syntax error, missing ; at the end of the SQL and move the sql string inside your for loop. string after )" — Michael Berkowski
– Michael Berkowski, Commented Jul 27, 2012 at 2:46

Rajan Rawal · Accepted Answer · 2012-07-27 02:47:14Z

3

        for($i=0;$i<count($user_data);$i++){
            $insert_tweets = "INSERT INTO tweets ('id','created_at','from_user_id','profile_image','from_user','from_user_name','text') VALUES ({$user_data[$i]["id"]},{$user_data[$i]["created_at"]},{$user_data[$i]["from_user_id"]},{$user_data[$i]["profile_image"]},{$user_data[$i]["from_user"]},{$user_data[$i]["from_user_name"]},{$user_data[$i]["text"]})";
            mysqli_query($mysqli,$insert_tweets);
        }

This should work

answered Jul 27, 2012 at 2:47

Rajan Rawal

6,3236 gold badges44 silver badges62 bronze badges

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

2 Comments

Chad Over a year ago

Oh gees, what a stupid error. Thanks for catching that. In short, the SQL statement looks okay though?

Rajan Rawal Over a year ago

Hey Try this and tell me what happens.

Ayush · Accepted Answer · 2012-07-27 02:51:31Z

3

Yes, it will work, but the best way to do this would be to use PDO.

You can create nameless parameters in your prepare statement and then just pass in a array to bind values to those params.

$data = array('val1', 'val2');
$query = $db->prepare("INSERT INTO table (col1, col2) VALUES (? , ?)");
$query->execute($data);

PDO will escape the input values for you.

Here's a tutorial on PDO to get you started http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

answered Jul 27, 2012 at 2:51

Ayush

42.5k52 gold badges169 silver badges243 bronze badges

Comments

BExDeath · Accepted Answer · 2012-07-27 03:12:56Z

1

Here is my suggestion on sanitizing your array:

What i do is create a basic function for sanitizing data:

function array_sanitize(&$item){
    $item = mysql_real_escape_string($item);
}

Then you can use the array_walk() to sanitize your array with your new function. (php manual refrence)

and sanitize by passing in your array like this:

array_walk($user_data, 'array_sanitize');

answered Jul 27, 2012 at 3:12

BExDeath

1045 bronze badges

Collectives™ on Stack Overflow

Inserting array values into MySQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related