Resultset navigation

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ameque
Forum Newbie
Posts: 8
Joined: Wed Jun 13, 2007 8:18 am

Resultset navigation

Post by ameque »

I have two tables in MySQL that are not related. I need to have a search box yield results from both tables and be able to navigate through the results using one navigation system.
Last edited by ameque on Tue Sep 11, 2007 9:07 am, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Use SQL's JOIN statement, as in "SELECT * FROM table1 JOIN table2 ON table1.id=table2.table1_id WHERE foo='bar'". Use the AS keyword to get rename columns as needed.
(#10850)
ameque
Forum Newbie
Posts: 8
Joined: Wed Jun 13, 2007 8:18 am

Resultset Navigation

Post by ameque »

The UNION statement requires both tables to have same number of columns. I however have more columns on one table than the other and the search is being conducted on all columns.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

You haven't really given us a lot to work with.

Assuming there is some commonality between the tables (for now I assume id)

Code: Select all

SELECT * FROM table1,table2 WHERE table1.id=table2.table1_id AND table1.foo like '%search%' OR table2.bar LIKE '%search%';
The next question is obviously if the tables are not similar, why only process one result set as surely you need to work out which table the result belongs to. You could potentially use something like LEFT OUTER JOIN's to get the results, combined with looking at your database normalisation.
ameque
Forum Newbie
Posts: 8
Joined: Wed Jun 13, 2007 8:18 am

Resultset Navigation

Post by ameque »

Am developing a key search on two tables that are not related. The idea is to come out with one resultset that can be navigated through using PHP code. The UNION statement requires both tables to have same number of fields. If I use the LEFT OUTER JOIN, the search takes almost forever. HELP PLEASE!
Post Reply