Page 1 of 1

Moved voluntarily to databases: REGEX to query my database

Posted: Wed Nov 23, 2005 6:11 am
by zoe
I've built a simple, local business directory that pretty much works, but I really want to refine the results a little.
I'm currently just using '%$search_string%', which is fine, but obviously if the user searches for 'car', they also get 'McArthur's Newsagent, etc.

I've absolutely no experience of regular expressions; it looks like I need to use ^ and $ at some point, but I'm not sure where, and what the syntax should be.

Below is a simplified version of some of my code. Should I be using regex at the begginning where I define my variables, or within the query? Any pearls of wisdom would be hugely appreciated.

Code: Select all

$businesstype = ($_GET['businesstype']);

$query = "SELECT * FROM business_directory WHERE Name LIKE '%$businesstype%' OR (Type LIKE '%$businesstype%' OR Keywords LIKE '%$businesstype%')";

Posted: Wed Nov 23, 2005 6:18 am
by Jenk
Hi there, I would suggest that if you are allowing the user to specify if the phrase begins or ends with the criteria, that you structure your SQL to suit it so, with something along the lines of:

Code: Select all

<?php

if ($beginend = 'begin') {
    $sql = "SELECT * FROM `table` WHERE column LIKE '{$criteria}%'";
} elseif ($beginend = 'end') {
    $sql = "SELECT * FROM `table` WHERE column LIKE '%{$criteria}'";
} else {
    $sql = "SELECT * FROM `table` WHERE column LIKE '%{$criteria}%'";
}

?>
This will be far more efficient than using the 3rd statement everytime and then regex'ing the results :)

REGEX to query my database

Posted: Wed Nov 23, 2005 6:59 am
by zoe
Aah, thanks for the reply. Never looked at it like that. But what if the criteria crops up in the middle of the phrase - running with the 'car' example, 'Jim's CAR Wash'. I guess I'm needing some sort of explode function?

Posted: Wed Nov 23, 2005 7:38 am
by Jenk
If the phrase is a whole word, then the criteria for the SQL should be:

Code: Select all

$sql = "SELECT * FROM table WHERE column LIKE '% {$criteria} %'";
I'm not overly experienced with search algorithms, so I wouldn't be able to assist much if, for example, you wanted to base the search on relevance. I assume you are using MySQL, I shall have a look to see if there are any specific commands for MySQL that will allow you to select on where the criteria matches a whole word (this will then allow you to select words at the end or beginning of a sentance, or with a trailing comma etc.)

EDIT:

I've already found something that may be of interest for you:

http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html

Hope this helps :)

REGEX to query my database

Posted: Wed Nov 23, 2005 8:56 am
by zoe
Thank you so much for all that Jenk. I was wondering how the relevance ranking thing worked any way, so that article you found is a major bonus! :P

Posted: Fri Nov 25, 2005 11:32 am
by zoe
Have more or less got what I was after from Jenck's original post without needing regex at all - just wasn't paying enough attention to the placement of spaces.

Code: Select all

$query = "SELECT * FROM business_directory WHERE 
Name LIKE '% $businesstype %' OR Name LIKE '$businesstype %' OR Name LIKE '% $businesstype'";
My question now is: Is it possible, and what would the correct syntax be to also match '$businesstype'(s), thereby finding designers when a search is carried out for 'designer'?

I should move this to Databases forum shouldn't I? Will do that now.