Rene’s PHP Blog

I just ran into the problem of a very big database, and a very slow search function..

At first i was a little confused, but soon i came to the conclusion of building a Full Text Search function.

The first problem on building this i encountered was the database coalition. You have to have a MyISAM database. So, that’s what i did.

• Create a table:

CREATE TABLE news (content TEXT, title VARCHAR(250), id INT(11) NOT NULL auto_increment, PRIMARY KEY(id);

• Add a fulltext index:

ALTER TABLE articles ADD FULLTEXT(content, title);

This FULLTEXT index makes an index on both content and title, so you can’t search just one of them.

• Create a Query:

$query = "SELECT title,content,id, MATCH(title,content) AGAINST ('my search') AS score FROM news WHERE MATCH(title,content) AGAINST ('my search')";

Let me explain:

SELECT MATCH(title,content) AGAINST (’my search’) means you select the result of a MATCH. The result is a score. The higher the number, the better the result.

AGAINST (’my search’) searches in the selected fields in the MATCH (title,content) in my example. The search can be multiple words. The search searches for all words individually. You can also add a minus (-) and a plus (+) to a word, in order to change the priority. A minus tries searching a row without the word, a plus makes a word more imporant.

The reason the MATCH AGAINST is double in my query is just because i want to order my results (2nd time) and i want the score visible (1st time). I rename my MATCH AGAINST as Score, in order to get the score back out of the query. This would be something you could show on your page. Or do something with in your code.

If you want to limit the result by score, you have to add the following to the query:

HAVING score > 2 (you can change the number in anything you want, but this will filter the worst results)

Today i had a problem i only had a week number and a year. And i needed timestamps to get all results from a database in that week.

So, i searched on google and found this as first result:
http://tzzz.wordpress.com/2006/08/14/8/

The code he provided wasn’t exactly what i have been looking for but in the comments there was a result that was very close.

function getFirstDayOfWeek($year, $weeknr){
 
   $offset = date('w', mktime(0,0,0,1,1,$year));
   $offset = ($offset < 5) ? 1-$offset : 8-$offset;
   $monday = mktime(0,0,0,1,1+$offset,$year);
   return strtotime('+' . ($weeknr - 1) . ' weeks', $monday);
}

Now i changed this code to provide me the first and last day of the week:

function getFirstDayOfWeek($year, $weeknr){
 
    $offset = date('w', mktime(0,0,0,1,1,$year));
    $offset = ($offset < 5) ? 1-$offset : 8-$offset;
    $monday = mktime(0,0,0,1,1+$offset,$year);
    $sunday = mktime(0,0,0,1,7+$offset,$year);
 
    $day['first']=strtotime('+' . ($weeknr - 1) .  'weeks', $monday);
    $day['last']=strtotime('+' . ($weeknr - 1) . ' weeks', $sunday);
 
return($day);
}

This solved my entire problem, so i could search my database with weeks without having it to store it in the database. That would be a solution that would cost me to much data.