Searching through multiple tables

[jumbo]

Hi

 

I have like 50 tables I want to search through for a keyword. I am certainly not gonna do a query for each table separately, plus I need the results to be stored in 1 variable and ordered by date. The following query returns an error (using 2 tables for example):

 

mysql_query("SELECT * FROM table1,table2 WHERE text LIKE \"%$keyword%\" OR title LIKE \"%$keyword%\" ORDER BY date")

 

If I run this query using 1 table only, it returns a valid result.

 

Is there something I missed here?

 

PS: all the tables have the same structure, including columns named "text", "title" and "date"

 

Thanks

[CodeMaster]

Well I suppose you have a good reason for having 50 tables of the same structure. But I have my doubts.

 

Anyway, you might want to create a VIEW and than merge the tables with UNION ALL. I think that will kill your server for 50 tables, but if so you can make a new table (temp) with a stored procedure and then run that stored procedure from time to time.

 

CREATE VIEW view_table AS
SELECT field FROM table0
UNION ALL SELECT field FROM table1 
UNION ALL SELECT field FROM table2
UNION ALL SELECT field FROM table3
UNION ALL SELECT field FROM table4
UNION ALL SELECT field FROM table5
....