Php insert into sql from array

Question

I have 2 array and I want to add them into my sql table with one button. I dont know using arrays with sql. I read a lot of page but couldnt find an easy way.

$name=array("Jack", "John", "Fiona");

$country=array("London", "Greece", "Japan"); 

$entry =' went to school';

Sql table :

**id** **|** **name**  **|**    **entry**            **|**      **country**

**1** Jack **|** Jack went to school   **|** London

**2** John  **|**  John went to school    **|** Greece

**3** Fiona  **|**  Fiona went to school  **|** Japan

I tried something like this but it didn't work. Regards

$sql = mysql_query("INSERT INTO bilgi (name,entry,country) VALUES ('$name[]','$name[]&$entry','$country[]'");

Nana Partykar · Accepted Answer · 2015-09-28 10:04:57Z

1

$name=array("Jack", "John", "Fiona");

$country=array("London", "Greece", "Japan"); 

$entry =' went to school';

$SizeOfName=sizeof($name);

for($i=0;$i<$SizeOfName;$i++)
{
    $Name=$name[$i];
    $Country=$country[$i];
    $Entry=$Name$entry;

    $sql = mysql_query("INSERT INTO bilgi (name,entry,country) VALUES ('$Name','$Entry','$Country')";
}

edited Sep 28, 2015 at 10:04

answered Sep 28, 2015 at 9:48

Nana Partykar

10.5k10 gold badges50 silver badges79 bronze badges

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

3 Comments

Naruto Over a year ago

Whats going to happen if $country doesn't have the same amount of values as $name?

PeeHaa Over a year ago

sizeof?? for instead of foreach?

Nana Partykar Over a year ago

Yeah. I also thought that point. But, OP requirement was like this only, as i saw in example given by him Mr @Naruto

Community · Accepted Answer · 2017-05-23 10:27:21Z

You should really upgrade to either PDO or mysqli. The mysql API has been deprecated for a while now and will be removed in the next release of PHP (7).

To answer your actual question imo the first step should be to make the data sane, i.e.:

$data = [
    [
        'name'    => 'Jack',
        'country' => 'London',
    ],
    [
        'name'    => 'John',
        'country' => 'Greece',
    ],
    [
        'name'    => 'Fiona',
        'country' => 'Japan',
    ],
];

This makes it much easier to handle and maintain the data.

Next you simply need to loop through the single array:

$entry = ' went to school';

foreach ($data as $item) {
    $sql = mysql_query("INSERT INTO bilgi (name,entry,country) VALUES ('$item[name]','$item[name]$entry','$item[country]'");
}

However this still uses the old API and doesn't prevent sql vulnerabilities. What you should do (after making a database connection proper) is:

$entry = 'went to school';

$stmt = $pdo->prepare('INSERT INTO bilgi (name,entry,country) VALUES (:name, :entry, :country');

foreach ($data as $item) {
    $stmt->execute([
        'name' => $item['name'],
        'entry' => $entry,
        'country' => $item['country'],
    ]);
}

I have also removed name from the entry field, because that is a bad way of duplication in your database. If ever the name changes you are stuck with the name in the entry column.

This uses a non deprecated API. Prevents SQL injection vulnerabilities and is just more sane and maintainable.

Tharanga · Accepted Answer · 2015-09-28 10:03:05Z

0

<?php

$name=array("Jack", "John", "Fiona");

$country=array("London", "Greece", "Japan"); 

$entry =' went to school';

for($i=0 ; $i<count($name);$i++){

    $sql = mysql_query("INSERT INTO bilgi (name,entry,country) VALUES ('".$name[$i]."','".$name[$i].$entry."','".$country[$i]."'");
}

answered Sep 28, 2015 at 10:03

Tharanga

816 bronze badges

Comments

Linh · Accepted Answer · 2015-09-28 10:08:28Z

0

You can insert in one SQL as:

$name = array("Jack", "John", "Fiona");
$country = array("London", "Greece", "Japan"); 
$entry =' went to school';

$values;
for ($i = 0; $i < count($name); $i++) {
    $values[] = "('$name[$i]', '$name[$i]$entry', '$country[$i]')";
}

$sql = 'INSERT INTO bilgi (name, entry, country) VALUES ';
for ($i = 0; $i < count($values); $i++) {
    $sql .= $values[$i];
    if($i < (count($values) - 1)) $sql .= ", ";
}

$result = mysql_query($sql);

Please take care on your arrays before insert into database. $name and $country must be equal in number of elements.

answered Sep 28, 2015 at 10:08

Linh

1214 bronze badges

1 Comment

nickelaj xxx Over a year ago

Thanks a lot. I wil notice them.

Ninju · Accepted Answer · 2015-09-28 10:24:56Z

0

You can use this

for ($i = 0; $i < count($name); $i++) {
   $entryVal = $name[$i].$entry;
   $sql = mysql_query("INSERT INTO bilgi (name,entry,country) VALUES  ('".$name[$i]."','".$entryVal."','".$country[$i]."'");
}

edited Sep 28, 2015 at 10:24

answered Sep 28, 2015 at 9:51

Ninju

2,5322 gold badges16 silver badges21 bronze badges

1 Comment

nickelaj xxx Over a year ago

Thanks, I tried but it did like name:J, name:A, name.J . Got only first letter. And i think 3 letter for 3 arrays.

Monty Khanna · Accepted Answer · 2015-09-28 11:20:28Z

0

This is dynamic solution for your question :

<?php
$name=array("Jack", "John", "Fiona","Mack","Raja","Bohemia");
$country=array("London", "Greece", "Japan"); 
$entry ='went to school';

$bigcount = max(count($name),count($country));

for($i=0;$i<$bigcount;$i++)
{
    $finalValues .= "('$name[$i]','$country[$i]','$entry'),";
}
$final = substr($finalValues,0,-1);
$query = "INSERT INTO bilgi (name,entry,country) VALUES $final";
$sql = mysql_query($query);
?>

answered Sep 28, 2015 at 11:20

Monty Khanna

1,1207 silver badges15 bronze badges

Collectives™ on Stack Overflow

Php insert into sql from array

6 Answers 6

3 Comments

Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related