RE: [PHP] sorting via PHP or MySQL?
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| | | -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? > > -James I really don't think you'd notice any difference, unless your table contains a large amount of data and is not properly indexed. And if that were the case, you'd notice a hit on just one query anyway. I'd stick with the 2 query model, but instead of creating arrays, just loop through the query results to output your data, no sense looping to make an array when you're just going to end up looping through the array to output; that seems redundant to me :P Just my $.02 -B -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] 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| | | -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. | `' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sorting via PHP or MySQL?
A somewhat more extensible version of the 1 query method: http://www.garfieldtech.com/blog/php-group-by If you will only ever have 2 values there, then either method is probably fine. The "php group by" method (above) is more extensible if you're going to have a variable or arbitrary number of groups, though. Cheers. On Thursday 10 May 2007, 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| | | -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? > > -James -- Larry Garfield AIM: LOLG42 [EMAIL PROTECTED] ICQ: 6817012 "If nature has made any one thing less susceptible than all others of exclusive property, it is the action of the thinking power called an idea, which an individual may exclusively possess as long as he keeps it to himself; but the moment it is divulged, it forces itself into the possession of every one, and the receiver cannot dispossess himself of it." -- Thomas Jefferson -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sorting via PHP or MySQL?
On Thu, May 10, 2007 1:00 pm, 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| | | -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; > } */ $array[$row['set_type_id']][] = $row; > } var_dump($array); You'll have one array of all the 21s, and one of all the 22s. > Which method is better? Take a hit using MySQL or take a hit using > PHP? Honestly, is really doesn't make a damn bit of difference unless you have ZILLIONS of records in the first place, in which case you shouldn't be sucking them all down at once anyway... So write whatever you can figure out what's going on next month/year without beating your head against the wall trying to read your own code. Worry about optimizing only after you identify bottlenecks. Anything else is optimize-wankery. -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php