How to get a loop query into one query in php

Question

I got something like this.

It's working, but have to do a mysql query like 40 times is not the best thing.

Can somebody help me get it in to one query?

$string_unique  = 'Unique string';

$number = count($something);

for ($i=0; $i < $number; $i++) 
{

if(!empty($some_input)) {       
    $string_one     = 'Something_one' . $i;
    $string_two     = 'Something_two' . $i;

    mysql_query("INSERT INTO `table` (`unique_string`, `string_one`, `string_two`) VALUES('$unique_string', '$string_one', '$string_two') ON DUPLICATE KEY UPDATE `string_one` = '$string_one', `string_two` = '$string_two'") or die(mysql_error());
} else {
    $string_one     = '';
    $string_two     = '';

    mysql_query("INSERT INTO `table` (`unique_string`, `string_one`, `string_two`) VALUES('$unique_string', '$string_one', '$string_two') ON DUPLICATE KEY UPDATE `string_one` = '$string_one', `string_two` = '$string_two'") or die(mysql_error());

}

Answer 1

You can generate a single query in that loop and then execute it only once:

$query = 'INSERT INTO `table` (`unique_string`, `string_one`, `string_two`) VALUES ';
$queryValues = array();

$string_unique  = 'Unique string';

$number = count($something);

for ($i=0; $i < $number; $i++)
{
    if(!empty($some_input)) {
        $string_one     = 'Something_one' . $i;
        $string_two     = 'Something_two' . $i;

        $queryValues[] = sprintf('("%s", "%s", "%s")', $unique_string, $string_one, $string_two);
    } else {
        // I can't understand what are you using this part for... Anyway.
        $queryValues[] = sprintf('("%s", "", "")', $unique_string);
    }
}

$query .= implode(', ', $queryValues);

// And here is the unique key updating.
// I don't know whole the structure, so I just guess you have the PRIMARY `id` row
// What you did in your ON DUPLICATE KEY was unnecessary.
$query .= ' ON DUPLICATE KEY UPDATE `id`=`id`';

// Don't use mysql_* functions in new code.
// See: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php
mysql_query($query);

Answer 2

You're a allowed to add multiple rows of data in an insert statement in MySQL. That way, you can build one big insert statement in the loop, and execute it in one go after the loop. That is way faster and much more efficient.

Answer 3

You can insert multi row with sql,

here an example

insert into table1 (First,Last) values ("Fred","Smith"), ("John","Smith"), ("Michael","Smith"), ("Robert","Smith");