Optimizing MySQL/PHP queries

Question

My database looks something like this:

[foods]
foods_id, foods_name, groups_id, producers_id

[producers]
producers_id, producers_name

[groups]
groups_id, groups_name

PHP:

<?php
$producers = mysql_query('SELECT p.producers_name,p.producers_id FROM foods as f,producers as p WHERE p.producers_id = f.producers_id AND f.groups_id = 1 GROUP BY p.producers_name');
    //list the producer...
    while($row = mysql_fetch_array($producers, MYSQL_ASSOC)){
        $foods = mysql_query('SELECT foods_id, foods_name FROM foods WHERE producers_id = '.$row['producers_id'].' ORDER BY foods_name ASC');       
        while($row2 = mysql_fetch_array($foods, MYSQL_ASSOC)){
            //list the foods under this producer...
        }
    }
?>

GOAL:

PRODUCER A
   FOOD A
   FOOD B
   FOOD C
PRODUCER B
   FOOD D
   FOOD E
PRODUCER C
   FOOD F
...

SOLUTION:

<?php
$res = mysql_query('SELECT f.foods_id, f.foods_name, p.producers_id, p.producers_name FROM foods f, producers p WHERE p.producers_id = f.producers_id AND f.groups_id = '.$_GET['g'].' ORDER BY p.producers_name ASC, f.foods_name ASC');       
$xproducers = array();
while($row = mysql_fetch_array($res , MYSQL_ASSOC)){
    if(!array_key_exists($row['producers_id'],$xproducers)){
        $xproducers[$row['producers_id']] = array('id'=>$row['producers_id'],'name'=>$row['producers_name'],'foods'=>array());
    }
    array_push($xproducers[$row['producers_id']]['foods'],array('id'=>$row['foods_id'],'name'=>$row['foods_name']));
}
?>

This solution is fast enough for me, maybe its not perfect but it does the job fine :) I thank you all for your awesome help!

Cheers

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — orourkek
– orourkek, Commented May 24, 2012 at 19:18
have you tried a JOIN? dev.mysql.com/doc/refman/5.0/en/join.html — deltree
– deltree, Commented May 24, 2012 at 19:20

ShaaD · Accepted Answer · 2012-05-24 19:29:46Z

1

I cant understand why you do first select, this get the same data.

$res = mysql_query('SELECT f.foods_id, f.foods_name, p.producers_id, p.producers_name FROM foods f, producers p WHERE p.producers_id = f.producers_id AND f.groups_id = 1');       
    while($row = mysql_fetch_array($res , MYSQL_ASSOC)){
        $producers[$row['producers_id']][$row['foods_id']] = $row;
    }

answered May 24, 2012 at 19:29

ShaaD

6263 silver badges10 bronze badges

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

1 Comment

Cango Over a year ago

Your right, i could use this in a PHP array and then work my way with this array, instead of querying the server xxx times. Thanks

Jonathan · Accepted Answer · 2012-05-24 19:27:09Z

0

I would just do

mysql_query('SELECT producers_name, producers_id
FROM producers
WHERE producers_id IN
(SELECT producers_id FROM foods WHERE groups_id = 1)');

That should work much faster.

Best of luck!

edited May 24, 2012 at 19:27

answered May 24, 2012 at 19:20

Jonathan

9861 gold badge8 silver badges28 bronze badges

3 Comments

orourkek Over a year ago

Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information.

Cango Over a year ago

This took way longer to just list the producers? Showing rows 0 - 29 ( 289 total, Query took 4.3762 sec)

Cango Over a year ago

I did fix that :P still took to long

Collectives™ on Stack Overflow

Optimizing MySQL/PHP queries

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related