Question

我的数据库看起来是这样的:

[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...
        }
    }
?>

目标:

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 ]));
}
?>

这个解决方案对我来说已经足够快了, 也许不完美,

干杯干杯

Answer 1

我无法理解你为什么首先选择,这得到的数据是一样的。

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

Answer 2

我就会做

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

这应该能更快地发挥作用。

祝你好运!

友情链接