mysql / php: find starting string within string

Question

I have a list of items (Brand + productModel) in a mysql table, and I have a list of Brands in another table.

example:

table_items

|id| name             | brand_id  |
-----------------------------------
| 1| Apple Mac 15     |           
| 2| Dell Laptop NXY  | 
| 3| HP Tablet 15     |
| 4| Apple Laptop AA  | 
| 5| Dell Tablet VV   | 
| 6| HP Desktop  XYZ  |

table_brands

|id| name  | 
------------
| 1| Apple | 
| 2| Dell  | 
| 3| HP    |

I inherited the table_items from a previous project so I need to detect the brand name in table_items,if the brand is present, then add the brand id to the item column brand_id (currently empty)

so the ideal output would be

|id| name             | brand_id  |
-----------------------------------
| 1| Apple Mac 15     | 1   
| 2| Dell Laptop NXY  | 2
| 3| Dell Tablet 15   | 2
| 4| Apple Laptop AA  | 1
| 5| HP Tablet VV     | 3
| 6| HP Desktop  XYZ  | 3

so I don't know if I should use PHP or can be done in MySQL directly... and if PHP how to I detect the matching strings?

Answer 1

You can join both table using like and update as needed.

UPDATE `table_items` TI 
INNER JOIN table_brands TB 
    ON TI.name LIKE CONCAT(TB.name, '%')
SET TI.brand_id = TB.id

Note: INNER JOIN will only update those fields which are matched.