Insertion of associative array into MySQL Database in php

Question

I have an array called $output which contains:

Array
(
    [0] => Array
        (
            [0] => J Arora
            [1] =>  India
        )

    [1] => Array
        (
            [0] => J Ramuj
            [2] =>  Russia
        )

    [2] => Array
        (
            [1] =>  KJ Tris
            [2] =>  Germany
        )

)

How can i insert these data into mysql database table like these

---------------
name   | country
-------|---------
J Arora|India
-------|--------
J Ramuj|Russia
-------|--------
KJ Tris|Germany
-------|--------

I am not able to fetch these values separately from given array as their index are not in sequence.

simply you can use foreach loop to insert the same ? do want the insert query ? — TIGER
– TIGER, Commented Dec 23, 2016 at 10:54

Tolios · Accepted Answer · 2016-12-23 11:33:58Z

5

Just loop it with a foreach, and use PHP native functions like current() and end() to get your elements. Given that you just have two elements in your array at all times, this should work

foreach ($output as $v) {
    echo current($v); // Name
    echo end($v); // Country
}

Adapt this to build your query and execute it inside the loop. Inside the loop you could do

foreach ($output as $v) {
    $sql = "INSERT INTO table (`name`, `country`) VALUES ('".current($v)."', '".end($v)."')";
    // TODO: Execute query
}

You should also note that any query using variables should be using prepared statements with placeholders. Both MySQLi and PDO supports this.

Using prepared statements

Build the query before, and execute it as you loop it with the appropriate variables bound.

Example with PDO:

$stmt = $pdo->prepare("INSERT INTO table (`name`, `country`) VALUES (:name, :country)");
foreach ($output as $v) {
    $stmt->execute(array("name" => current($v), "country" => end($v));
}

Example with MySQLi:

$stmt = $mysqli->prepare("INSERT INTO table (`name`, `country`) VALUES (?, ?)");
foreach ($output as $v) {
    $name = current($v);
    $country = end($v);
    $stmt->bind_param("ss", $name, $country);
    $stmt->execute();
}

edited Dec 23, 2016 at 11:33

answered Dec 23, 2016 at 10:58

Tolios

2193 silver badges12 bronze badges

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

3 Comments

GKumar Over a year ago

Thank you. I was not aware of these functions.

Tolios Over a year ago

The best approach is of course to use prepared statements - we're dealing with variables here. Updated my answer to reflect that with examples for both MySQLi and PDO.

GKumar Over a year ago

Yes. I have used PS only.

Atul Sharma · Accepted Answer · 2016-12-23 10:57:59Z

0

$sql = "INSERT INTO tablename(name,country) VALUES";

for($i=0;$i<sizeof($myarray);$i++){
  $sql.="(".$myarray[0].",".$myarray[1].")";
}

now execute $sql

answered Dec 23, 2016 at 10:57

Atul Sharma

11k10 gold badges42 silver badges69 bronze badges

Comments

Manoj Sharma · Accepted Answer · 2016-12-23 11:00:42Z

0

for ($a=0; $a<count($array); $i++) {
    $dataArray=array_values($array[$i]);
    for ($j=0; $j<count($dataArray); $j++) {

        $name=$dataArray[0];
        $country=$dataArray[1];
        $insert_query="insert into tablename set name='$name', country='$country'";
        $res_query=mysql_query($insert_query);
    }
}

Hope this will works for you.

answered Dec 23, 2016 at 11:00

Manoj Sharma

1,4552 gold badges13 silver badges20 bronze badges

Comments

M A SIDDIQUI · Accepted Answer · 2016-12-23 11:05:03Z

0

Let's call your array user_details

foreach($user_detail as $single_user){
       $i = 0;
       foreach($single_user as $user){
           if($i==0){
             $name = $user;
           } else {
            $country = $user;
           } $i++;
       }
     $sql_query = INSERT into 'Table'($name, $country);
     //Execute this query as per mysqli or or mysql

}

//Considering that first value is always the name and 2nd values is always a country name

edited Dec 23, 2016 at 11:05

answered Dec 23, 2016 at 10:57

M A SIDDIQUI

2,2251 gold badge21 silver badges24 bronze badges

2 Comments

Hanky Panky Over a year ago

I am not able to fetch these values separately from given array as their index are not in sequence.

GKumar Over a year ago

index of arrays are not in sequence. this will not work.

TIGER · Accepted Answer · 2016-12-23 11:06:34Z

0

Might be this can help. Its not tested.

$query = "INSERT into 'your table name' ('name', 'country') VALUES ";
foreach($yourArray as $data) {
    foreach($data as $d) {
       $query .= "('.$d[0].','.$d[1]),";
   }
}

$query = substr($query, 0, -1); // To remove last comma from query string

Then you can execute this query.

edited Dec 23, 2016 at 11:06

answered Dec 23, 2016 at 11:01

TIGER

2,9055 gold badges38 silver badges46 bronze badges

Collectives™ on Stack Overflow

Insertion of associative array into MySQL Database in php

5 Answers 5

Using prepared statements

3 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Using prepared statements

3 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related