Error inserting records with MySQLi with prepared statements

2

I published a question yesterday, but another very similar problem arose, I already verified the connection to my database (it is correct), I already did "SELECT" of all my table and answered correctly, and update records and if you update them , just that the updated id that returns to me is "0", when it really is not like that.

$id_registro = $sql->insert_id; this is what always returns "0", that's why it does not enter the if($id_registro > 0) which makes me get the error. I already verified that my column has AUTO_INCREMENT .

I have already verified that the data in the form is sent correctly, honestly I do not know where the error could be. Any extra information you need, I can provide it.

In the part of the AJAX that becomes a console.log(data) I tried to return the php variable die(json_encode($sql)); and all the fields return it as NULL .

This is my connection to the database.

$conn = new mysqli('localhost', 'root', '******', '*********');

if($conn->connect_error){
  echo $error -> $conn->connect_error;
}

This is my php file

include_once "funciones/funciones.php";
$usuario = $_POST['usuario'];
$nombre = $_POST['nombre'];
$password = $_POST['password'];
$id_registro = $_POST['id_registro'];
if ($_POST['registro'] == 'nuevo') { //INICIO-NUEVO
  $opciones = array(
    'cost' => 12
  );
  $password_hashed = password_hash($password, PASSWORD_BCRYPT, $opciones);
  try {
    $sql = $conn->prepare(" INSERT INTO admins (usuario, nombre, password) VALUES (?, ?, ?) ");
    $sql->bind_param('sss', $usuario, $nombre, $password_hashed);
    $sql->execute();
    $id_registro = $sql->insert_id; //la pregunta pasada me dijeron que insert_id es un método de la conexión no de la sentencia, probé ambas y no funcionó
    if ($id_registro > 0) {
      $respuesta = array(
        'respuesta' => 'exito',
        'id_admin' => $id_registro
      );
    }else{
      $respuesta = array(
        'respuesta' => 'error'
      );
    }
    $sql->close();
    $conn->close();
  } catch (Exception $e) {
    echo "Error: " . $e->getMessage();
  }
die(json_encode($respuesta));// aqui se regresan valores a ajax
} // FIN-NUEVO

if ($_POST['registro'] == 'actualizar') { // INICIO-ACTUALIZAR
  try {
    if (empty($_POST['password'])) {
      $stmt = $conn->prepare("UPDATE admins SET usuario = ?, nombre = ?, editado = NOW() WHERE id_admin = ? ");
      $stmt->bind_param("ssi", $usuario, $nombre, $id_registro);
    }else{
      $opciones = array(
        'cost'=>12
      );
      $hash_password = password_hash($password, PASSWORD_BCRYPT, $opciones);
      $stmt = $conn->prepare("UPDATE admins SET usuario = ?, nombre = ?, password = ?, editado = NOW() WHERE id_admin = ? ");
      $stmt->bind_param("sssi", $usuario, $nombre, $hash_password, $id_registro);
    }
    $stmt->execute();
    if ($stmt->affected_rows) {
      $respuesta = array(
        'respuesta' => 'exito', // esto tiene que corresponder con la respuesta esperada en AJAX
        'id_actualizado' => $stmt->insert_id
      );
    }else{
      $respuesta = array(
        'respuesta' => 'error'
      );
    }
    $stmt->close();
    $conn->close();
  } catch (Exception $e) {
    $respuesta = array(
      'respuesta' => $e->getMessage()
    );
  }
  die(json_encode($respuesta));
} // FIN-ACTUALIZAR

This is the AJAX to which the answer returns.

  $('#guardar-registro').on('submit', function(e){
    e.preventDefault();
    var datos = $(this).serializeArray();
    $.ajax({
      type: $(this).attr('method'), // post, en este caso
      data: datos, // la variable con todos los datos enviados
      url: $(this).attr('action'), //esto es el archivo php que está más arriba
      dataType: 'json', // el formato de los datos que estamos enviando
      success: function(data) { // data, es lo que regresa el archivo enlazado
        console.log(data);
        var resultado = data;
        if (resultado.respuesta == 'exito') {
          swal(
            'Correcto',
            'Se guardó correctamente',
            'success'
          )
        }else{
          swal(
            'Incorrecto',
            'Hubo un error al crear el administrador',
            'error'
          )
        }
      }
    })
  });

EDIT:

This is what the SHOW CREATE TABLE admins shows:

CREATE TABLE 'admins' (
 'id_admin' int(10) unsigned NOT NULL AUTO_INCREMENT,
 'usuario' varchar(50) NOT NULL,
 'nombre' varchar(100) NOT NULL,
 'password' varchar(60) NOT NULL,
 'editado' datetime DEFAULT NULL,
 'nivel' int(1) NOT NULL,
 PRIMARY KEY ('id_admin'),
 UNIQUE KEY 'usuario' ('usuario')
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
    
asked by Angel Rivas 02.08.2018 в 02:06
source

1 answer

1

The first thing that must be settled is that insert_id is a method of the connection, not of the sentence. The PHP Manual is very clear in that: mysqli::$insert_id where mysqli represents the connection object, which is $conn in your case.

Then, we have managed to print the reason for the error, doing this:

 ...
 }else{
  $respuesta = array(
    'respuesta' => 'error: '.$conn->error;
  );

which is the following:

  

Field 'edited' does not have a default value "

When you make insertions in a table, the columns that do not receive specific values in that query acquire:

  • a . its default value if you have it
  • b. or NULL if they have no default value

In the case b there may be a conflict, which is what must surely be happening with your editado column: the same has not declared a default value nor does it support null values . Not being mentioned in the INSERT INTO ... will try to assign NULL and the insertion will not be possible because it violates the restriction NOT NULL that that column has.

Solutions :

a . Give it a default value with:

ALTER TABLE admins 
MODIFY COLUMN editado DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

b . Include it in the insert statement assigning a value:

INSERT INTO admins (usuario, nombre, password, editado) VALUES (?, ?, ?, NOW())

c. Modify editado to allow nulls.

  

a. is the most reasonable solution, solving the root problem.

Note that you have the same problem with your column nivel , it does not support nulls, therefore, it should have a default value ( 0 for example) or you must modify it to admit nulls or you can not forget it never in a INSERT INTO ... :-) Small details that escape but that are very important.

I hope it's useful.

    
answered by 02.08.2018 / 03:22
source