Write numeric topic (1 1.2, 1.2.1) in Mysql with PHP

Question

I have the following problem: I need to record in a database some conditions, which have their item numbers and upload as in this example:

1. Test 1

2. Test 2

   2.1 Testing

I tried to do with numeric(4,2) or float but when a condition happens to have more climbs, for example 2.2.1, it no longer works. Someone can help me?

Answer 1

None of these approaches is the right one. Let’s assume you’re putting the records like this:

numero | nome
1      | Teste 1
2      | Teste 2
2.1    | Teste
2.1.1  | Outro teste

The solution would be to create two new columns, one to be the new primary key and one with the parent record id:

id | id_pai | numero | nome
1  | null   |      1 | Teste 1
2  | null   |      2 | Teste 2
3  | 2      |      1 | Teste
4  | 3      |      1 | Outro teste

Obviously, the countryside id_pai would be a foreign key to the field id from the same table. The field id would be the primary key. The number is formed by traversing the record father, grandfather, etc until arriving in a null and concatenate the field values numero that are found.