Full Text Search Across Multiple Tables

[ralphiooo]

Hi, I've been looking for a solution to searching across multiple tables using a full text search to do a site wide search. ie say I have the following tables:

news_posts
- id
- title
- content

gallery_images
- id
- title
- image_url

I want to be able to search the title field for both tables and display the matches from both tables.

So far all I have come across is to do the full text search on both the tables and insert the results into a table which stores the score, table name and id and then query that table but it does seem a little long winded and surely won't be efficient enough with lots of data.

Appreciate it if someone could help. Thanks

 

[OsakaWebbie]

This sounds like a database question, not PHP. What database are you using? Also, you might find the fastest and best help on the forum corresponding to the database you are using.

 

[ralphiooo]

Using MySQL. Tried posting in the mysql forum on this site but it wouldn't let me for some reason.

 

[OsakaWebbie]

Okay, I have never tried this, so it's a bit of grasping at straws, but would this give you stuff from both tables?

SELECT n.id AS NewsID, g.id AS GalleryID from news_posts n, gallery_images g WHERE n.title='string' OR g.title='string'

With no criteria connecting the tables as relational, it goes against the grain of how such a query is supposed to be used, and in the code that processes the results you'll have to work with two columns in your returned data for what you probably intend to be one thing, but does it give you the data you expect?

As aside comment: The fact that you want to do such a query tells me that your database is probably not designed the way it should be. If id and title in both tables refers to the same kind of thing, you should probably have a separate table for those, like "posts", and then news_posts and gallery_images would not have title but just id, and that would be related to the id of the "posts" table. Then you could just search the posts table for what you want, and do joins to get the information in the other tables as needed. But I am aware that you may not have the liberty to redesign the database - I just thought I'd mention it.

 

[OsakaWebbie]

By the way, it's strange that you could post on one forum but not another, but perhaps the moderators can move this whole thread over to the MySQL forum, which is where it definitely belongs.

 

[jpadie]

if this is something you must do in a single qiuery, consider a union query.

for the like compare remember to enclose your search term in % signs

remember also to setup the right indices otherwise your queries will be far from optimal.

 

[ralphiooo]

Hi, cheers jpadie I went for the union way and so far I've got:

(SELECT post_id AS result_id, title, content, 'news_posts' AS table_name, MATCH (title, content) AGAINST ('$search_string') AS score FROM news_posts WHERE MATCH (title, content) AGAINST ('$search_string'))
UNION ALL
(SELECT image_id AS result_id, image_name AS title, description AS content, 'gallery_images' AS table_name, MATCH (image_name, description) AGAINST ('$search_string') AS score FROM gallery_images WHERE MATCH (image_name, description) AGAINST ('$search_string'))
ORDER BY score DESC
LIMIT " . ($page - 1) * 15 . ", 15

which works a treat, the only problem i'm having is that i wish to return a count of the total results from both tables. Appreciate the help once more. Thanks

 

[jpadie]

how about mysql_num_rows()?

 

[ralphiooo]

I expect that's the way i'm going to go but was trying to optimize, i did try:

SELECT SUM(all.num_rows) AS total_num_rows FROM (
SELECT COUNT(post_id) AS num_rows FROM news_posts WHERE MATCH (title, content) AGAINST ('$search_string')
UNION ALL
SELECT COUNT(image_id) AS num_rows FROM gallery_images WHERE MATCH (image_name, description) AGAINST ('$search_string')
) all

but it did not work.

 

[jpadie]

could you add a count(*) as cnt as a column in each part of the union?

 

[ralphiooo]

Huh, afraid you lost me I thought i was using a count in each part of the union and then taking a sum.

 

[OsakaWebbie]

how about mysql_num_rows()?

I expect that's the way i'm going to go but was trying to optimize...

In my opinion, keeping the query as simple as possible, and just asking PHP for the number of rows that were returned, is about as optimized as it gets. I use mysql_num_rows() all the time - why is that a last resort for you?

 

[ralphiooo]

Ah right k, i'll go for that.