0
foreach($array as $values){

    $MySQL_string = "
    UPDATE `MyGuests` SET `firstname` ='".$values['name]."' WHERE `id` ='".$values['user]."';
    ";

    $DB->mysqli->query($MySQL_string);

}

Are there any downfalls to performing multiple PHP MySQL queries in a for loop?

  • this way it just doesn't make any sense!? – Jeff Jan 02 '17 at 22:23
  • 2
    `foreach($value)` will just throw an error. – Rwd Jan 02 '17 at 22:26
  • 2
    You can run queries in a php loop. But you should update your code to use prepared statements, with placeholders, as (1) it is a more efficient way to run queries in a loop, and (2) it prevents sql injection - [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Sean Jan 02 '17 at 22:28
  • 2
    it's not so much as querying inside a loop, it's the connection you're doing each time – Funk Forty Niner Jan 02 '17 at 22:41
  • Fred -ii how do we do it in a loop without making a connection each time? Each time through the loop it will update a row. Is this proper or not? –  Jan 02 '17 at 23:04
  • 1
    The question I m trying to formulate here - is to ask if it is proper procedure to run queries inside a php for loop. Any"help" formulating this question would be appreciated. –  Jan 02 '17 at 23:13
  • 2
    It's perfectly fine to run SQL queries in a loop in PHP. Unless you do it all over the place and/or on most page-loads, or otherwise on a scale large enough to hinder your server's performance, when (if) more performance-friendly options would be available by refactoring your code/application logic. That said, if you're running `SELECT` statements in a loop, something's probably not right. – Markus AO Jan 02 '17 at 23:29

2 Answers2

2

It's perfectly fine to run SQL queries in a loop in PHP as long as you know what you're doing.

If you loop queries all over the place and/or on most page-loads, or on a scale large enough to hinder your server's performance, you should look into refactoring your code/application logic and find more performance-friendly options. This is unlikely to be the case with INSERT and UPDATE queries. If you're running lots of SELECT statements in a loop, however, you're probably doing something wrong.

You could avoid a loop by using mysqli_multi_query(). However it'll make debugging individual queries more difficult. Also it won't allow you to use prepared statements (a very good idea for both performance and security reasons). There's nothing inherently evil about loops. Don't worry too much about (premature) optimization for operations that aren't the churning core of your application.

Markus AO
  • 4,771
  • 2
  • 18
  • 29
1

Depending on your interpretation, it might be correct to run multiple queries in a loop. However, for optimal performance, it is best to reduce the queries to a minimal number of queries.

One way this can be done with your example is to use a CASE statement. See this article for more information, and the example below, based on your sample code.

Also, while this isn't codereview.stackexchange.com, it is recommended that you prepare statements and bind parameters instead of placing values inline, so as to avoid the possibility of a SQL injection attack. See this answer where parameters are bound for the INSERT statement.

$cases = array();
$whereConditions = array();
foreach($array as $values){
    $cases[] = 'WHEN `id` = '.$values['user'].' THEN \''.$values['name'].'\'';
    $whereConditions[] = '`id` = \''.$values['user'].'\'';
}


$MySQL_string = "UPDATE `MyGuests` SET `firstname` = CASE ".implode(' ',$cases)." END WHERE ".implode(' OR ',$whereConditions);
$DB->mysqli->query($MySQL_string);
Community
  • 1
  • 1
Sᴀᴍ Onᴇᴌᴀ
  • 8,218
  • 8
  • 36
  • 58
  • It's a possible solution, however -- as with `mysqli_multi_query()`, another work-around -- you will have to escape everything yourself. I'd rather use prepared statements and deal with minor performance overhead. (On a side-note, any idea on how well `CASE` performs?) – Markus AO Jan 02 '17 at 23:44
  • Sure - I support prepared statements with bound parameters, and could update the answer as such. As far as performance, that could be an open-ended topic - with answers like [this one](http://stackoverflow.com/questions/7722423/case-performance-in-mysql#answer-7722621) – Sᴀᴍ Onᴇᴌᴀ Jan 02 '17 at 23:48
  • Right one could actually turn that into a prepared statement. Would make one hella prep though with enough cases on the table! ^_^ I suspect reusing a simple prepared statement would perform better than prepping one of these. Cheers for the link! – Markus AO Jan 02 '17 at 23:51