Record Multiple Numbers in a Field in the Database

Question

Guys I have a column in a table in the database that is called "synchronizes" inside it I want to save several numbers separated by comma, and then do a check on a page as follows, that if there exists between those commas a number that is equal to the number passed in the URL it does a function for me, how I can increment a new number with the comma in this field "synchronizes" and how do I check between commas if this number is equal to the one passed in the URL?? Thank you so much

Answer 1

Use JSON format field.

Support Mysql for JSON format, which is perfect for your purpose.

Basically:

You will capture the numbers, put in an array and convert to JSON, after that will save in the bank.

$numbers = [1,2,3];
$json = json_encode($numbers);
// save to db

Basically. Mysql’s native support for field json, as you can see in the documentation:

https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

After saving, just run the querys with JSON_CONTAINS to check whether the number given is present in the field.

See an example of a query with JSON_CONTAINS

I first created a json and then asked if the number 3 was present in json.

See the documentation, implementation will be easy.

Answer 2

First you should create a column that supports text. Although they are multiple numbers, the way you want to store, it is impossible to use a numerical field.

  CREATE TABLE xxx (
        ....
        sincrozina VARCHAR(255) //defina o tamanho necessário
        ....);

If you want to add a value in this field, it is necessary to have a record (how you will create it, I don’t know). To increment the field with a value, use the following SQL.

 UPDATE xxx SET sincroinza = CONCAT(sincroinza, ",", novo_número) WHERE ....

novo_numerois the new value you want to enter. To check if a certain number is in this list, you should do it as follows:

SELECT * FROM xxx WHERE sincroniza LIKE %numero_buscado%

Where numero_buscado is the number you want to verify. If this query returns the record where the list of numbers is, is why the value is in the list.

I only posted snippets of SQL. Since you provided no code, and little information, there is no way to help you with other implementation details, so you will have to adapt these Sqls to your code, to reach the solution.