mysql decimal() Numeric value out of range: 1264

Question

I have a silly problem with decimal and mysql. Mysql version is: "10.4.19-MariaDB - Source distribution". I cannot understand the right setting for this column datatype. I would like to store and then display a number like this one: 325,130. So i create a column with datatype like decimal(6,3). As far as i can understand it means 3 digits before the comma (on the left) and 3 digits after the comma (on the right) but it doesn't work..

what is the right syntax for this column datatype? or should i use float instead of decimal?

that's the code used to store data into db:

$datas = ['rif' => $rif,
        'contratto_id' => $contratto_id,
        'categoria_id' => $categoria_id,
        'tipologia_id' => $tipologia_id,
        'tipologiacomm_id' => $tipologiacomm_id,
        'classe_energetica_id' => $classe_energetica_id,
        'kwh' => $kwh,
        'condizioni_id' => $condizioni_id,
        'statorogito_id' => $statorogito_id,
        'box_id' => $box_id,
        'piano_id' => $piano_id,
        'riscaldamento_id' => $riscaldamento_id,
        'giardino_id' => $giardino_id,
        'ipe' => $ipe,
        'note_classe_energetica' => $note_classe_energetica,
        'prezzo_vendita' => $prezzo_vendita,
        'prezzo_affitto' => $prezzo_affitto,
        'trattativa_riservata' => $trattativa_riservata,
        'mq' => $mq,
        'numero_locali' => $numero_locali,
        'bagni' => $bagni,
        'anno_costruzione' => $anno_costruzione,
        'mq_box' => $mq_box,
        'totale_piano' => $totale_piano,
        'spese_condominiali' => $spese_condominiali,
        'mq_giardino' => $mq_giardino,
        'ce_id' => $ce_id,
        'ci_id' => $ci_id,
        'visualizzazioni' => $visualizzazioni,
        'data_inserimento' => $data_inserimento,
        'data_agg' => $data_agg,
        'id' => $id
];

$sql = "UPDATE annuncio SET rif = :rif,
contratto_id = :contratto_id,
categoria_id = :categoria_id,
tipologia_id = :tipologia_id,
tipologiacomm_id = :tipologiacomm_id,
classe_energetica_id = :classe_energetica_id,
kwh = :kwh,
condizioni_id = :condizioni_id,
statorogito_id = :statorogito_id,
box_id = :box_id,
piano_id = :piano_id,
riscaldamento_id = :riscaldamento_id,
giardino_id = :giardino_id,
ipe = :ipe,
note_classe_energetica = :note_classe_energetica,
prezzo_vendita = :prezzo_vendita,
prezzo_affitto = :prezzo_affitto,
trattativa_riservata = :trattativa_riservata,
mq = :mq,
numero_locali = :numero_locali,
bagni = :bagni,
anno_costruzione = :anno_costruzione,
mq_box = :mq_box,
totale_piano = :totale_piano,
spese_condominiali = :spese_condominiali,
mq_giardino = :mq_giardino,
ce_id = :ce_id,
ci_id = :ci_id,
visualizzazioni = :visualizzazioni,
data_inserimento = :data_inserimento,
data_agg = :data_agg
 WHERE id = :id";

$stmt = $pdo->prepare($sql);
if(!$stmt->execute($datas)){
    throw new Exception(sprintf(
        "Errore esecuzione PDO: %s", implode(',', $pdo->errorInfo())
    ));
}

this is the structure of the table:

    id Primaria     int(11)             No  Nessuno         AUTO_INCREMENT
