php - Finding inner join mysql with array

Question

I have two tables, First one is products where it has list of products with some specifications, in the other hand I have a table with clients and what type of product they want, they might want a product in any town of a list exactly as explained in the following tables,

Products Table like

| id |   owner  | userid | city | town | status | price |
| 1  | jon spee |   10   |  10  |  4   |   0    | 10500 |
| 2  | Hiss Roe |   10   |  7   |  9   |   0    | 20000 |
| 3  | John Smi |   10   |  10  |  12  |   0    | 10000 |

Clients Table like

| id | fullname | userid | city | towns | status | price |
| 1  | name 1   |   10   |  10  |4,8,6,2|   0    | 20000 |
| 2  | name 2   |   10   |  7   | 7,2,9 |   0    | 25000 |
| 3  | name 3   |   10   |  10  |  1    |   0    | 20000 |

MySQL Query :

SELECT *
FROM clients
INNER JOIN products
ON (
clients.userid = products.userid AND
clients.price >= products.price AND
clients.city = products.city AND
clients.status = products.status

I want it to check also in towns like for each town it executs this query (dynamically),

(products.town LIKE '%4%' OR products.town LIKE '%8%' OR products.town LIKE '%6%' OR products.town LIKE '%2%')

标签： phpmysqlsqlcsvinner-join

Answer 1

Your primary effort should go into fixing your data model. Don't store multiple integer values in a string column. You should have a separate table to store the relation betwen clients and towns, which each tuple on a separate row.

That said: for your current design, you can join on find_in_set():

on 
    clients.userid = products.userid 
    and ...
    and find_in_set(product.town, client.towns)