How do I insert an array into a database in php using MySQL?

Question

My code is:

<?php
include("connect.php");
mysql_select_db("cars",$conec);
car = array("BMW","Rolls-Royce","Lamborghini","Mustang");
color = array("red","green","blue","yellow"); 
?>

What I want to do is insert each car into a database and this is what I tried to do but I only get the id increasing becuase it is an auto-increment. This is my insertion code below:

for($i = 0; $i < 4; $i++){
    $res = mysql_query("insert into auto (car,colors) values ('$car[$i]','$color[$i]')");
}

Sorry the cuotes are not the problem, I copied wrong, didn't copy and paste. — user3368897, Mar 19 '14 at 17:12
I recommend using PDO. It's part of PHP by default and is easily portable across database drivers, as well has a more OO-design and is easier to use. With PHP you can insert an array just like that. — Jeroen, Mar 19 '14 at 17:12
Sidenote: Did you forget the `$` for `car = array` and `color = array` to be as `$car = array` and `$color = array`? *"Say it ain't so Joe, say it ain't so."* — Funk Forty Niner, Mar 19 '14 at 17:14
@user3368897 See my answer below, tested and working (you did forget the `$` signs for your variables). Do accept my answer to close the question. Enjoy ;-) — Funk Forty Niner, Mar 19 '14 at 17:30

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Edit

Tested and working

<?php
DEFINE ('DB_USER', 'xxx');
DEFINE ('DB_PASSWORD', 'xxx');  
DEFINE ('DB_HOST', 'xxx');
DEFINE ('DB_NAME', 'xxx');

$mysqli = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) 
OR die("could not connect");

$car = array("BMW","Rolls-Royce","Lamborghini","Mustang");
$color = array("red","green","blue","yellow");

for($i = 0; $i < 4; $i++){
    $res = mysqli_query($mysqli,"insert into auto (car,colors) values ('$car[$i]','$color[$i]')");
}
?>

If this is your actual code that you posted:

You forgot the $ signs for your car and color variables.

 car = array("BMW","Rolls-Royce","Lamborghini","Mustang");
^-- // here
 color = array("red","green","blue","yellow"); 
^-- // and here

<?php
include("connect.php");
mysql_select_db("cars",$conec);
$car = array("BMW","Rolls-Royce","Lamborghini","Mustang");
$color = array("red","green","blue","yellow"); 
?>

score 0 · Answer 2 · answered Mar 19 '14 at 17:26

I think it is generally frowned upon to put PHP arrays in MySQL since they can only be read by PHP. You should run

$car = json_encode($car);
$color = json_encode($color);

Then when you want to pull the info from the database run

$car = json_decode($mysqlcarvarhere);
$color = json_decode($mysqlcolorvarhere);

Also, on your second bit of code, that will insert '$car[$i]' into that database instead of the VALUE of $car['$i'] into the database. To fix this you should change your query to:

$res = mysql_query("insert into auto (car,colors) values ('" . $car[$i] . "','" . $color[$i] ."')");

Also, generic "you should switch to PDO/MySQLi or else you'll get hacked" speech here.

score 0 · Answer 3 · edited May 23 '17 at 12:28

I would recommend you to use Prepared Statement, for all kinds of reasons.

Assuming a connection is already made to the database, here is one way to do it :

<?php
    include("connect.php");

    $cars = array("BMW","Rolls-Royce","Lamborghini","Mustang");
    $colors = array("red","green","blue","yellow"); 

    $db = new mysqli($host, $user, $password, $database);
    $stmt = $db->prepare("INSERT INTO auto(car, color) VALUES(?, ?)");

    // Here I deliberately assume that all car has every color variant
    foreach($cars as $car) {
        foreach($colors as $color) {
            $stmt->bind_param('s', $car);
            $stmt->bind_param('s', $color);

            $stmt->execute();
        }
    }

    $stmt->close();

?>

It's just a rough idea how to solve it with Prepared Statement, you could always wrap it up in a function or a class.

Sidenote: Change all `$stms` to `$stmt` *otherwise*, `execute()` and `close()` won't execute. — Funk Forty Niner, Mar 19 '14 at 17:42
@Fred-ii- Whoops. Staying up late does have it's toll.. Corrected, thanks! — Samuel Adam, Mar 19 '14 at 17:46

How do I insert an array into a database in php using MySQL?

3 Answers3

Edit