I need the least expensive way to check if my url slug is formed from the values from two separate columns from two separate tables.
I will use dummy example with stores and locations to make this more human readable. I have the following url:
www.domain.com/store-location
This could be, for example:
www.domain.com/three-words-store-chicago or
www.domain.com/nicestore-new-york-or-some-neighbourhood-with-more-words or
www.domain.com/oneword-oneword
(you get the idea)
Stores are located in table called stores, and locations in the table called locations. All the combinations are possible in theory. So, I would need some clever mysql query combined with php which will check if my slug (what goes after .com/) is the exact combination of store+location. So, to make it more descriptive:
url: www.domain.com/cool-store-los-angeles
Check is there "cool-store" in the table stores.slug_stores and is there "los-angeles" in the table locations.slug_location. The number of words of both is undefined as you can see above, so I don't have any possible delimiter.
IT MUST BE THE LEAST EXPENSIVE WAY because both tables tables have around 1000 lines. PLEASE HELP AND THANK YOU GUYS!
ps. IMPORTANT: I MUSTN'T CHANGE URLS IN ANY WAY
Edit: This is real project, website. Depending on the url i.e. slug I return some view with data. So I need to check for www.domain.com/nicestore-nicecity if Nicestore and Nicecity exist in tables stores and locations, and if not, or if anything else is there like www.domain.com/nicestore-nicecityBLABLA to kill that page with 404. Otherwise, if there is Nicestore and Nicecity to return some page populated with related data. I tried so far to make separate table with formed slugs like "nicestore-nicecity" and to use it for queries "SELECT whatever FROM slugs WHERE whatever = 'nicestore-nicecity' and if there is line return whatever I need to show the page ... Simplified... But, this separate table is hard to maintain. If nicestore moves to uglycity, or if it changes name, or if you add a new store or new city. I hope I was more clear now ;-)
I'm assuming that you don't have any id values on which to JOIN your tables, and that you don't have the ability to create such values. In that case, since your store/location combination could be as short as oneword-oneword, the first and last words of the slug are about as much as you can search on. You can extract the start and end parts of the slug using SUBSTRING_INDEX and use that to narrow the set of matches in each table before you try and compare the whole string. In my example, I'm using an SQL variable to store the slug:
SET @store = 'cool-store-los-angeles'
SELECT *
FROM (SELECT *
FROM stores
WHERE store LIKE CONCAT(SUBSTRING_INDEX(@store, '-', 1), '%')) s
JOIN (SELECT *
FROM locations
WHERE location LIKE CONCAT('%', SUBSTRING_INDEX(@store, '-', -1))) l
WHERE CONCAT(s.store, '-', l.location) = @store
This will return all data associated with cool-store-los-angeles assuming that such a store exists.