Insert into table from Array PHP

Question

I have four Array consisting of some values. I want to insert Array with same index in same row. Eg:

$product = [Facebook, Twitter]; 
$sub_product = [Likes, Boost]; 
$plan = [10k, 20k];
$months = [3,6];

I want table to be some thing like this

+----+----------+-------------+------+--------+
| id | product  | sub_product | plan | months |
+----+----------+-------------+------+--------+
| 1  | Facebook | Likes       | 10k  | 3      |
+----+----------+-------------+------+--------+
| 2  | Twitter  | Boost       | 20k  | 6      |
+----+----------+-------------+------+--------+

What will be the best way to achieve this such that Index of each array falls in same row? I want to insert in HTML table

you can add a forech loop and than insert the records one by one. — Priyank
– Priyank, Commented Jan 5, 2016 at 4:47
you want to insert in database or want to show as a table in html? — user5570620
– user5570620, Commented Jan 5, 2016 at 4:59

Vegeta · Accepted Answer · 2016-01-05 05:25:24Z

6

If you want to execute mysql_query in single shot then

$product = [Facebook, Twitter]; 
$sub_product = [Likes, Boost]; 
$plan = [10k, 20k];
$months = [3,6]; 
$query = 'insert into TABLE (product,sub_product,plan,months) values';
foreach( $product as $index => $col ){
    $query .= "( '".$product[$index]."', '".$sub_product[$index]."', '".$plan[$index]."', ".$months[$index]." ),";
}

$query = rtrim( $query, ',');
mysqli_query(mysql_query);

This will be faster than executing multiple mysql_query function in a loop.

edited Jan 5, 2016 at 5:25

answered Jan 5, 2016 at 4:55

Vegeta

1,3179 silver badges17 bronze badges

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

7 Comments

saurabh kamble Over a year ago

you need add validation for all elements present in that foreach if they exist

Vegeta Over a year ago

no need. @Nehil Mistry mentioned that the indexes are same for all array. Thanks

saurabh kamble Over a year ago

if anyone of the element is missing then,what will your code do, error undefined index ?

A J Over a year ago

@Vegeta, It is a good approach to form a query first and then execute it. Good one.

Mohit Aggarwal Over a year ago

Process is optimized one. Just one doubt to clear instead of ltrim it should be rtrim because comma has been added at the right in the query.

|

Priyank · Accepted Answer · 2016-01-05 06:53:12Z

5

Try This Code,

    foreach($product as $key=>$p){
    $data = array(
    'product'=>$p,
    'sub_product'=>$sub_product[$key],
    'plan'=>$plan[$key],
    'months'=>$months[$key]
    );

    //Code to insert this array to Database
    // Create connection
         $conn = mysqli_connect($servername, $username, $password, $dbname);
         $sql = "INSERT INTO `table`('product','sub_product',...) VALUES ($data['product'],$data['sub_product'],...)";
         mysqli_close($conn);

    //OR If you are using Codeigniter,
         $this->db->insert('table',$data);

    }

edited Jan 5, 2016 at 6:53

answered Jan 5, 2016 at 4:50

Priyank

1,00913 silver badges37 bronze badges

2 Comments

Priyank Over a year ago

@saurabhkamble Thanks.

saurabh kamble Over a year ago

can you add the code inserting the $data array into database @priyank57

Amit Shah · Accepted Answer · 2016-01-05 04:52:16Z

0

this code should work,

$product = array("Facebook", "Twitter"); 
$sub_product = array("Likes", "Boost"); 
$plan = array("10k", "20k");
$months = array(3,6); 

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

    $product_name=$product[$i];
    $sub_product_name=$sub_product[$i];
    $plan_name=$plan[$i];
    $month_no=$months[$i];

    echo "insert into table_name (product_name, sub_product_name, plan_name, month_no) values ('$product_name', '$sub_product_name', '$plan_name', '$month_no')";
    echo "<br>";

}

Thanks Amit

answered Jan 5, 2016 at 4:52

Amit Shah

1,3801 gold badge10 silver badges20 bronze badges

Comments

wogsland · Accepted Answer · 2016-01-05 04:53:32Z

0

Assuming all arrays have the same length and $mysqli is a connection to your database,

for ($i=0; $i < count($product); $i++) {
    $query = "INSERT INTO your_table (id, product, sub_product, plan, months)";
    $query .= " VALUES ('$i', '{$product[$i]}', '{$sub_product[$i]}', '{$plan[$i]}', '{$months[$i]}')";
    $mysqli->query($query);
}

answered Jan 5, 2016 at 4:53

wogsland

9,53121 gold badges63 silver badges99 bronze badges

1 Comment

Vegeta Over a year ago

why are you splitting the $query string into two statement ?

AkshayS · Accepted Answer · 2016-01-05 05:03:21Z

Assuming that the size of all the above arrays are same at a given point of time and their indexing is consistent you can use the following code:

    <?php

    $product = array('Facebook', 'Twitter'); 
    $sub_product = array('Likes', 'Boost'); 
    $plan = array('10k', '20k');
    $months = array(3,6); 

/* Connected to a mysql Database */

$i = 0; //initialize a counter

while($i < sizeof($product)) //Run the loop (Twice in this case)
{
//Store the current iteration value in variables
$current_product = $product[$i];
$current_sub_product = $sub_product[$i];
$current_plan = $plan[$i];
$current_months = $months[$i];



    //Prepare the SQL statement
$sql = <<<EOD
        INSERT INTO table_product(product,subproduct,plan,months) 
        VALUES ('$current_product','$current_sub_product','$current_plan',$current_months)  
EOD;

$i++;
echo $sql . "<br />";
}


?>

Amit Rajput · Accepted Answer · 2016-01-05 04:52:38Z

-1

Do on this way simple:

$count = count($product);
$index=0;
while($index<$count){
//do your stuff here i.e. insert query
$sql = "INSERT INTO your_table SET product='".$product[$index]."', sub_product='".$sub_product[$index]."', plan='".$plan[$index]."', months='".$months[$index]."' ";
mysql_query($sql);
$index++;
}

answered Jan 5, 2016 at 4:52

Amit Rajput

2,0591 gold badge13 silver badges27 bronze badges

Collectives™ on Stack Overflow

Insert into table from Array PHP

6 Answers 6

7 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

7 Comments

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related