Loop over an insert Query in PHP

Question

I am having trouble with insert the same value N time In SQL via PHP script. I have this vslue?

Table Info  column (ID,Name, LastName,)
Valus ('',Alain,Alian);

Whata i want to do is insert this value in the same table 10 Times Using a While loop for E.G Or something like that any idea?.

<?php
    $i=1;
    While ($i<= 5)      
    {
       $sql="INSERT INTO arivage
          (ID_Ship,Date_ariv,Date_achat,prov_id,Sph,cyl,Prod_type,Pord_color)
          VALUES
          ('','$date','$date1','$prov_id','$sph','$cyl','$Prod_type','$Pord_color')";
       $i++;
    }
?>

score 5 · Accepted Answer · answered Apr 19 '12 at 10:23

Have you some errors?

what you need is feasible

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
  die('Could not connect: ' . mysql_error());
}

for($i = 0; $i<10; $i++) {
    $sql="INSERT INTO arivage (ID_Ship,Date_ariv,Date_achat,prov_id,Sph,cyl,Prod_type,Pord_color) VALUES ('','$date','$date1','$prov_id','$sph','$cyl','$Prod_type','$Pord_color')";
    $result = mysql_query($sql);
    if (!$result) {
     die('Invalid query: ' . mysql_error());
    }
}

score 0 · Answer 2 · answered Apr 19 '12 at 11:22

I assume you have a connection to the database (by using mysql_connect() + mysql_select_db()), and also execute your query after composing it (by mysql_query($sql)).

I need to know what is the datatype of column ID_Ship? Is this PRIMARY KEY and auto_increment? If yes, then you need to change your query from:

INSERT INTO arivage (ID_Ship,Date_ariv,Date_achat,prov_id,Sph,cyl,Prod_type,Pord_color) 
VALUES ('','$date','$date1','$prov_id','$sph','$cyl','$Prod_type','$Pord_color')

to:

INSERT INTO arivage (Date_ariv,Date_achat,prov_id,Sph,cyl,Prod_type,Pord_color) 
VALUES ('$date','$date1','$prov_id','$sph','$cyl','$Prod_type','$Pord_color');

Note: ID_ship is removed, and '' is also removed.

This removal is to prevent inserting the same primary key for 2nd, 3rd, etc records. Remember that primary key must be unique. Not specifying it will let MySQL inserts the generated value (because it is auto_increment).

score 0 · Answer 3 · answered Sep 19 '20 at 20:47

0

if you need to bind the params here is another way.

public function insert($rows) 
    {
        $setValues = null;
    
        foreach($rows as $column => $value) 
        {
          $setValues .= "{$column}=:{$column},";
        }

        $setValues = rtrim($setValues, ',');

        $query = "INSERT INTO {$this->_table} SET {$setValues}";

        $stmt = $this->_conn->prepare($query);

        foreach($rows as $col => $val) 
        {
            $stmt->bindParam(":{$col}", $val);
        }
      
        $stmt->execute();
    }

answered Sep 19 '20 at 20:47

jerryurenaa

3,863
1
27
17

why it need loops in chunks? – Jatinder Jun 10 '21 at 16:01
Sometimes you need to add multiple values at once. Plus you also need to bind your params. The loops prepare and build the query with the given values and bind them before executing the query. – jerryurenaa Jun 11 '21 at 17:30

score -1 · Answer 4 · answered Apr 19 '12 at 10:22

-1

Try This.. This might work

$sql= "INSERT INTO TABLE_NAME(field1,field2) VALUES( ";
    for($i=0;$i<10;$i++)
    {
       $sql.="('value1', 'value2'), "
    }
    $sql.=" )";

answered Apr 19 '12 at 10:22

Bibin Velayudhan

3,043
1
22
33

I think it should be just like values(),(),() . . . etc. – Suleman Ahmad Apr 19 '12 at 10:29
1

The last iteration will add comma and the query will be ended with ('value1', 'value2'), ). I think this will produce error. – Muhammad Alvin Apr 19 '12 at 11:25

score -1 · Answer 5 · answered Apr 19 '12 at 10:24

You should Add

mysql_query($sql);

to your code so that the query is executed between the cycles.

$i=1;
While ($i<= 5)      
{
 $sql="INSERT INTO arivage
 (ID_Ship,Date_ariv,Date_achat,prov_id,Sph,cyl,Prod_type,Pord_color)
 VALUES('','$date','$date1','$prov_id','$sph','$cyl','$Prod_type','$Pord_color')";

 mysql_query($sql);

    $i++;
}

Loop over an insert Query in PHP

5 Answers5

Linked

Related