Moved voluntarily to databases: REGEX to query my database

Any questions involving matching text strings to patterns - the pattern is called a "regular expression."

Moderator: General Moderators

Post Reply
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Moved voluntarily to databases: REGEX to query my database

Post 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%')";
Last edited by zoe on Fri Nov 25, 2005 11:49 am, edited 2 times in total.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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 :)
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

REGEX to query my database

Post 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?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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 :)
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

REGEX to query my database

Post 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
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post 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.
Post Reply