How to extract one value from imploded array in MySQL row

Question

I'm using implode to insert few values into one row in MySQL database.

implode(' ', $_POST['tag']);

Assuming that I have table named product with row named tags with 3 different values that inserted inside like this:

usb adapter charger

I have tried using this method using like operator (%), but that didn't worked.

$sql = "SELECT * FROM product WHERE tags='%usb%'";

How can I extract only one value from the imploded array using WHERE in mysql query?

https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad may be worth a read. — Nigel Ren, Feb 07 '20 at 18:27
"I'm using implode to insert few values into one row" Let me stop you right there. Re-design your database to store those values in another table with columns id, productid, and tagid. This will not only remove this current issue, but save you many other headaches. — Patrick Q, Feb 07 '20 at 18:31
@PatrickQ I'm inserting tags to one row, meaning one product might have couple of tags, I'm not inserting the entire table values into one row. — Yotam Dahan, Feb 07 '20 at 18:33
@YotamDahan I understand that. And what you're doing is quite ill-advised. See the link provided by Nigel. — Patrick Q, Feb 07 '20 at 18:33
@PatrickQ But I assume it's doable right? I'm using this method just to filter tags in my system, there wont be any use for that column if I get it right. — Yotam Dahan, Feb 07 '20 at 18:42
@YotamDahan Let's say you tag some things with "pan" because, well, they are pans. Let's say you tag some other things as "panther" because they are shirts or mugs or whatever with a panther on them. When you search for pans, do you also want to get all products with panthers on them? Your current strategy, assuming you change `=` to `LIKE` will have that result. Searches will also be slow(er), as they will not take advantage of indexes. — Patrick Q, Feb 07 '20 at 18:46
@PatrickQ Alright then, I'll change the database structure, but how do you suggest I'll connect each tag to his product? — Yotam Dahan, Feb 07 '20 at 18:50
@YotamDahan Exactly the way my first comment showed. You'll have a `products` table, a `tags` table, and a `product_tag` table (or some other similar name). `products` has `id`, `name`, `description`, etc. `tags` has `id`, and `name`. `product_tag` has `id`, `productid`, and `tagid`. — Patrick Q, Feb 07 '20 at 18:55
Does this answer your question? [A better way to search for tags in mysql table](https://stackoverflow.com/questions/39239521/a-better-way-to-search-for-tags-in-mysql-table) — Patrick Q, Feb 07 '20 at 18:58
There are a number of other related questions on here including [this one](https://stackoverflow.com/questions/14528763/searching-a-mysql-database-for-tags) — Patrick Q, Feb 07 '20 at 18:58

FanoFN · Accepted Answer · 2020-02-08T04:57:43.063

I agree with the comments about re-designing the database. At first read it seems that using LIKE would definitely get the result you want but after reading @Patrick Q's pan - panther example, it makes a lot sense that LIKE is not really a good solution. There are ways to get exactly the tag string you're looking for but it may hurt the performance and the query will be longer and complex. Hence the following are to demonstrate how the query would look like with your current tags data value:

MySQL query:

SELECT tags,
       SUBSTRING_INDEX(SUBSTRING_INDEX(tags,' ',FIND_IN_SET('usb',REPLACE(tags,' ',','))),' ',-1) v 
FROM   mytable
HAVING v = 'usb';

As you can see, there are a few functions being used just to get the exact string from the data cell. Since your example data was separating with spaces and FIND_IN_SET identify value separation by comma, REPLACE take place on the tags column first to replace spaces with comma. Then with SUBSTRING_INDEX twice to get the string using the location extracted in FIND_IN_SET. Finally at the end HAVING to get only the tag you're looking for.

Further demo here : https://www.db-fiddle.com/f/joDa7MNcQL2RakTgBa7qBM/3

How to extract one value from imploded array in MySQL row

1 Answers1