SQL statement inside loop with PHP, good idea?

Question

I ran into the following question while writing a PHP script. I need to store the first two integers from an array of variable lenght into a database table, remove them and repeat this until the array is empty. I could do it with a while loop, but I read that you should avoid writing SQL statements inside a loop because of the performance hit.

A simpliefied example:

while(count($array) > 0){
if ($sql = $db_connect->prepare("INSERT INTO table (number1, number2) VALUES (?,?)")){
                        $sql->bind_param('ii',$array[0],$array[1]);
                        $sql->execute();
                        $sql->close();
                    }
                        array_shift($array);
                        array_shift($array);
}

Is this the best way, and if not, what's a better approach?

Performing a database operation every time a loop iterates can cause problems. You need to execute a batch to reduce the number of trips to the database. — Lion
– Lion, Commented May 3, 2013 at 17:44

Niels · Accepted Answer · 2013-05-06 11:14:59Z

1

You can do something like this, which is way faster aswell:

Psuedo code:

$stack = array();
while(count($array) > 0){
    array_push($stack, "(" . $array[0] . ", " . $array[1] . ")");
    array_shift($array);
    array_shift($array);
}
if ($sql = $db_connect->prepare("INSERT INTO table (number1, number2) 
                                 VALUES " . implode(',', $stack))){
    $sql->execute();
    $sql->close();
}

The only issue here is that it's not a "MySQL Safe" insert, you will need to fix that!

This will generate and Array that holds the values. Within 1 query it will insert all values at once, where you need less MySQL time.

edited May 6, 2013 at 11:14

answered May 3, 2013 at 17:46

Niels

50.1k4 gold badges68 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jenthe Over a year ago

Looks good, thanks! But what do you mean with the MySQL safe part?

Niels Over a year ago

I just put the array directly in the query, so not as parameters and I didn't use mysql_real_escape_string on it so there could be no MySQL injection.

Jenthe Over a year ago

Just for helping others who are having the same problem: the above code is perfect but don't forget to add a comma as 'glue' to the implode function, otherwise it won't work! Example: implode(",",$stack)

Austin Best · Accepted Answer · 2013-05-03 17:44:37Z

0

Whether you run them one by one or in an array, an INSERT statement is not going to make a noticeable performance hit, from my experience.

The database connection is only opened once, so it is not a huge issue. I guess if you are doing some insane amount of queries, it could be.

answered May 3, 2013 at 17:44

Austin Best

1,0584 gold badges14 silver badges32 bronze badges

Comments

matzone · Accepted Answer · 2013-05-03 17:44:50Z

0

I think as long as your loop condition is safe ( will break in time ) and you got something from it .. it's ok

answered May 3, 2013 at 17:44

matzone

5,7293 gold badges19 silver badges20 bronze badges

Comments

Ascherer · Accepted Answer · 2013-05-03 17:52:38Z

0

You would be better off writing a bulk insert statement, less hits on mysql

$sql = "INSERT INTO table(number1, number2) VALUES";
$params = array();
foreach( $array as $item ) { 
    $sql .= "(?,?),\n";
    $params[] = $item;
}
$sql = rtrim( $sql, ",\n" ) . ';';
$sql = $db_connect->prepare( $sql );
foreach( $params as $param ) {
    $sql->bind_param( 'ii', $param[ 0 ], $param[ 1 ] );
}
$sql->execute();
$sql->close();

edited May 3, 2013 at 17:52

answered May 3, 2013 at 17:44

Ascherer

8,1213 gold badges45 silver badges60 bronze badges

Comments

Dan Bracuk · Accepted Answer · 2013-05-03 18:00:04Z

In ColdFusion you can put your loop inside the query instead of the other way around. I'm not a php programmer but my general belief is that most things that can be done in language a can also be done in language b. This code shows the concept. You should be able to figure out a php version.

<cfquery>
insert into mytable
(field1, field2)
select null, null
from SomeSmallTable
where 1=2
<cfloop from="1' to="#arrayLen(myArray)#" index="i">
select <cfqueryparam value="myArray[i][1]
, <cfqueryparam value="myArray[i][]
from SomeSmallTable
</cfloop>
</cfquery>

When I've looked at this approach myself, I've found it to be faster than query inside loop with oracle and sql server. I found it to be slower with redbrick.

There is a limitation with this approach. Sql server has a maximum number of parameters it will accept and a maximum query length. Other db engines might as well, I've just not discovered them yet.

Collectives™ on Stack Overflow

SQL statement inside loop with PHP, good idea?

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related