php - Store URL with wildcards in MySQL and return matches -

i'm writing php router, lookups page's id in database such query

select `id` `pages` '/about/test' regexp `url` order `url` 

from such table

id  type_id url             name    lookup   1   0       /about/[^/]*    null    null 2   1       /about/company  null    null 

so gets id 2 on '/about/company' , id 1 on /about/whatever on first row of result

i'm looking best way retrieve wildcard values mysql, e.g. id of '/about/[^/]+' entry , 'test' second argument when passing '/about/test'; , id when passing '/about/company'

simply, want variadic number of columns, first column return id , others - wildcard values in right order

let me assume have flag specifies if wildcard pattern or simple comparison.

if want matches, can do:

select `id` `pages` '/about/test' regexp `url` , url_has_wildcard = 0 union select `id` `pages` '/about/test' regexp `url` , url_has_wildcard = 1 ,       not exists (select 1                   pages                   '/about/test' regexp `url` , url_has_wildcard = 0                  ); 

you can define such flag using -- else -- regular expression. say, this:

where '/about/test' regexp `url` ,       (url regexp '^[a-za-z0-9/]*$') -- or whatever valid characters 

if want 1 row returned, can do:

select `id` `pages` '/about/test' regexp `url`  order (url regexp '^[a-za-z0-9/]*$') desc limit 1;