Insert arrays elements into database fields MYSQL

Question

I have a database called 'customers' and five arrays called name, address, city, cap, country. In my database i want to create 10 records containing this infos about every customer. With my code all the data is isert into database, but first are inserted all the names, the all the addresses and so on, so i got 50 records instead of 10. How can i fix it?

    <?php

$name = array('Alfreds Futterkiste','Ana Trujillo Emparedados','Antonio Moreno Taqueria','Around the Horn','Berglunds snabbkop','Blauer See Delikatessen','Blondel pere et fils ','Bolido Comidas preparadas','Bon app','Bottom-Dollar Marketse');

$address = array('Obere Str. 57','Avda. de la Constitucion 2222','Mataderos 2312','120 Hanover Sq.','Berguvsvagen 8','Forsterstr. 57','24, place Kleber','C/ Araquil, 67','12, rue des Bouchers','23 Tsawassen Blvd');

$city = array('Berlin','Mexico D.F.','Mexico D.F.','London','Lulea','Mannheim','Strasbourg','Madrid','Marseille','Tsawassen');

$cap = array('12209','05021','05023','WA1 1DP','S-958 22','68306','67000','28023','13008','T2F 8M4');

$country = array('Germany','Mexico','Mexico','UK','Sweden','Germany','France','Spain','France','Canada'); 

$conn= mysqli_connect('127.0.0.1','root','','preesame') or die("Connection failed: " . $conn->connect_error);

foreach ($name as $key=>$value)
{
$namess = mysqli_real_escape_string($conn,$value);
$addressess = mysqli_real_escape_string($conn,$addresses);
$citiess = mysqli_real_escape_string($conn,$cities);
$capss = mysqli_real_escape_string($conn,$caps);
$countriess = mysqli_real_escape_string($conn,$countries);
$insert = mysqli_query($conn,"INSERT INTO customers (id,name,address,city,cap,country) VALUES ('',$namess[$key],$addressess[$key],$citiess[$key],$capss[$key],$countriess[$key])");
}

$conn->close();
?>

Marc B · Accepted Answer · 2014-10-30 15:33:10Z

1

1) Connect to the database ONCE. You do NOT have to connect each time in each of your loops

2) Use a SINGLE loop, and a SINGLE query:

foreach ($name as $key => $value) {
   $sql = "INSERT ... (id, name, address, foo, bar, baz) VALUES ('', $value, $addresses[$key], $foo[$key], $bar[$key], etc...)";
   ... run query here
}

3) Read up and learn about sql injection attacks because you're wide open for getting your server pwn3d.

answered Oct 30, 2014 at 15:33

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

2 Comments

Marc B Over a year ago

again. connect to the database ONCE. you're connecting every single time you run the loop. that's just crazy. and [key] is NOT the same as [$key]

Antoder Over a year ago

Fixed. I still get a lot of 'Undefined variable' and 'Uninitialized string offset: '

vaso123 · Accepted Answer · 2014-10-30 15:40:49Z

Because you are looping on each array, and insert all of them.

Try this:

$name = array('Alfreds Futterkiste', 'Ana Trujillo Emparedados', 'Antonio Moreno Taqueria', 'Around the Horn', 'Berglunds snabbkop', 'Blauer See Delikatessen', 'Blondel pere et fils ', 'Bolido Comidas preparadas', 'Bon app', 'Bottom-Dollar Marketse');
$address = array('Obere Str. 57', 'Avda. de la Constitucion 2222', 'Mataderos 2312', '120 Hanover Sq.', 'Berguvsvagen 8', 'Forsterstr. 57', '24, place Kleber', 'C/ Araquil, 67', '12, rue des Bouchers', '23 Tsawassen Blvd');
$city = array('Berlin', 'Mexico D.F.', 'Mexico D.F.', 'London', 'Lulea', 'Mannheim', 'Strasbourg', 'Madrid', 'Marseille', 'Tsawassen');
$cap = array('12209', '05021', '05023', 'WA1 1DP', 'S-958 22', '68306', '67000', '28023', '13008', 'T2F 8M4');
$country = array('Germany', 'Mexico', 'Mexico', 'UK', 'Sweden', 'Germany', 'France', 'Spain', 'France', 'Canada');

$conn = mysqli_connect('127.0.0.1', 'root', '', 'preesame') or die("Connection failed: " . $conn->connect_error);

$i = 0;
foreach ($name as $names) {
    $sql = "INSERT INTO customers (name,address,city,cap,country) "
            . "VALUES ('".mysqli_real_escape_string($link, $names[$i])."',"
            . "'".mysqli_real_escape_string($link, $address[$i])."',"
            . "'".mysqli_real_escape_string($link, $city[$i])."',"
            . "'".mysqli_real_escape_string($link, $cap[$i])."',"
            . "'".mysqli_real_escape_string($link, $country[$i])."')";
    $insert = mysqli_query($conn, $sql);
    $i++;
}
$conn->close();

And if i can suggesst you a tip: organize your data like this in a multidimensional array like this:

$persons = array(
    array('name' => 'Alfreds Futterkiste', 'address' => 'Obere Str. 57', 'city' => 'Berlin', 'cap' => 12209, 'country' => 'Germany'),
    array('name' => 'Ana Trujillo Emparedados', 'address' => 'Avda. de la Constitucion 2222', 'city' => 'Mexico D.F.', 'cap' => 05021, 'country' => 'Mexico'),
   //more person here...
);

Easier to maintaine, and manipulate.

benjrb · Accepted Answer · 2014-10-30 15:50:49Z

0

You should only have one loop. Write a for loop and use the index/counter to access the values in your arrays. Something like this:

for ($i = 0, $i < 10, $i++) {
//insert using index $name[$i], $address[$i] etc.
}

edited Oct 30, 2014 at 15:50

answered Oct 30, 2014 at 15:41

benjrb

8361 gold badge6 silver badges14 bronze badges

Collectives™ on Stack Overflow

Insert arrays elements into database fields 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