How to insert array into a MYSQL database?

Question

This is my array for top 10 movies

$data() = Array ( [0] => Array ( [rank] => 1. [rating] => 9.2 [title] => The Shawshank Redemption [year] => 1994 [number_of_votes] => 643,339 ) [1] => Array ( [rank] => 2. [rating] => 9.2 [title] => The Godfather [year] => 1972 [number_of_votes] => 488,694 ) [2] => Array ( [rank] => 3. [rating] => 9.0 [title] => The Godfather: Part II [year] => 1974 [number_of_votes] => 301,665 ) [3] => Array ( [rank] => 4. [rating] => 8.9 [title] => The Good, the Bad and the Ugly [year] => 1966 [number_of_votes] => 202,341 ) [4] => Array ( [rank] => 5. [rating] => 8.9 [title] => Pulp Fiction [year] => 1994 [number_of_votes] => 507,363 ) [5] => Array ( [rank] => 6. [rating] => 8.9 [title] => 12 Angry Men [year] => 1957 [number_of_votes] => 154,104 ) [6] => Array ( [rank] => 7. [rating] => 8.9 [title] => Schindler's List [year] => 1993 [number_of_votes] => 337,187 ) [7] => Array ( [rank] => 8. [rating] => 8.8 [title] => One Flew Over the Cuckoo's Nest [year] => 1975 [number_of_votes] => 265,303 ) [8] => Array ( [rank] => 9. [rating] => 8.8 [title] => The Dark Knight [year] => 2008 [number_of_votes] => 578,207 ) [9] => Array ( [rank] => 10. [rating] => 8.8 [title] => Inception [year] => 2010 [number_of_votes] => 415,584 ) )

Schema

mysql_query("CREATE TABLE TopMovies (rank INT, 
                                     rating VARCHAR(3), 
                                     title VARCHAR(50), 
                                     year INT, 
                                     number_of_votes INT )");

This is my loop

foreach ($data as $movie => $rows) {
    foreach ($rows as $row => $columns) {
       $query = "INSERT INTO TopMovies (rank, rating, title, year, number_of_votes)
          VALUES ({$columns['rank']}, {$columns['rating']}, {$columns['title']},
                  {$columns['year']}, {$columns['number_of_votes']});";                             
    }
    mysql_query($query);        
}

This is what I get, what's wrong?

+------+--------+-------+------+-----------------+
| rank | rating | title | year | number_of_votes |
+------+--------+-------+------+-----------------+
|    6 | 6      | 6     |    6 |               6 |
|    4 | 4      | 4     |    4 |               4 |
|    3 | 3      | 3     |    3 |               3 |
|    2 | 2      | 2     |    2 |               2 |
|    5 | 5      | 5     |    5 |               5 |
|    1 | 1      | 1     |    1 |               1 |
|    3 | 3      | 3     |    3 |               3 |
|    2 | 2      | 2     |    2 |               2 |
|    5 | 5      | 5     |    5 |               5 |
|    4 | 4      | 4     |    4 |               4 |
+------+--------+-------+------+-----------------+

Same thing happen when you remove the single quotes around the key names? — alex
– alex, Commented Sep 15, 2011 at 23:07
@GolezTrol: Because the keys are already strings. Just throwing out suggestions. :) — alex
– alex, Commented Sep 15, 2011 at 23:11

drew010 · Accepted Answer · 2011-09-15 23:09:45Z

2

Given that array of data, I believe this is the loop you need:

foreach ($data as $movie) {
   $query = "INSERT INTO TopMovies (rank, rating, title, year, number_of_votes)
      VALUES ({$movie['rank']}, '{$movie['rating']}', '{$movie['title']}',
              {$movie['year']}, {$movie['number_of_votes']});";
    mysql_query($query);
}

Also take care to mysql_real_escape_string the data that are strings and may be input by users.

answered Sep 15, 2011 at 23:09

drew010

70.3k11 gold badges144 silver badges174 bronze badges

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

5 Comments

patrick Over a year ago

Getting an empty set with yours, thoughts?

drew010 Over a year ago

Try to var_dump($movie) in that loop to see what it looks like and comment mysql_query() for now. Also you can echo $query to see what it looks like. From the array you posted I thought that should work.

patrick Over a year ago

Hmm, both var_dump and echo $query looks good :INSERT INTO TopMovies (rank, rating, title, year, number_of_votes) VALUES (1., '9.2', 'The Shawshank Redemption ', 1994, 643,339);

drew010 Over a year ago

The comma in the number of votes needs to be removed, it is unbalancing your column to value count. It thinks 643 and 339 are 2 different columns.

drew010 Over a year ago

Either just put quotes around it (mysql wont care that its a numeric column enclosed in quotes), or $movie['number_of_votes'] = str_replace(',', '', $movie['number_of_votes']);

Brian Colvin · Accepted Answer · 2011-09-15 23:23:42Z

2

$query = "INSERT INTO TopMovies (rank, rating, title, year, number_of_votes) VALUES "
foreach ($data as $movie => $rows) {
    foreach ($rows as $row => $columns) {
       $query += "({$columns['rank']}, '{$columns['rating']}', '{$columns['title']}',
              {$columns['year']}, {$columns['number_of_votes']}),";                             
    }      
}
#remove last comma add ';'
mysql_query($query);

edited Sep 15, 2011 at 23:23

answered Sep 15, 2011 at 23:17

Brian Colvin

3801 silver badge10 bronze badges

1 Comment

Adam Purdie Over a year ago

$query += should probably be .= :)

Dan Bizdadea · Accepted Answer · 2011-09-15 23:14:18Z

0

you need to put the mysql_query inside the loop with the queries so you execute them. also, you have an array of records. you need only to parse once. you don't need two loops.

so

foreach ($data as $record) {
    $sql = "INSERT INTO .... {$record['rank']}, ...";
    mysql_query($sql);
}

answered Sep 15, 2011 at 23:14

Dan Bizdadea

1,3028 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to insert array into a MYSQL database?

3 Answers 3

5 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Related