CODEWITHSUNDEEP
mysqlregexfull-text-search
autumn
autumn

Reputation: 31

Regexp and mysql: is there an "AND" operator

I have a database of book titles and would like the user to be able to search for the titles without having to use their search terms in the exact order of the title. The search is done using a form which queries the database

For example: I want them to be able to search Pride and Prejudice by asking for "Pride Prejudice" or "Prejudice Pride" instead of typing "pride and prejudice".

I tried using REGEXP and exploding the search terms and then imploding them with an OR operator (|) [gives me everything with the word "and" when searching pride and prejudice], (+) and (.*) to no avail.

Upvotes: 3

Views: 3239

Answers (5)

Bill Karwin
Bill Karwin

Reputation: 562791

If you need sophisticated search features, use a fulltext search solution.

In MySQL, the builtin fulltext search index feature works in MyISAM and InnoDB storage engines. InnoDB is the default since 2010, and the preferred engine in virtually all cases.

You could also use an external solution like Sphinx Search or Apache Solr to provide fulltext search capabilities externally to the database. There's even a way to use a Sphinx Search index via the MySQL pluggable storage engine interface.

I don't like using LIKE with wildcards or REGEXP for fulltext searches. These solutions incur full table scans for every search, and depending on the volume of your data, run hundreds or thousands of times slower than if you have an index.

I wrote a comparison of fulltext search solutions for MySQL in my presentation, "Practical Full-Text Search in MySQL," and also in a chapter of my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Upvotes: 4

Michael Eugene Yuen
Michael Eugene Yuen

Reputation: 2528

To match all:

$keywords = $_POST['keywords'];
$keys = preg_replace('!\s+!', '|', $keywords);
$count = count(explode('|',$keys));
$regexp = "(($keys).*){{$count}}";
$query = "SELECT * FROM table WHERE column REGEXP '$regexp'";

Upvotes: 0

Bodhi
Bodhi

Reputation: 31

This should be what your looking for:

$search_terms = trim($_GET['search']);
$search_terms = explode(" ",$search_terms);
$match_size = sizeof($search_terms);
$search = "";

for ($x=0;$x<$match_size$x++){
if($x>0)
    $search = $search."|".$search_terms[$x];
else
    $search = trim($search_terms[$x]);
}

select * from TABLE where FIELD regexp '(($search).*){{$match_size}}';";

Upvotes: 3

Yeti
Yeti

Reputation: 2865

Usually in normal Regular expressions you would just do (?=regA)(?=regB) but this doesn't work in MySQL now. My solution to this problem was to just add an AND statement in the MySQL statement. Although when automatically generated, semantically not a nice solution in your case.

SELECT * FROM table WHERE column REGEXP 'regA' AND column REGEXP 'regB'

Upvotes: 0

bfavaretto
bfavaretto

Reputation: 71939

I don't think you need REGEXP here. You could try something like ... WHERE title LIKE '%prejudice%' AND title LIKE '%pride%'.

Upvotes: 1

Related Questions