I have this $value= 24153;
i have a field the can hold one or more values like this {"id":"2","value":["3"]} or like this

קוד:

{"id":"2","value":["3","4"]} 

or

קוד:

 {"id":"2","value":["3","4","2"]}

i have this rejex that works fine, but returns if the ONE value exist i need to improve this regex to the case there are more then one value in field

קוד:

  REGEXP BINARY \'(.*{"id":"2","value":\["[^\"]*['.$value.'][^\"]*",?)+\]}.*\'

I wouldn't call this an SQL question, rather than a general programming question. You're asking about how to write a regular expression, not an SQL.

You didn't ask, but I will answer for your own sake. It seems to me that you have an X-to-many relationship that you implemented by taking the data and serializing it, then throwing it to the DB.

This is wrong in SO MANY ways. It is highly inefficient - so - it's slow; You cannot query by the data; You cannot do indices (indexes) on it; You cannot verify data integrity because the DB can't read these things. ‏You can't add or remove data without reading all existing data, modifying it on the application level, then updating it with the new data (and lock the table while doing so...). The more you grow, the more horrible your performance would get.

What you SHOULD do is create a new table, that has two rows, id and value, and per each 'value' in your current data structure, add a row to the table, which would contain the id and the specific value. Then you could very easily query for 'all id's that have value X' or 'all the values of Y id's', and so on. The "id", could be used in your original table to denote the same set of data you currently use a serialized string to store...

If those id's are not unique (i.e. you can have an id = 5 in two places, with a different set of values), you should use a linking table for uniqueness; i.e. add a new table with just an autoincrement; Per each insert of a new value, insert a row there, get its last_insert_id(), use that in your original table as the ID for the unique data, and have the two-column table I talked about before, be a three-column table, with the third column being with the number you got from the auto_increment.

Granted, this is more complicated than just serialize()ing your data and throwing it as a string, but, believe me, you would thank me later.

Good luck

נמאס לכם לזכור סיסמאות? לחצו כאן!