Error: 1265 SQLSTATE: 01000 (WARN_DATA_TRUNCATED)

Question

Well, I want to practice with php and mysql, so I want to make a bbdd and a GUI to control it. Here are the code where I have the problem:

MySql (v5.6.12):

CREATE TABLE IF NOT EXISTS `personaje` (

`id_personaje` int(3) NOT NULL AUTO_INCREMENT,
`nombre` varchar(30) NOT NULL,
`servidor` varchar(25) NOT NULL,
`nivel` int(3) NOT NULL,
`faccion` varchar(10) NOT NULL,
`clase` varchar(25) NOT NULL,
`raza` char(30) NOT NULL,
`profesion1` varchar(20) NOT NULL,
`nivel1` int(4) NOT NULL,
`profesion2` varchar(20) NOT NULL,
`nivel2` int(4) NOT NULL,
`nivel_coc` int(4) NOT NULL,
`nivel_pes` int(4) NOT NULL,
`nivel_arqu` int(4) NOT NULL,
`nivel_prim` int(4) NOT NULL,

PRIMARY KEY (`id_personaje`),
UNIQUE KEY `raza` (`raza`),
UNIQUE KEY `clase` (`clase`),
UNIQUE KEY `prof1` (`profesion1`),
UNIQUE KEY `prof2` (`profesion2`),
UNIQUE KEY `faccion` (`faccion`)
) 
ENGINE=InnoDB 

PHP(v5.4.12):

    <?php
   
    
    //Recoger los datos que llegan
    
    $nombre = $_POST['nombreChar'];
    $raza = $_POST['razaChar'];
    $clase = $_POST['claseChar'];
    $servidor = $_POST['servidorChar'];
    $faccion = $_POST['faccionChar'];
    $nivel = $_POST['nivelChar'];
    settype($nivel,"int");
    $prof1 = $_POST['prof1Char'];
    $lvlpr1 = $_POST['lvlpr1Char'];
    $prof2 = $_POST['prof2Char'];
    $lvlpr2 = $_POST['lvlpr2Char'];
    $nivel_coc = $_POST['nivel_cocChar'];
    $nivel_arqu = $_POST['nivel_arquChar'];
    $nivel_pes = $_POST['nivel_pesChar'];
    $nivel_prim = $_POST['nivel_primChar'];
    $muestra = gettype($nivel);
    
     //Conectandonos con la base de datos
   $conexion = mysql_connect(/* ... */);
    mysql_select_db (/* ... */, $conexion) OR die ("No se puede conectar");
           
    //Comprobar que no haya otro personaje repetido
   /* $Consulta_per = "SELECT nombre, servidor FROM personaje WHERE nombre =
        '".$nombre."' && servidor = '".$servidor."'";
    $busqueda = mysql_query($Consulta_per,$conexion) or die ("Error en busqueda " . mysql_error());       
    if (!$busqueda)
        */
    $res = mysql_query("INSERT INTO personaje (nombre, servidor, nivel, faccion, 
        clase, raza, profesion1, nivel1, profesion2, nivel2, nivel_coc, 
        nivel_pes, nivel_arqu, nivel_prim) VALUE ('$nombre','$servidor',
            '$nivel,','$faccion','$clase','$raza','$prof1','$lvlpr1',
                '$prof2','$lvlpr2','$nivel_coc','$nivel_pes','$nivel_arqu',
                    '$nivel_prim')",$conexion)
    or die ("No se pudo insertar " . mysql_error() ." ". $nivel . $muestra );// this line show the error
    echo "Insertado con exito";
    mysql_close($conexion);
    
    ?>

The form that comes is:

    <FORM method="POST" action="crear-personaje.php">
        Nombre <INPUT type="text" name="nombreChar" id="nombreChar" value="Nombre"></br>
        Raza <INPUT type="text" name="razaChar" id="razaChar" value="Raza"></br>
        Facción <INPUT type="text" name="faccionChar" id="faccionChar" value="Facción"></br>
        Clase <INPUT type="text" name="claseChar" id="claseChar" value="Clase"></br>
        Servidor <INPUT type="text" name="servidorChar" id="servidorChar" value="Servidor"></br>
        Nivel <INPUT type="number" name="nivelChar" id="nivelChar" value="1"></br>
        Profesión 1 <INPUT type="text" name="prof1Char" id="prof1Char" value="Profesion1"></br>
        Nivel de profesión 1 <INPUT type="text" name="lvlpr1Char" id="lvlpr1Char" value="1"></br>
        Profesion 2 <INPUT type="text" name="prof2Char" id="prof2Char" value="Profesion2"></br>
        Nivel de profesion 2 <INPUT type="text" name="lvlpr2Char" id="lvlpr2Char" value="1"></br>
        Nivel cocina<INPUT type="text" name="nivel_cocChar" id="nivel_cocChar" value="1"></br>
        Nivel pesca<INPUT type="text" name="nivel_pesChar" id="nivel_pesChar" value="1"></br>
        Nivel primeros auxilios<INPUT type="text" name="nivel_primChar" id="nivel_primChar" value="1"></br>
        Nivel arqueología<INPUT type="text" name="nivel_arquChar" id="nivel_arquChar" value="1"></br>
        
        <INPUT type="submit" NAME="enviar" VALUE="Dar de alta!" id="enviar">
    
    </FORM>

Apache version 2.4.4

Everything must be ok, but... no. When I tried to insert a integer value (like 87 or 1) in the "nivel" field, mysql give me the next error:

"No se pudo insertar Data truncated for column 'nivel' at row 1 1integer", like the comment I wrote in the code.

("No se pudo insertar" means "Cannot insert").

As you can see, I forced the variable $nivel as integer, and PHP recognizes it correctly (that's the reason I put $nivel and $muestra in the die sentence). I tried to change the type "nivel" variable from int (3) to Varchar (3) in MySql, and let me introduce the character, but... I prefer use the type int (the level of a character ever is a integer, obviously).

Why does MySql give me this error? What can I do to solve this?

Answer 1

In your INSERT SQL you've got '$nivel,' - note the comma inside the quotes:

 $res = mysql_query("INSERT INTO personaje (nombre, servidor, nivel, faccion, 
        clase, raza, profesion1, nivel1, profesion2, nivel2, nivel_coc, 
        nivel_pes, nivel_arqu, nivel_prim) VALUE ('$nombre','$servidor',
            '$nivel,', ...

Is that just a typo in the question? If not it could be what's causing your error as MySQL is struggling to turn '87,', for example, into an integer.