CODEWITHSUNDEEP
phpmysqloptimization
Matt
Matt

Reputation: 3894

PHP+MySQL: searching using wildcards

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

Upvotes: 1

Views: 1417

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562330

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.

Upvotes: 2

dkretz
dkretz

Reputation: 37655

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'

)

Upvotes: 0

Related Questions