2   rif     text    utf8_general_ci         Sì  NULL        
3   contratto_id    int(11)             Sì  NULL        
4   categoria_id    int(11)             Sì  NULL        
5   tipologia_id    int(11)             Sì  NULL        
6   tipologiacomm_id    int(11)             No  Nessuno         
7   classe_energetica_id    int(11)             Sì  NULL        
8   kwh     int(11)             Sì  NULL        
9   condizioni_id   int(11)             Sì  NULL        
10  statorogito_id  int(11)             Sì  NULL        
11  box_id  int(11)             Sì  NULL        
12  piano_id    int(11)             Sì  NULL        
13  riscaldamento_id    int(11)             Sì  NULL        
14  comune_id   int(11)             Sì  NULL        
15  provincia_id    int(11)             Sì  NULL        
16  regione_id  int(11)             Sì  NULL        
17  giardino_id     int(11)             Sì  NULL        
18  ipe     decimal(6,3)            Sì  NULL        
19  note_classe_energetica  text    utf8_general_ci         Sì  NULL        
20  prezzo_vendita  decimal(10,2)           Sì  NULL        
21  prezzo_affitto  decimal(10,2)           Sì  NULL        
22  trattativa_riservata    int(11)             Sì  NULL        
23  mq  int(11)             Sì  NULL        
24  numero_locali   int(11)             Sì  NULL        
25  bagni   int(11)             Sì  NULL        
26  anno_costruzione    int(11)             Sì  NULL        
27  mq_box  int(11)             Sì  NULL        
28  totale_piano    int(11)             Sì  NULL        
29  spese_condominiali  decimal(10,2)           Sì  NULL        
30  mq_giardino     int(11)             Sì  NULL        
31  ce_id   int(11)             Sì  NULL        
32  ci_id   int(11)             Sì  NULL        
33  testo_ita   text    utf8_general_ci         Sì  NULL        
34  testo_fr    text    utf8_general_ci         Sì  NULL        
35  data_inserimento    date            Sì  NULL        
36  data_agg    date            Sì  NULL        
37  ann_visibile    int(11)             Sì  NULL        
38  indirizzo   text    utf8_general_ci         Sì  NULL        
39  civico  int(11)             Sì  NULL        
40  cap     int(11)             Sì  NULL        
41  indirizzo_visibile  int(11)             Sì  NULL        
42  visualizzazioni     int(11)             Sì  NULL

`or should i use float instead of decimal?` if you need decimals (like for currencies) then no. — t.niese, Sep 04 '21 at 10:35
Decimal(6,3) means a six digit number with three digits to the right of the decimal point. Eg 123.456. I can see this might cause confusion in some European countries that use a decimal comma, and write that same number as 123,456. Your example would need a DECIMAL(6,0) column. — Tangentially Perpendicular, Sep 04 '21 at 10:35
Could you please show the query you use to insert the `325,130`? Using `,` instead of `.` results in a 1265 error for me. And inserting `325.130` (with a `.`) works, if the column is `decimal(6,3)` — t.niese, Sep 04 '21 at 10:41
@Tangentially Perpendicular if I put decimal(6,0) it doesn't store the comma and the value after it. It just store the number without comma. For example: 123,456 become 123456 — P.Davide, Sep 04 '21 at 10:53
@t.niese no i have to store a number (not money). It is a value that indicate a level and it has 3 digits on the left and 3 on the right. So if I can solve my problem using float I will use it. What do you suggest? — P.Davide, Sep 04 '21 at 10:55
`It is a value that indicate a level` might be that `float` could work. But it also could be that it does not work well for levels. A decimal can represent all possible values in that range. A float might not be able to represent all values with 3 digits before and after the decimal point. — t.niese, Sep 04 '21 at 10:58
Anyhow, show the query you try to use to insert that value and the table structure. That would allow us to tell you what you are doing wrong. As I said inserting `325.130` in a column with the type works `decimal(6,3)` — t.niese, Sep 04 '21 at 10:59
That is some code, but not the final query. Besides that code is missing the actual data that is inserted and the actual structure of the table. Reduce the code to a [mcve] remove everything before `$sql = "UPDATE annuncio` and replace it some dummy data that reproduces the error message. — t.niese, Sep 04 '21 at 11:13
You edited the code once again, but you still didn't providede any data that allows reproducing the problem. And it is still lacking the table structure. — t.niese, Sep 04 '21 at 13:40
@t.niese i add the table structure now, hope this is what you mean. Thanks Davide — P.Davide, Sep 04 '21 at 14:14
I give up. That's my last comment here. And I won't bother with that question any further. I asked you a couple of things, but with each edit, you just targeted one of these with each edit. The question still lacks data that would result (in combination with that table structure) in the error message. — t.niese, Sep 04 '21 at 14:43
@t.niese, sorry my apologize, I don't understand what do you mean (my fault) i have lack in english so i really don't understand what you need to help me... I edited the code inserting, what i guess, is the data structure (took from phpmyadmin). I don't really know what you mean.. do you need the output error from php script? — P.Davide, Sep 04 '21 at 14:52

score 0 · Answer 1 · answered Sep 04 '21 at 15:10

Problem solved. The right column definition for this kind of datatype was decimal(6,3). The problem was in this row:

$ipe = filter_input(INPUT_POST, 'ipe', FILTER_SANITIZE_NUMBER_INT);

changed to:

$ipe = $_POST['ipe'];

solved the problem. Posting the variable with: print_r($ipe); shows that the 'dot' was missing probably due to sanification string.

Hope this could help someone else.

mysql decimal() Numeric value out of range: 1264

1 Answers1