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.

Answer 2

you can use the query in mysql like

SELECT id FROM TABLE_BRANDS WHERE name like "Dell%";

Answer 3

It is much better for you to handle within Database if you can come up with correct query.

You may try the following query:

SELECT ti.id, ti.name, tb.id as brand_id
FROM table_items ti LEFT JOIN table_brands tb ON ti.name LIKE CONCAT(tb.name, '%');

Answer 4

I find what you want to do is similar to search and it's best with MySQL.

Following query to get all similar item with your wildcard.

 SELECT * FROM brand_table WHERE name LIKE "Apple%"

All names beginning with apples will appear. I hope you catch the drift here

Answer 5

This query will do the job.

UPDATE table_items
       JOIN table_brands
       ON  table_items.name LIKE CONCAT('%', table_brands.name ,'%')
SET    table_items.brand_id = table_brands.id;

Answer 6

Here is the simplest query without to dive into joins:

UPDATE table_items SET brand_id = (SELECT id FROM table_brands WHERE `Name` = SUBSTRING_INDEX(table_items.name,' ',1))

Answer 7

The simplest way to do it it's to use SQL directly.

The simplest query that do the job is:

UPDATE table_items
    SET brand_id = (SELECT id FROM table_brands
                    WHERE table_items.name LIKE CONCAT(name,'%'))