CODEWITHSUNDEEP
mysql
Keverw
Keverw

Reputation: 3786

simple mysql search engine

I'm working on a open source script. (https://github.com/keverw/Random-Quotes)

SELECT * FROM quotes WHERE text LIKE '%$searchTermbox%' AND OR author LIKE '%$searchTermbox%' LIMIT 15

I'm trying to make a simple search engine where it searches both text and author and also won't have duplicates. Any ideas? So if i type in "Kevin" it would show both authors with that name and Quotes mentioning them.

So AND OR because somethings they both will mention it or they both won't.... Any ideas how to write something very simple like this? I'm very basic at MySQL.

id | text | time | author | approved is the Structure. Also approved needs to = 1

Upvotes: 0

Views: 567

Answers (2)

drf
drf

Reputation: 8699

Note that using LIKE '%...' is generally inefficient since it requires every record in the table be individually scanned, and cannot take advantage of indexes. Generally, the more efficient method for this type of problem is to use a full text index, which MySQL supports (see, e.g., http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html).

This approach requires first creating the full-text index (a one-time operation), and then using a specialized syntax to query the data (in place of the LIKE operator).

To create the full text index (assuming quotes is a MyISAM table), you can use:

ALTER TABLE quotes ADD FULLTEXT (author, text);

Then the equivalent query expression can be written as:

SELECT * FROM quotes WHERE MATCH (author, text) AGAINST ('$searchTermbox')
  AND APPROVED=1
  LIMIT 15;

This should provide the same results as the earlier query, but may be significantly faster, particularly if the database is large.

Upvotes: 2

Konerak
Konerak

Reputation: 39763

You don't need the AND.

SELECT * 
FROM quotes 
WHERE text LIKE '%$searchTermbox%' 
    OR author LIKE '%$searchTermbox%' 
LIMIT 15

This will select all quotes where either text or author or both contain $searchTermbox.

This is because when using OR, the AND case is covered as well. By default, a OR b means a, b or both.

Upvotes: 2

Related Questions