Kim Steinhaug wrote:
Im wondering how It would be possible to join two different mySQL
results into one set?
To clear it up a little bit, say we have two different databases which
are completely different but they both share fields name and email.
I want to select all the emails and names from both databases and
show them in table on the webpage.
The first thing that comes to mind is :
ALTERNATIVE 1
1. Select the results from table 1 (Which accually is a join select from 2
tables)
2. Select the results from table 2 (Which is a single table)
3. Spin through set one, and spin through set two.
This creates a problem since we are talking about eg. 1000 records and I
only show 20 a time on the screen with a nice next page in the bottom to
browse the results.
Another sollution could be :
ALTERNATIVE 2
1. Select the results from table 1 (Which accually is a join select from 2
tables)
2. Select the results from table 2 (Which is a single table)
3. Create new array with needed fields,
4. Pump in results 1
5. Pump in results 2
6. Show and browse the array as if it was the accual query.
This sounds practical, on the other hand a large set would be wasted
bandwidth
from the SQL engine and large arrays. Then again, theese queries arnt done
often so I shouldnt care to much about the hardware, there shouldnt be more
than
10.000 records either at max. (Sure, you never know but statistically there
shouldnt).
Finally, another sollution, cache the result set :
ALTERNATIVE 3
1. Check to see if we have a fresh cache? We dont so we go to point two
2. Select the results from table 1 (Which accually is a join select from 2
tables)
3. Select the results from table 2 (Which is a single table)
4. Create new cache table, punp in results set 1 and 2
5. Do the queries from cache table
Anyone have some points on this? I would maby think alternative 3 would be
best,
or maby alternative 2?
Maby you have some other alternatives?
Well, the obvious ALTERNATIVE 4 is rewriting both scripts to use a
single table, but
If you're using MySQL 4.0+ you can use UNION:
(SELECT name, email FROM database1.table1)
UNION
(SELECT name, email FROM database2.table2)
ORDER BY name ASC
LIMIT x,y
If you're not using MySQL4, then I'd go with Method 3 above. You can use
INSERT INTO ... SELECT ... to create the cache table. The only issue
with that method is that you have to come up with a method to clear the
cache. You can either clear it periodically (using cron, for example) or
clear it when there is a change to either of the two tables it's built
from (if you have control over those scripts).
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals www.phparch.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
