[SOLVED] Pattern Matching Query

[GeneralBill]

I would appreciate it if someone could help me come up with a query.  Let's pretend that I have a schools table and that I want to fetch an id given a name.  However, I don't have the exact name.  Instead, I have a name that has all the non alphanumeric / whitespace characters stripped from it.

 

SCHOOLS TABLE

id, name

1, Plantation High-Schools

2, Cat & Dog Schools

3, Culinary / Cooking Schools

 

I have this information available:

$name = plantation high schools

$name = cat dog schools

$name = culinary cooking schools

 

Let's say that I wanted to get the id for $name.  What would the query look like?  I assume it might look something like this:

 

SELECT id FROM schools WHERE name RLIKE '';

[paul2463]

$query = "SELECT `id` FROM `schools` WHERE `name` LIKE '$name'";

[fenway]

With wildcards LIKE is the same as =.

[GeneralBill]

$query = "SELECT `id` FROM `schools` WHERE `name` LIKE '$name'";

 

The above query unfortunately is not what I'm looking for.

 

$name = "cat dog schools";

$query = "SELECT `id` FROM `schools` WHERE `name` LIKE '$name'";

 

That would not return any results.  I want it to return id = 2.

 

I probably need to use some kind of REGEXP or RLIKE pattern.  Anyone else out there have any ideas?

[GeneralBill]

I figured it out.  I just needed to format this:

 

$name = "cat dog schools";

 

To look like this:

 

$name = "^cat[^[:alnum:]]+dog[^[:alnum:]]+schools$"

 

Then, this will work:

 

select id from schools where name rlike '$name';