CODEWITHSUNDEEP
phpmysqlsqlarraysloops
DexterAnthony
DexterAnthony

Reputation: 45

Insert Multiple rows of mysql using php

How can I insert multiple data to mysql database using php. I tried using for loop but no luck.

//array of input boxes
$id1=array($aa1,$aa2,$aa3,$aa4,$aa5,$aa6,$aa7);
$timeRank1=array($a3,$a6,$a9,$a12,$a15,$a18,$a21);
for ($i = 0; $i < 7; $i++) {
require_once("connection.php");
$a = $id1[$i];
$b = $timeRank1[$i];
$sql = "INSERT INTO results (id,swim_rank)
VALUES ('".$a."','".$b."')";

Upvotes: 1

Views: 11928

Answers (3)

John Conde
John Conde

Reputation: 219814

Just build your query in a loop and then execute it when the loop is compete

require_once("connection.php");
$sql = "INSERT INTO results (id,swim_rank) VALUES ";
for ($i = 0; $i < 7; $i++) {
    $sql .= "('".$id1[$i]."','".$timeRank1[$i]."'),";
}
$sql = rtrim($sql, ',');
// run your query here

You'll also notice I moved your include of your DB connection outside of the loop. No need to repeatedly make that call.

Also, make sure you either escape those values being inserted or use parametized queries to make your inserts to protect against SQL injections.

Upvotes: 7

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

(Not looking to overstep John, nor to gain points)

To elaborate on John Conde's answer: (it is easier to show here in a visual representation, instead of further comments) and as John said "I can't say for sure because I don't know what API you are using..."

Assuming connection.php contains something to the effect of:

<?php
$DB_HOST = "xxx";
$DB_NAME = "xxx";
$DB_USER = "xxx";
$DB_PASS = "xxx";
$con = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($con->connect_errno > 0) {
  die('Connection failed [' . $con->connect_error . ']');
}

Query:

<?php
require_once("connection.php");
$sql = "INSERT INTO results (id,swim_rank) VALUES ";
for ($i = 0; $i < 7; $i++) {
    $sql .= "('".$id1[$i]."','".$timeRank1[$i]."'),";
}
rtrim($sql, ',');
// run your query here

$result = mysqli_query($con, $sql); 
if ( false===$sql ) { 
printf("error: %s\n", mysqli_error($con)); 
}

Upvotes: 1

Yemi Adeoye
Yemi Adeoye

Reputation: 1

According to mysql reference guide, here's how to insert multiple values with a single insert statement.

INSERT INTO people (name,age) VALUES('William',25),('Bart',15),('Mary',12);

i.e assuming we have a table named people that has columns name and age.

Now, to translate this to PHP, a good approach is to create a query string. The following php code creates such string.

   $query_values = " INSERT INTO results (id,swim_rank) VALUES "; // notice space after VALUES

 if (count($id1) == count($timeRank1)) // ensure both arrays have equal number of elements
 {
    for ($i = 0; $i <= (count($id1) - 1); $i++) // remember array index starts from
    {
        $query_values .= "({$id1[$i]},{$timeRank1[$i]})"  ;
        if ($i != (count($id1) - 1)) // you dont want a trailing comma after your last bracket
        {
            $query_values .= ",";
        }
    }
  }

The query produced should now be used to enter your values into your database. I hope you found this helpful. Any other queries lemme know. Cheers

Upvotes: 0

Related Questions