Selecting multiple database tables...

leapingfrog

I'm making a search engine, and I have the feature "Search the Entire Site". This will search all the tables in my database for whatever they are searching for..

Anyway, I'm having problems getting this to work. What query would I use to SELECT multiple tables and search them all?

I keep getting an error saying something like the content column is ambiguous. What does this mean?

I'm currently using a query that looks simiar to this:

SELECT * FROM misc, about WHERE LOWER(content) LIKE '%$wrd%'

Thanks for any help,
Andrew

brandonschnell

mysql doesn't know from what table you want to search the content column.

try:
SELECT * FROM misc, about WHERE LOWER(misc.content) LIKE '%$wrd%' OR LOWER(about.content) LIKE '%$wrd%'

hotspur

While Brandon's suggestion gets you over the error message, having looked at your query I am not sure your logic is correct.

Forgive me if my understanding is wrong but are you wanting to join the data from the two tables together in this fashion?

Your query will produce a cartesian product ie every row in misc joined with every row in about. so if you have 100 rows in each you will get 10000 rows back and each row would contain all of the columns in each table. Is this the reslt you require?

The alternative (and what I assume you are after) is to select all of the data from misc followed by all of the data from in about. There are two ways of doing this. Fistly if you want to use only one SQL statement a UNION is required

Select from misc
where LOWER(content) LIKE '%$wrd%'
UNION ALL
Select from about
where LOWER(content) LIKE '%$wrd%'

however Union expects the columns selectted in each part to be the same so the structure of about and misc would need to be the same. You can frig this by selecting simialr columns and giving them the same AS name.

You could select the data from each table into separate arrays held in PHP and then join the data in the program before displaying it.

leapingfrog

🙁 I'm having problems getting this to work...

I tried the query you told me to use mark, and it isn't working. The table structures are the exact same.

Are there any other ways to do this or is there something I'm doing wrong?

I simplified the query a bit:
SELECT FROM cmmnity
UNION ALL
SELECT FROM progs

That throws this error at me:
<I><B>"MySQL said: You have an error in your SQL syntax near 'ALL SELECT * FROM progs' at line 2"</B></I>

Thanks for any additional help,
Andrew

hotspur

Sorry my fault!!
I checked the SQL 92 standard not the MySQL manual before I sent in the answer.

MySQL does not supprt UNIONS I had a look at their manual and they advise using temp tables ie

CREATE TEMPORARY TABLE tmp
SELECT FROM cmmnity;
INSERT INTO tmp
SELECT FROM progs;
SELECT from tmp;
/ Insert display text etc here */
DROP TABLE tmp;

I would advise explicitly naming your colums rather than using the *.

Anon

Thanks a lot mark! 🙂

I'll try and see if it works. Thanks.

shanx24

Ok just a needless plug. Been there, done that. At the end of the day, if you are running a serious website, using temp tables might not be a good idea simple because of dysmal performance (its also less portable - something I've had to face).

You might like to create a new SEARCH table with a blob for the field. You could put all your content from other tables into this table (disk space is cheap, I hope you wont have to worry about that). Searching will be a lot faster and simpler for you (and most likely more maintenable if you are using a suitably relational design).

One corollary -ve effect could be that for all your content, you will have to add a functionality that when someone inserts.updates.deletes content, the same is reflected in your search db as well.

Trust me, it will be a bit of an initial effort, but its a good way to go.

I'd love to hear other ideas/opinions..

Shanx

hotspur

Yes I agree but you are not using the temp tables to store the data in while it is being processed, the data is already stored in the underlying tables. You are infact mimicking the UNION statement that MySQL does not support.

if you used the SEARCH table option, this would include the overhead of keeping this table is step with the underlying tables. As it's not my site i don't know how often the data will change