CODEWITHSUNDEEP
phpmysql
user7531085
user7531085

Reputation:

Running an SQL query multiple times

I need to run this query 100 times to input data into my MySQL database. ID auto increments. Not concerned about incrementing the table Name column. Just need to fill the database. What is the best way to go about this without having to copy/paste 100 times?

"INSERT INTO `tables`(id, name, assigned_seating, open_seating, position) VALUES ('', 'Table 1', 0, 1, '')";

Upvotes: 0

Views: 10175

Answers (5)

user7531085
user7531085

Reputation:

If anyone sees this in the future, this is the best answer

public function addbatch()
{
  for ($i = 1; $i <= 100; $i++)
  {
    $tableName = "Table " . $i;
    $q = "INSERT INTO `tables`(id, name, cap, assigned_seating, open_seating, position) VALUES ('', '".$tableName."', 10, 0, 1, '')";
    $this->db->query($q);
  }
}

call function once. Make sure to delete when done though!

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31772

All you need is an existing table with at least 100 rows. I will use information_schema.columns as example:

INSERT INTO `tables`(id, name, assigned_seating, open_seating, position)
    SELECT null, 'Table 1', 0, 1, ''
    FROM information_schema.columns
    LIMIT 100;

Demo: http://rextester.com/DMSC23853

Upvotes: 1

Danilo Bustos
Danilo Bustos

Reputation: 1093

try this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `multipleInsert` $$
    CREATE PROCEDURE `multipleInsert`(in n int)
    BEGIN
    DECLARE cont int default 0;
    WHILE cont  < n DO
    INSERT INTO `tables`(id, name, assigned_seating, open_seating, position) VALUES ('', 'Table 1', 0, 1, '');
    set cont  = cont  + 1;
    end while;
    END $$

DELIMITER ;

Call Procedure:

call multipleInsert(100);

Upvotes: 0

user6763587
user6763587

Reputation: 

 $vals='';
 for ($i = 0; $i < 100; $i++) {
    $vals.="('Table 1', 0, 1, ''),";
 }
 $vals=rtrim($vals,',');

 mysqli_query($dbh, 'INSERT INTO `tables`(name, assigned_seating, open_seating, position) VALUES ' . $vals);

assumed id was auto incremented so just leave it out of the query

Upvotes: 0

Gerardo Jaramillo
Gerardo Jaramillo

Reputation: 485

You can do a Batch insert:

insert into Table
(column1, column2)
    VALUES
      ('value 1', 'value2') ,
      ('value3', 'value4')

You can do as many rows as you want as long as you separate them by comas.

Upvotes: 0

Related Questions