PHP+MySQL: searching using wildcards

Question

So, I have the following rows in the DB:

1 | /users/

2 | /users/admin/

3 | /users/admin/*

4 | /users/admin/mike/

5 | /users/admin/steve/docs/

The input URL is /users/admin/steve/, and the goal is to find the URL match from the DB.

I want to return #3 as the correct row, since the wildcard "*" specifies that anything can go in place of the asterisk. What would be the most efficient method for doing this?

Here's my initial thoughts, but I'm sure they could be improved upon:

1. Make a query to see if there's an exact URL match
2. If no matches, then retrieve all rows with "*" as the last character, in reverse order (so the more specific URLs take precedence)
3. For each row, if it (minus the "*") matches the input URL, then return it
4. If nothing is found, then we're SOL

Answer 1

If I understand this correctly, something like this should work:

SELECT COALESCE(

(SELECT whatever FROM your query to see if there is an exact url),
(SELECT next approximation),
'SOL'

)

Answer 2

Here's how I'd do it:

SELECT * FROM mytable AS m
WHERE <input-url> = m.urlpattern
 OR <input-url> REGEXP REPLACE(m.urlpattern, '*', '.*');

The REPLACE() is to change the globbing-style wildcard into an equivalent regular-expression wildcard.