php INSERT data to sql in for loop

Question

I want to create a mysql insert statement in a for-loop. I'm looking for to insert multiple records at a time.

if ($_SERVER["REQUEST_METHOD"] == "POST") {
$label =htmlspecialchars( $_POST["label"]);
$splitLabel = explode(" ", $label);//split the label to a array
}
//.....insert another data, getting the $last_id here

$sql = $result = "";
for ($i =0; $i< count($splitLabel); $i++){
    if ($i < count($splitLabel)){
        $sql .= "INSERT INTO label (item_id, label)
        VALUES ('".$last_id."', '".$splitLabel[$i]."');";
    }else{
        $sql .= "INSERT INTO label (item_id, label)
        VALUES ('".$last_id."', '".$splitLabel[$i]."')";
    }
}
$result = mysqli_query($conn, $sql);

i got a error about

 check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO label (item_id, label)
    VALUES ('13', 'tin');INSERT INTO label (' at line 2

the label tabel:

Field      Type           null
item_id    int(11)         NO
label      varchar(50)     NO

i cannot find the mistake, please help me to find it..

mysqli_query() executes _one_ statement. But e.g. `INSERT ... ; INSERT ...;` are two statements. see http://docs.php.net/manual/en/mysqli.quickstart.multiple-statement.php | But exactly why do you need multiple statements, esp. when they all hit the same table? — VolkerK, Feb 29 '16 at 06:35
Use [mysqli_multi_query](http://php.net/manual/en/mysqli.multi-query.php) to execute multiple query — Saty, Feb 29 '16 at 06:36
In your case, only if condition will execute . Why did you wrote if and else?? — Ravi Hirani, Feb 29 '16 at 06:37
i want to Insert Multiple Records Into MySQL.... in the for loop — king yau, Feb 29 '16 at 06:39
Have a look this link once http://www.w3schools.com/php/php_mysql_insert_multiple.asp — Kailas, Feb 29 '16 at 06:39

score 4 · Answer 1 · answered Feb 29 '16 at 06:43

mysqli_query() excutes exactly one statement, but you're sending multiple statements. You could use mysqli_multi_query(), but ....

better use a prepared statement + parameters for that.
Something like e.g

if ( isset($_POST["label"]) ) {
    $stmt = $conn->prepare('INSERT INTO label (item_id, label) VALUES (?,?)')
    if ( !$stmt ) {
        someErrorHandler( $conn->error );
    }
    else if ( !$stmt->bind_param('ss', $last_id, $label) ) {
        someErrorHandler( $stmt->error );
    }
    else {
        // I have no idea where this $last_id comes from ....
        foreach( explode(' ', $_POST["label"]) as $label ) {
            if ( !$stmt->execute() ) {
              someErrorHandler( $stmt->error );
            }
        }
    }
}

score 2 · Answer 2 · answered Feb 29 '16 at 06:44

You just need to use mysqli_multi_query() for multiple queries.

$sql = "";
$result = "";

for ($i =0; $i< count($splitLabel); $i++){
    if ($i < count($splitLabel)){
        $sql .= " INSERT INTO label (item_id, label)
        VALUES ('".$last_id."', '".$splitLabel[$i]."');";
    }else{
        $sql .= " INSERT INTO label (item_id, label)
        VALUES ('".$last_id."', '".$splitLabel[$i]."');";
    }
}
$result = mysqli_multi_query($conn, $sql);

You can not use multiple INSERT into single mysqli_query for this action, you can use mysqli_multi_query() for executing multiple queries.

score 1 · Accepted Answer · answered Feb 29 '16 at 06:59

For multiple query execution you can use mysqli_multi_query()

For multi insertion to a table with single query is like this multi insert:-

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

So you can try like

$sqlInsert   = '';
$countOfData = count($splitLabel);
for ($i = 0; $i < $countOfData; $i++){
    $sqlInsert .= "('{$last_id}', '{$splitLabel[$i]}'),";
}
$sqlInsert = rtrim($sqlInsert, ',');//remove the extra comma
if ($sqlInsert) {
    $sql = "INSERT INTO label (item_id, label) VALUES {$sqlInsert} ;";
    $result = mysqli_query($conn, $sql);
}

score 0 · Answer 4 · answered Feb 29 '16 at 06:40

Try this code

if ($_SERVER["REQUEST_METHOD"] == "POST") {
$label =htmlspecialchars( $_POST["label"]);
$splitLabel = explode(" ", $label);//split the label to a array
}
//.....insert another data, getting the $last_id here

$sql = $result = "";
for ($i =0; $i< count($splitLabel); $i++){
if ($i < count($splitLabel)){
    $sql .= "INSERT INTO label (item_id, label) VALUES ('$last_id', '$splitLabel[$i]')";
}else{
    $sql .= "INSERT INTO label (item_id, label)
    VALUES ('$last_id', '$splitLabel[$i]')";
}
}

score -1 · Answer 5 · answered Feb 29 '16 at 06:38

-1

    $sql .= "INSERT INTO label (item_id, label)
    VALUES ('".$last_id."', '".$splitLabel[$i]."');";

You missed ";"

answered Feb 29 '16 at 06:38

Sardor Dushamov

1,665
3
17
44

Try with ";", then you wil know . – Sardor Dushamov Feb 29 '16 at 06:45
1

@kingyau always finish your statements with ; - it's syntax delimiter, there's no other way... don't forget, you're joining multiple strings with for loop. try to echo your $sql before execution to see what exactly goes into query process. btw, like others suggested, use mysqli_multi_query() to execute this kind queries or consider using PDO->query (witch allows multi-queries) and also you don't need multiple queries when inserting into same table; you can insert thousands of rows into same table with just one INSERT command (see documentation). – Wh1T3h4Ck5 Feb 29 '16 at 06:53
@Wh1T3h4Ck5 can you suggest some documentation for this problem? – king yau Feb 29 '16 at 06:57
@kingyau The best resources for all php/mysql developers are [php.net](http://www.php.net) and [mysql.com](http://www.mysql.com). Your problem could be solved using [this link](http://php.net/manual/en/mysqli.multi-query.php) (most likely). generally, there you can find all you need to learn php and mysql properly. – Wh1T3h4Ck5 Mar 01 '16 at 10:24

JanLeeYu · Answer 6 · 2016-02-29T06:48:11.817

-2

Try this one.

   $sql = $result = "INSERT INTO label (item_id, label) VALUES ";
   for ($i =0; $i< count($splitLabel); $i++){    
        $sql .= "('".$last_id."', '".$splitLabel[$i]."')";
        if ($i < count($splitLabel) - 1){
            sql .= ",";
        }
    }

edited Feb 29 '16 at 06:48

answered Feb 29 '16 at 06:40

JanLeeYu

981
2
9
24

2

He want to insert multiple values at time – Kailas Feb 29 '16 at 06:44
he is using mysqli. can you prepare on sample for mysqli multi insert. so you get +ve vote. – Kailas Feb 29 '16 at 06:59
1

While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. - [From review](https://stackoverflow.com/review/low-quality-posts/11444821) – Ferrybig Feb 29 '16 at 08:10

php INSERT data to sql in for loop

6 Answers6