Forum Moderators: coopster
I want to build quite a good search engine and want to know your ideas for it.
My biggest problem is that I have a databank stored in various files: pps, doc, xls which are (now) in folders 0,...9,A,B,...Z. However in those folders the may be a file, or a subfolder (and so on, and so on).
So what I thought of in the first place is to automate the process of storage.
I created a table for storing urls:
CREATE TABLE urls (id...,url, name, description, letter);
However for search improvement I have made a new table with keywords
CREATE TABLE keywords (id, keyword, url_id)
where I would like to store the keyword searched,
and table mistakes where if there's a mistake, the correct keyword is stored.
CREATE TABLE mistakes (id, mistake, correct)
I scan the hdd and put the urls, names of the files and the letter of the dir into a db urls.
My search queries look now like this:
SELECT urls.url, urls.name, urls.description FROM urls, keywords WHERE urls.id=keywords.url_id AND keywords.keyword='$q' LIMIT $offset, 15;
$q is mysql_real_escaped with stripslashes if necessary
SELECT urls.url, urls.name, urls.description FROM urls, keywords, mistakes WHERE urls.id=keywords.url_id AND keywords.keyword=mistakes. AND correct.'$q' LIMIT $offset, 15;
If the above results haven't returned any answers then remember the keyword in no_keyword (it's a table with no results search keyword, date and username) (INSERT INTO... so I can see how many times the keyword was searched)
then search for similarities:
(SELECT url, name, description FROM urls WHERE name LIKE '% $q %') UNION
(SELECT url, name, description FROM urls WHERE name LIKE '%$q%') UNION
(SELECT url, name, description FROM urls WHERE description LIKE '% $q %') UNION
(SELECT url, name, description FROM urls WHERE description LIKE '%$q%') LIMIT $offset, 15
I count total queries returned in $i and would like to show only 15, and not to do the LIKE query if not necessary
However I think I will do the first two (with keyword and mistake with UNION as well)
Manually I need to look at the no_keywords and move the data either to keywords or mistakes. I need to add description to urls. And the problem is with pagination of the page - I don't want to search with LIKE if it's not necessary.
My question is: Is there a better way to do that? And feel free to comment this way!
PS. There may be a problem with multiple word queries.
Best regards
Michal Cibor
