Re: sorting via PHP or MySQL?

On Thu, 2007-05-10 at 14:00 -0400, James Tu wrote: > (I've cross posted at the MySQL list as well) > > Here's an example with a simple table: > > describe collection; > > +------------------+---------------------+------+----- > +---------------------+----------------+ > | Field | Type | Null | Key | > Default | Extra | > +------------------+---------------------+------+----- > +---------------------+----------------+ > | id | bigint(20) unsigned | | PRI | > NULL | auto_increment | > | receiver_id | bigint(20) unsigned | | MUL | > 0 | | > | set_type_id | int(2) unsigned | | | > 0 | | > | card_id | int(3) unsigned | | | > 0 | | > | completed_set_id | bigint(20) unsigned | | | > 0 | | > | created_on_gmt | datetime | | | 0000-00-00 > 00:00:00 | | > +------------------+---------------------+------+----- > +---------------------+----------------+ > > > I want to end up with two PHP arrays. One for set_type_id = 22 and > one for set_type_id=21. > > (1) one query method: > SELECT * from collection WHERE set_type_id=22 OR set_type_id=21; > ...do query... > while( $row = $this->db->fetch_array_row() ){ > if ($row['set_type_id'] == 21){ > $array_a[] = $row; > } else { > $array_b[] = $row; > } > } > > > (2) two query method: > SELECT * from collection WHERE set_type_id=22; > ...do query... > while( $row = $this->db->fetch_array_row() ){ > $array_a[] = $row; > } > > SELECT * from collection WHERE set_type_id=21; > ...do query... > while( $row = $this->db->fetch_array_row() ){ > $array_b[] = $row; > } > > > Which method is better? Take a hit using MySQL or take a hit using PHP? Single query method is superior in your example. Cheers, Rob. -- .------------------------------------------------------------. | InterJinn Application Framework - http://www.interjinn.com | :------------------------------------------------------------: | An application and templating framework for PHP. Boasting | | a powerful, scalable system for accessing system services | | such as forms, properties, sessions, and caches. InterJinn | | also provides an extremely flexible architecture for | | creating re-usable components quickly and easily. | `------------------------------------------------------------'