How to do a search in the database by a numerical range using BETWEEN and similar methods in PHP?

Question

PHP

Hello!
The question is:
I have a database and a column called "xfields" which has a content structure like this:
------| --xfields---
ID1--| price|7510
------| -------------
ID2--| price|1500
------| -------------
ID3--| price|10000
------| -------------

How to formulate a query using foreach to display those records that are in the desired price range?
For example, in the "Price from" field, I specify 8000 and in the "Price to" field, I specify 1000000. In order for the query to return only those id that fall within the specified price range of the price | ?.
The nuance is that right there in the column, separated by commas, there can be another value, for example:
price|1000, color|balck, bg|dark and so on.


This is what the xfields column looks like.

Now I use the following range search option:

$where[] = "ABS(SUBSTRING_INDEX(SUBSTRING_INDEX(xfields, '{$key}|', -1 ), '||', 1))>={$value[0]} AND ABS(SUBSTRING_INDEX(SUBSTRING_INDEX(xfields, '{$key}|', -1), '||', 1))<={$value[1]}";

$key is the name of the value(price);
But this method is extremely inaccurate and unstable. Are there alternatives?

Answer 1

Are there alternatives?

Of course have.
The best alternative is to normalize the base. Attributes that most products have, make columns in the main table. Make additional attributes a separate table (product_id, attribute_id, value).