Optimize SQL query in foreach() loop

Question

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
)

jedrzej.kurylo · Accepted Answer · 2015-08-07 12:01:44Z

2

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:

edited Aug 7, 2015 at 12:01

answered Aug 7, 2015 at 11:56

jedrzej.kurylo

41.1k10 gold badges108 silver badges112 bronze badges

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

2 Comments

jedrzej.kurylo Over a year ago

Dear down-voters - please leave a comment, I'd love to learn what's wrong with that code :)

Draven Over a year ago

Thank you very much. I did not think about using IN(). I'm aware mysql_ is depreciated and the site is in the process of getting recoded to use MySQLi.

sarath · Accepted Answer · 2015-08-07 11:52:03Z

0

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

answered Aug 7, 2015 at 11:52

sarath

3438 silver badges31 bronze badges

Comments

syed suleman · Accepted Answer · 2015-08-07 12:03:29Z

0

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>';
}

edited Aug 7, 2015 at 12:03

answered Aug 7, 2015 at 11:52

syed suleman

5422 silver badges6 bronze badges

1 Comment

jedrzej.kurylo Over a year ago

How is this supposed to work? $forge_materials_name will be fetched only for the first material and then used in every loop run. Every row displayed should show the name corresponding to the id.

Collectives™ on Stack Overflow

Optimize SQL query in foreach() loop

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related