mysql insert inside foreach is not working?

Question

I'm trying to figure out why this MYSQL INSERT inside the foreach doesn't insert anything into mysql database!

   // Parse the form data and add inventory item to the system
    if (isset($_POST['g_name'])) {


        $g_name =$_POST['g_name'];
        $numbers = $_POST['numbers'];

        $comma_separated = explode(", ", $numbers);


        foreach($comma_separated as $seperate){

        $sql .="INSERT INTO groups(`g_name`, `numbers`) VALUES ('$g_name','$seperate')";

        }

$query = mysqli_query($db_conx, $sql);
        header("location: groups.php"); 
        exit();
    }

if I change the $sql .= to $sql = it inserts only one value in the MYSQL database.

the value of $numbers is like this: 1, 2, 3, 4, 5

could someone please advise on this issue?

any help would be appreciated.

Where are you executing the query? From the code you're showing it is just being built. — Script47, Sep 04 '15 at 21:57
Show the querying code as well. I suspect multi-querying isn't allowed by the extension you're using. — DeDee, Sep 04 '15 at 21:57
If you want to do multiple queries at once, you have to use `mysqli_multi_query()`. And you have to put `;` between the queries. — Barmar, Sep 04 '15 at 22:03
First of all, you should be escaping your inputs. Since there is multiple queries, PDO will be especially suited. You could also do something like `INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);`, but it will be a bit complicated with a prepared statement. — user1032531, Sep 04 '15 at 22:03

score 4 · Accepted Answer · edited May 23 '17 at 11:52

4

Change your loop so you execute the query each time:

foreach($comma_separated as $seperate){

    $sql ="INSERT INTO groups(`g_name`, `numbers`) VALUES ('$g_name','$seperate')";
    $query = mysqli_query($db_conx, $sql);
}

You should keep in mind that your script is at risk for SQL Injection Attacks. Learn about prepared statements.

edited May 23 '17 at 11:52

Community

1
1

answered Sep 04 '15 at 21:59

Jay Blanchard

34,243
16
77
119

1

This old answer is insecure/unstable and should not be used. Prepared statement(s) should be used instead. (and we have many duplicates on Stack Overflow that demonstrate this technique) This page should probably be closed and deleted instead of improved due to redundancy. – mickmackusa Aug 11 '21 at 01:53

score 4 · Answer 2 · edited Jun 20 '20 at 09:12

4

Rather than trying to execute multiple queries, you can use this syntax for inserting multiple records with one query.

$sql = "INSERT INTO groups(`g_name`, `numbers`) VALUES";
$comma = '';

foreach($comma_separated as $seperate){
    $sql .="$comma ('$g_name','$seperate')";
    $comma = ',';
}

$query = mysqli_query($db_conx, $sql);

From the MySQL documentation:

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 04 '15 at 22:01

Don't Panic

41,125
10
61
80

This old answer is insecure/unstable and should not be used. Prepared statement(s) should be used instead. (and we have many duplicates on Stack Overflow that demonstrate this technique) This page should probably be closed and deleted instead of improved due to redundancy. – mickmackusa Aug 11 '21 at 01:52

score 1 · Answer 3 · answered Sep 04 '15 at 22:00

1

You need to execute the query inside the loop for it to insert more than once.

<?php
foreach($comma_separated as $seperate) {
    $sql = "INSERT INTO groups(`g_name`, `numbers`) VALUES ('$g_name','$seperate')";

    $query = mysqli_query($db_conx, $sql);
}
?>

You should also consider using prepared statements.

answered Sep 04 '15 at 22:00

Script47

14,230
4
45
66

This old answer is insecure/unstable and should not be used. Prepared statement(s) should be used instead. (and we have many duplicates on Stack Overflow that demonstrate this technique) This page should probably be closed and deleted instead of improved due to redundancy. – mickmackusa Aug 11 '21 at 01:52

mysql insert inside foreach is not working?

3 Answers3