Search Engine Help

treeleaf20

I would like to put a search this site option on every page, my pages are completely database driven written in PHP as you can imagine so there is barely any content that a regular indexer could index on my site. I have an event section on my website and a forum and so forth, if a user types in a term like: "Saturday Night" I would want it to search the whole database (all tables) and come up with a result. I'm not sure how it would even search every table other then writing multiple queries and that method seems like it would take a lot of bandwidth. Any ideas on a method to use here?

Thanks.

execute

Very easy, if you have a MySQL database, all you do is search for the command like this:

"SELECT * FROM Table WHERE eventsections LIKE '%$searchterm%'";

use that SQL command, just replace the table with your table, and WHERE eventsections with whatever field in your table you are searching for, the % signs means wild card, incase someone misspells etc.

$searchterm is the variable that you need to use a "search the site" form to complete. And then you can display whatever it is you got selected.

treeleaf20

I understand how to do that, I will have to search through every table to get the results. I'm saying is there an easy way to maybe index the the actual database and then sort through it like that so that way I would only have to check against 1 table instead of manually saying search every single table in my database?

treeleaf20

Anyone have any ideas on how to manage this?

DoctorWho

ONe idea comes to mind.

You have to search through every Table, but you most likely don't have to search through every field.

You could grab the relevant fields and create a new table, based on those fields only, index it, and search on that.

That should also give you a smaller data set to search through.

But you have to also be able to determine which Table the original data is coming from. Therefore you would have to look at a way to put a reference in the new table, that refers to the original table.

But I would experiment with the way it's setup now. You might find it can still go through all the tables pretty quickly, or at least faster than you think.

Never really done anything like this though.

treeleaf20

Anyone have an idea how to index the database with certain fields and certain tables? Any idea where to start?

sneakyimp

think about this. if you want 1 table to search, you will need to change your code so that it maintains this table in an ongoing basis when your tables change. If you have forums, then each time a forum gets posted, this 1 search table is going to have to get updated.

from a coding perspective, it would probably be easier to query every table in a series of queries. or maybe you could write a complex JOIN query to hit all the tables at once.

if you are concerned about performance, then creating that one table might be a better way to go. I think PHPBB creates an index of the words used. I'm not quite sure how it handles searches for phrases. You might want to think about reading the phpBB source code.

Another possibility is that you could try to harness an existing search engine as your search tool. I think google allows you to specify a google search that only searches your site. The trick in that case is to make sure your dynamic content is properly indexed by google's webcrawler, googlebot. Google let's you upload sitemaps to their server in which you could specify EVERY possible URL on your site.

For forums, this is a bit of a hassle, so you would have to hope that the googlebot eventually gets to the new topics and so on because new content gets added by users visiting your site--making a comprehensive list of urls would be a total pain. I have noticed that certain forums do list on google quite well. As I understand it, part of the difficulty in getting forums indexed properly lies in the fact that session ids are sometimes appended to URLS when the client user agent doesn't support cookies. Many search engines will ignore sites that put session ids in the url (phpBB, osCommerce) because from the perspective of the webcrawler, it looks like the site has an infinite number of URLs because the session id is different every time it visits.