Search feature for essay site (and advice from Chris)...

[LittleGuy]

I'm having a little problem...

I have a couple of thousand essays in a mysql database. How can I allow users to efficiently search for them? Chris, would you mind sharing any advice on how you did it on your literature site?

PS. I want users to be able to search by the content of the essay (not just the name of the essay) -- i.e. search within the essay.

Thanks in advance.

-The Little Guy

[Chris]

The simplest way to do this is a MySQL query like this

select * from essays where essay_content like '%$searchterm%'

% is the SQL wildcard.

This will return essays where the exact (and I do mean exact) search phrase is found.

For more complex searches you can use a FULLTEXT index. That is what forums like this one use. I don't use a FULLTEXT index on my literature site as people are often looking for exact phrases. On an essay site where people are looking for general topics, I would use one.

http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html

[Chris]

http://www.zend.com/zend/tut/tutorial-ferrara1.php

Another tutorial on fulltext

[LittleGuy]

Chris,

Do you break up the search term and search for each individual one? ie LIKE '%keyword1%' OR '%keyword2%'?

Thanks for your advice

[incka]

I think I will use this, Mike will too I'm sure...

[chromate]

Originally posted by LittleGuy
Do you break up the search term and search for each individual one? ie LIKE '%keyword1%' OR '%keyword2%'?

It depends if you want an exact match for a search term or not. breaking it up will obviously match any word, where as if it's all one string then it will only match if it finds the exact phrase.

[rani]

hi,
The search like
select * from table name where column like "%search-string%"
will result the one which the word is found anywhere in the string.

But is it possible to check in a string for each word for exact match or even starting with?

I mean
-> " This string should return if I query_for the term querystarting with" ;
select * from tablename where column like "query%";

-> "This string should return when i query for this term when i match for this anywhere";
select * from tablename where column like "%this%";

->"This string should return for exact match for a word";
select * from tablename where column like "exact"

[AndyH]

I suggest giving an option to search just the essay titles and also the essay bodies. If the site is, or gets very popular, the searches could be very process consuming for the server.

Or you could just make the change when the time comes.

[Chris]

Yes Rani.

For instance to get everyone who has a name that starts with A

select * from people where name like 'A%'

[eMEraLdwPn]

i tried adding fulltext like that article said... after about 3 minutes i got this message:

MySQL said:

#3 - Error writing file '/tmp/STPALyTf' (Errcode: 28)

[rani]

Yes Rani.

For instance to get everyone who has a name that starts with A

select * from people where name like 'A%'

This works fine if the word start with the A.
For eg:
1) Andrew_smith was at the door.
2) I think Andrew_smith is at the door.
3) Andrew is right at the door
if we query like
select * from tablename where column like "Andrew%"; the first and third will return and the second will not.
what one can do for such thing.

regards,
rani

[AndyH]

SELECT * FROM `names` WHERE `name` LIKE '%A%';

[Todd W]

Is this


SELECT * FROM `names` WHERE `name` LIKE '%A%';

Just as fast as using a new column for only the first letter of their name (or last name).. you just grab the first char. when you add the names to the database and write it to a different column. Then search with


SELECT * FROM names WHERE name= "A"

Just curious.

[AndyH]

Well, if you want to make it as fast as possible then make another column that holds a value from 1-26 (TINYINT(2) UNSIGNED) and index it. Then use:


SELECT * FROM names WHERE first_letter= "7" If the first letter is a G.

[Todd W]

Well, if you want to make it as fast as possible then make another column that holds a value from 1-26 (TINYINT(2) UNSIGNED) and index it. Then use:


SELECT * FROM names WHERE first_letter= "7" If the first letter is a G.

Well thought idea :)