Clever Geek Handbook

PHP + MySQL: Search Using Wildcards

So, I have the following lines in the DB:

1 | / users /

2 | / Users / Administrators /

3 | / Users / Administrators / *

4 | / Users / Administrator / Microphone /

5 | / Users / Administrator / Steve / Documents /

The input url is / users / admin / steve / and the goal is to find a match url from the database.

I want to return # 3 as a valid string since the wildcard "*" indicates that anything can be used instead of an asterisk. What would be the most efficient way to do this?

Here are my initial thoughts, but I'm sure they can be improved:

  • Make a request to see if there is an exact url
  • If there is no match, then extract all lines with "*" as the last character in reverse order (which is why more specific URLs are prioritized).
  • For each line, if it (minus "*") matches the input url, return it
  • If nothing is found we will be SOL
+1


source to share


2 answers


This is how I would do it:

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


REPLACE () is to change the substitution style pattern in the equivalent regex pattern.

+2


source


If I understand correctly, something like this should work:

SELECT COALESCE (



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

)

0


source






More articles:


All Articles