Looping to update article codes on one server based on queries on another server

Question

I've read somewhere on Stack Overflow that doing queries in a loop is very inefficient. It will hammer your SQL server and make your script very slow.

Sample code:

// Connect to SQL Server 1
$query = "SELECT * FROM
articles
";

$resource = mysql_query($query);
$articles = array();
while $record = mysql_fetch_assoc($resource) {
    $articles[] = $record;
}

// The part that bothers me:
// (Note: this is executed on a different sql server)
foreach ($articles as $article) {
    // Connect to SQL server 2
    $sQuery = "SELECT artcode FROM articles WHERE id='".$article['id']."'";
    $sResource = mysql_query($query);
    if (mysql_num_rows($sResource) == 1) {
        $data = mysql_fetch_assoc($sResource);
        // Connect to SQL Server 1 again ...
        // This will be executed on the first sql server again
        $uQuery = "UPDATE articles SET artcode='".$data['artcode']."' WHERE id='".$article['id']."' LIMIT 1";
        $uResource = mysql_query($query);
    }
}

How would I go and make this code more efficient. (In best case scenario avoiding doing the query in a loop.)

Answer 1

The problem: there is a query being performed inside a for-loop.

foreach ($articles as $article) {
    // Connect to SQL server 2
    $sQuery = "SELECT artcode FROM articles WHERE id='".$article['id']."'";
}

The solution: move the query outside of the for-loop

foreach ($articles as $article) {
    $articleIds[] = $article['id'];
}
$sQuery = "SELECT artcode FROM articles WHERE id IN '".implode(',',$articleIds)."'";

Then loop over the result of that query and peform the update query. Again, this update query be moved outside the loop. Check this for more info.

Answer 2

if you can access the second database from the same connection

use this query

UPDATE articles a1 inner join YOURDB.articles a2 on a1.id=a2.id
set a2.artcode = a1.artcode

but if your database is in another server i'm afraid you can't make it in one query

Answer 3

Less is more

I guess there's more going on in your code, but from what I can see you wouldn't need the whole article records. So this is what I would do:

$set = mysql_query('SELECT id, artcode FROM articles',$db1);
while ($row = mysql_fetch_assoc($set)) {
  extract($row);
  mysql_query("UPDATE articles SET artcode = '$artcode' WHERE id = '$id'",$db2);
}

As you can see it's short and simple. Yes, this is all you need.

You could try to do multiple updates in one SQL command, but that technique seems a bit clumsy to me. You would only do that if this is way too slow. It will complicate your code no end, and that makes debugging a lot harder.

Do I need to repeat that mysql_* functions are deprecated? No, I don't think so.