CODEWITHSUNDEEP
phpmysql
Draven
Draven

Reputation: 1467

Optimize SQL query in foreach() loop

How else can I get the name without putting the query in the foreach loop?

PHP:

$material_decode=json_decode($materials['materials']);
foreach($material_decode as $material_id=>$material_value)
{
    $forge_materials=mysql_fetch_assoc(mysql_query('SELECT `name` FROM `forging_materials` WHERE `id`='.$material_id.' LIMIT 1'));
    echo '<tr>'.
        '<td>'.
            $forge_materials['name'].
        '</td>'.
        '<td>'.
            number_format($material_value).
        '</td>'.
    '</tr>';
}

$material_decode (forge_material_id => material_value):

stdClass Object
(
    [2] => 25
    [4] => 32
    [5] => 23
)

Upvotes: 2

Views: 267

Answers (3)

syed suleman
syed suleman

Reputation: 542

Updated your code. Try this

$forge_materials_name   =   "";

$material_decode=json_decode($materials['materials']);
foreach($material_decode as $material_id=>$material_value)
{
    if( empty($forge_materials_name) )
    {
        $forge_materials=mysql_fetch_assoc(mysql_query('SELECT `name` FROM `forging_materials` WHERE `id`='.$material_id.' LIMIT 1'));  
        $forge_materials_name   =   $forge_materials['name'];
    }
    echo '<tr>'.
        '<td>'.
            $forge_materials_name.
        '</td>'.
        '<td>'.
            number_format($material_value).
        '</td>'.
    '</tr>';
}

Upvotes: 0

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40919

You can fetch all needed records at once using WHERE IN outside the loop. Only single query will be neccessary this way.

This will work for you:

$material_decode = json_decode($materials['materials'], true);
$forge_materials = mysql_query('SELECT `id`, `name` FROM `forging_materials` WHERE `id` IN ( '. implode(',', array_keys($material_decode)) .')');

while($row = mysql_fetch_assoc($forge_materials))
{
    echo '<tr>'.
      '<td>'.
        $row['name'].
      '</td>'.
      '<td>'.
        number_format($material_decode[$row['id']]).
      '</td>'.
    '</tr>';
}

Keep in mind that mysql_ methods have been deprecated and it is now advised to use PDO or mysqli_ methods instead. You can read more in the docs:

Upvotes: 2

sarath
sarath

Reputation: 343

First run SELECT id,name FROM forging_materials WHERE 1 it will fetch all id & names from the table and then in the foreach loop, get the name from the query result which have id equals to $material_id

Upvotes: 0

Related Questions