Insert data in database

Question

Insert data in database

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I am trying to insert my data from a form and from a database, that is to say that from my form I only add payroll and dates and when executing in the INSERT I insert what is related to that payroll number of the table used, I saw that it was done with a SELECT inside the INSERT but it does not work for me, I could be guided, what I'm doing wrong. I leave my code:

mysqli_query($con, "INSERT INTO 
    roles_turno_emp 
    (NOMBRE,NOMINA,RUTA,F_INICIAL,F_FINAL) 
    VALUES 
    (SELECT nombre FROM empleado WHERE nomina = '$_POST[nomina]',
    '$_POST[nomina]',
    SELECT ruta FROM empleado WHERE nomina = '$_POST[nomina]'),
    '$_POST[fecha1]',
    '$_POST[fecha2]'");

php

asked by Xavi 12.12.2018 в 18:29

source

2 answers

Table name has no column column name [closed] Can not convert an int to struct in c ++

score 1 · Answer 1

I propose the following: There are things you should consider, such as validations, but this can help you.

//declaramos variables.
$nombreEmpleado = $ruta = $fecha1 = $fecha2 = "";
//bandera para permitir insertar si pasa todas las validaciones.
$valid = true;
if ($_SERVER["REQUEST_METHOD"] == "POST") {

if (empty($_POST["nomina"])) {
    $valid = false;
    echo "La nómina es requerida";
} else {
    $nomina = test_input($_POST["nomina"]);
}

if (empty($_POST["fecha1"])) {
    $valid = false;
    echo "La fecha 1 es requerida";
} else {
    $fecha1 = test_input($_POST["fecha1"]);
}

if (empty($_POST["fecha2"])) {
    $valid = false;
    echo "La fecha 2 es requerida";
} else {
    $fecha2 = test_input($_POST["nomina"]);
}

//Incluye tu conexión a la BD.
require 'tuConexion.php';
//Prepara la consulta para obtener el nombre y la ruta del empleado con la nómina que viene del post:
$stmt = $con->prepare("SELECT nombre, ruta FROM empleado WHERE nomina = ?");
$stmt->bind_param("s", $nomina);
$stmt->execute();
$result = $stmt->get_result();
if ($stmt === false) {
    $valid = false;
    echo "Sucedió un error.";
}

if ($result->num_rows > 0) {
    // output data of each row
    while ($row = $result->fetch_assoc()) {
        $nombreEmpleado = $row["nombre"];
        $ruta = $row["ruta"];
    }
} else {
    $valid = false;
    echo '<p>No existe empleado y ruta relacionado a esta nómina.</p><br>';
}

 //Si todo bien, que inserte a la BD (Usando sentencias preparadas). en bind_param defines el tipo de dato que esperas recibir.
 //Asumiendo que recibirás solo strings:
if ($valid) {
    $stmt = $con->prepare("INSERT INTO roles_turno_emp(NOMBRE, NOMINA, RUTA, F_INICIAL, F_FINAL) VALUES (?,?,?,?,?)");
    $stmt->bind_param("sssss", $nombreEmpleado, $nomina, $ruta, $fecha1, $fecha2);

    if ($stmt->execute()) {
        echo "Los datos han sido insertados correctamente :)";
    } else {
        echo "Ocurrió un error (" . $stmt->errno . ") " . $stmt->error;
    }
  }
 } 

 //Con la siguiente función validamos plecas, espacios, caracteres especiales:
  function test_input($data) {
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
 }

score 1 · Answer 2

In order for your query to work as you have written it, you must correct the closing of parentheses for the fields NOMBRE and RUTA

INSERT INTO 
    roles_turno_emp(NOMBRE,NOMINA,RUTA,F_INICIAL,F_FINAL) 
VALUES 
    (SELECT nombre FROM empleado WHERE nomina = '$nomina'), <-------- cierre aquí
    '$nomina',
    (SELECT ruta FROM empleado WHERE nomina = '$nomina'), <-------- cierre aquí
    '$fecha1',
    '$fecha2'

1- In addition to this it is VERY important that you check the security of the variables that are happening to your queries, you must sanitize them to avoid the injection of malicious code to your queries that you could even erase your entire database, worry! link

2- You must optimize your code because you are doing two queries to obtain two fields and it is an aberration to the optimization :) and more, before modifying your code to optimize those two queries I would recommend that this insert you convert it to a stored procedure and make the corrections there. link