Mysql / php: find start line in line

Question

I have a list of elements (Brand + productModel) in the 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 table_items from a previous project, so I need to identify the brand name in table_items if the brand is present, and then add the brand ID to the product column brand_id (currently empty)

, so the ideal solution 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 executed directly in MySQL ... and if PHP, how to determine the corresponding lines?

Answer 1

You can join both tables using, for example, 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 fields that match.

Answer 2

you can use query in mysql like

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

Answer 3

I find what you want to do, it looks like a search and best of all in MySQL.

The following query to get all similar elements with your template.

  SELECT * FROM brand_table WHERE name LIKE "Apple%" 

All names starting with apples will appear. Hope you catch the drift here.

Answer 4

You are much better off processing the database if you can find the right query.

You can 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 5

This request will complete the task.

 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 a simple query without diving into unions:

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

Answer 7

The easiest way to do this is to use SQL directly.

The simplest query to do the job:

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