How to check if some string is combination of values from two separate columns i.e tables PHP MySql

Question

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 ;-)

Answer 1

Here's what I know about your system...

1. You have a stores table with column slug_stores
2. You have a locations table with column slug_location

I'm going to assume that each table has an id column of some type. I'm also going to assume they have a many-to-many relationship using a third junction table, something like

CREATE TABLE store_locations (
  store_id <type>,
  location_id <type>,
  PRIMARY KEY (store_id, location_id),
  FOREIGN KEY (store_id) REFERENCES stores(id),
  FOREIGN KEY (location_id) REFERENCES locations(id)
);

If you don't have this sort of relationship defined, I really don't know how you maintain your store locations.

---

What I would suggest is creating a VIEW to calculate and represent your URLs. For example...

CREATE VIEW store_location_urls AS
SELECT
  sl.store_id,
  sl.location_id,
  CONCAT_WS('-', s.slug_stores, l.slug_location) AS slug
FROM store_locations sl
INNER JOIN stores s ON sl.store_id = s.id
INNER JOIN locations l ON sl.location_id = l.id;

Now you can use this view to do the following...

1. Check if a request URL slug is valid

   SELECT store_id, location_id FROM store_location_urls WHERE slug = ?
   

   If this returns a record, you can then further query the stores and locations tables for whatever extra data you need to render your page (or even just join them in the original query). Otherwise, use

   http_response_code(404);
   

2. Get all the URL slugs for a particular store

   SELECT slug FROM store_location_urls WHERE store_id = ?
   

3. Similarly, you could get all the URL slugs for a particular location

---

An extra note... due to concatenating strings, any indexes you have on stores.slug_stores and locations.slug_location will be useless with the above VIEW. The alternative is to use a real derived table (like what you currently have) and maintain it with triggers.

Answer 2

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.

Demo on dbfiddle

Answer 3

I think you can query like following in mysql and if do check in php afterwards. From your description, it doesn't sound like there is any join possible between those tables so, union is required, i think.

select col1,col2 from stores where slug_stores = ? union select col1,col2 from locations where slug_location = ?