advanced search engine help

[someone]

I have a couple of sites built with different databases because all these sites are under different subject. And the databases have been built for a long time already. I want to make a global search engine so if you search for ie. "berry", it would display results from all databases that have the search word.

Right now, if people want to search for "berry", they would have to go to my A database to search for it. Then if they want to search for "berry" from my B database, they would have to go there to search. So it's very inconvenient. I know I could just incorporate all the databases together into one big database, but I don't want to do that.

I hope I haven't lost anyone yet. Does anyone have any idea of doing that? Thanks.

[httpwebwitch]

you'd need to make separate connections to each database in turn. Throw your "hits" from database A into an array, then add the hits from database B, then when you're done sort the array to display the results.

[coopster]

Welcome to WebmasterWorld, someone!

[JasonHamilton]

As long as you have your indexs setup properly, you should be able to search on more than one table at the same time.

worse case, you can use a JOIN statement and join two tables together.

[someone]

coopster, thanks for the welcom.
httpwebwitch and JasonHamilton, thanks for the hints.

[httpwebwitch]

yes, are you talking about separate DSNs, separate databases, or multiple tables in the same database?

if you have multiple databases in the same "database" (e.g. mySQL), then you can use

mysql_choose_db("database_name1");
// do query
mysql_choose_db("database_name2");
// do another query

If you have multiple tables in the same database, then as mentioned above you can try a JOIN or UNION or something. I am hardly capable of giving advice on that subject... Hopefully someone will jump in with a solution, if there is one.

Regardless of the method you use to grab the hits, you will want to "mix" and sort the results by relevance. If there is a way to do it with just SQL, then do that! But it's easier to do in PHP, once you've grabbed all the close hits and thrown them into an array.

$hits[0]['URL']="http://yaddayadda"
$hits[0]['title]="this is my page"
$hits[0]['relevance']=95.4

$hits[1]['URL']="http://hupnahupna"
$hits[1]['title]="this is your page"
$hits[1]['relevance']=17.0

then you can sort the array by relevance, and show them in order.

[mincklerstraat]

If these truly are 3 different databases and not just 3 different tables as you say, you will probably want to combine the 3 databases into just one database nonetheless since connecting to databases can take time, and connecting to 3 different databases would, I imagine, result in a lot of work for your server, and time waiting for your users. 3 tables would be the way to go - using a join, as mentioned above, would speed things up tremendously.

If searching is really key to the functionality to your site, you could also set up another table that just has all the information from your other tables that you want searchable in one field, and an identifier or url in another field corresponding to the page all that info is on. This could use a fulltext index for better search relevance. It'll use a lot of hard disk space, but hard disk space is cheap these days.