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

_"I MUSTN'T CHANGE URLS IN ANY WAY"_ you've really dug a difficult hole for yourself. Would have been much easier as `/store-name/location-name` — Phil, Jan 29 '19 at 01:56
@Phil, I wasn't digging this whole myself, I inherited it :-(. I agree with you, and when I was "digging" on some other project I did exactly what you said. ;-) — Mirjana Katalina, Jan 29 '19 at 01:59
Have you tried anything yet? If so, you should add your attempts to your question. I have some ideas but none of them are particularly efficient though sifting through a million possible combinations probably wouldn't be _too_ taxing on your DB — Phil, Jan 29 '19 at 02:00
@Phil I have separate table called urls with already joined combinations of stores and locations. It is hard to maintain, any change of address, name, new stores, new cities...So, I hoped some php/mysql guru might help :-D — Mirjana Katalina, Jan 29 '19 at 02:03
Why not make it a [`VIEW`](https://dev.mysql.com/doc/refman/8.0/en/create-view.html)? That way it's always up-to-date — Phil, Jan 29 '19 at 02:05
Also, if this is for maintaining some sort of derived table, why does it need to be efficient? — Phil, Jan 29 '19 at 02:11
@Phil view sounds interesting, I've never used it before. How could it be implemented in this example? It must be efficient since it checks what sort of page to return or to give 404 (website) — Mirjana Katalina, Jan 29 '19 at 02:19
Sounds like you need to [edit your question](https://stackoverflow.com/posts/54412715/edit) with **all** the details. Include the process / logic flow as I'm confused about whether this is some real-time, request-based solution you need or some offline _"update this `URLS` table"_ task. Where do the request URLs come from? — Phil, Jan 29 '19 at 02:23

score 2 · Accepted Answer · answered Jan 29 '19 at 02:47

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

Phil · Answer 2 · 2019-01-29T03:10:37.970

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

You have a stores table with column slug_stores
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...

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

Get all the URL slugs for a particular store

SELECT slug FROM store_location_urls WHERE store_id = ?

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.

score -1 · Answer 3 · answered Jan 29 '19 at 01:53

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 = ?

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

3 Answers3