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
