Hi,
I have a table links, which the user can search against using keywords.
The records can be searched against Title and Description which are fields that belong to eachh record.
I am trying to construct a query similar to below
SELECT * FROM links
WHERE Description LIKE '%Keyword1%'
LIMIT 1000;
but that allows me to search for several keywords, aggainst two columns ... ie :
SELECT * FROM links
WHERE Title or Description LIKE '%Keyword1%' OR '%Keyword2%'
LIMIT 1000;
So the Query would search against both Title and Description and search foor one or more keywords.
Hope someone can help !
Stuck with a simple query
Moderator: General Moderators
Using your methodology, this is what you need to do
However, I'd strongly suggest using a FULLTEXT search - search functionality built in to MySQL. This lets you match against any number of keywords, and you can get relevance returned as well, rather than just returning everything that matches.
MySQL FULLTEXT searching
Code: Select all
SELECT
*
FROM
links
WHERE
Title LIKE '%Keyword1%' OR
Title LIKE '%Keyword2%' OR
Description LIKE '%Keyword1%' OR
Description LIKE '%Keyword2%'MySQL FULLTEXT searching
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
It is better if you have full text index but if you are not interested you can use boolean text mode which is of higher clarity than where statements..
Code: Select all
select * from `links`
match (`Title`, `Description`)
against ("keyword1 keyword2" in boolean mode)
limit 1, 1000;- raghavan20
- DevNet Resident
- Posts: 1451
- Joined: Sat Jun 11, 2005 6:57 am
- Location: London, UK
- Contact:
They are two full text searches available one with fulltext index and the other one with or without fulltext index
You have to create full text indexes on the search fields...
example:
Putting an index would definitely speedup queries on huge volume of records.
You have to create full text indexes on the search fields...
example:
Code: Select all
alter table `links`
add fulltext LinkDescriptionIndex (`link`, `description`);Hi,
I have attemprted to use the full-text query as follows :
SELECT * FROM links
WHERE MATCH (Title,Description) AGAINST ('dating')
LIMIT 100;
However for most cases it returnes no rows .. even when the word 'dating' appears in many Descriptions.
I'm not sure how the query is behaving, but it seems it is trying to match both Title AND Description against the keyword, however, is it possible to match Title OR Descrption? .. so the keyword does not have to appear in both columns it is searching against?
I hope the above all makes sense.
I have attemprted to use the full-text query as follows :
SELECT * FROM links
WHERE MATCH (Title,Description) AGAINST ('dating')
LIMIT 100;
However for most cases it returnes no rows .. even when the word 'dating' appears in many Descriptions.
I'm not sure how the query is behaving, but it seems it is trying to match both Title AND Description against the keyword, however, is it possible to match Title OR Descrption? .. so the keyword does not have to appear in both columns it is searching against?
I hope the above all makes sense.