David:
I definitely can get the result set using one query, but what I do
with the result set has me thinking about breaking it up into two
queries.
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? I still think that based on David's comments
regarding MySQL being more performative I'm leaning towards option (2).
-James
On May 10, 2007, at 12:54 PM, David T. Ashley wrote:
On 5/10/07, James Tu <[EMAIL PROTECTED]> wrote:
I think b/c of the way the tables are designed, I have to perform
multiple queries, unfortunately.
Hi James,
My suggestion to you would be that if you have a situation you
don't believe
you can handle in one query, post all the details to the MySQL list
and let
others take a whack at it.
I've not yet encountered a situation where the database can't be
designed
for "one query" results.
I also believe that MySQL has temporary table functionality:
http://www.xaprb.com/blog/2006/03/26/temporary-table-subtleties-in-
mysql/
http://www.devwebpro.com/devwebpro-39-20010817Temporary-Tables-With-
MySQL.html
I don't know how this works (I guess I should read the manual), but
I think
this would give you the ability in many cases to have MySQL (rather
than
PHP) do the heavy lifting. It will be much more efficient in MySQL
than in
PHP.
Good luck, Dave.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
