将数组元素插入数据库字段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();
?>

Answer 1

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.