CODEWITHSUNDEEP
phpsql
Jenthe
Jenthe

Reputation: 797

SQL statement inside loop with PHP, good idea?

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?

Upvotes: 0

Views: 1296

Answers (5)

Niels
Niels

Reputation: 49929

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.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

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.

Upvotes: 0

Ascherer
Ascherer

Reputation: 8093

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();

Upvotes: 0

matzone
matzone

Reputation: 5719

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

Upvotes: 0

Austin Best
Austin Best

Reputation: 1058

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.

Upvotes: 0

Related Questions