record data in different tables

0

I get the following question: I have three tables in my database (student (role 1), teacher (role 2), guardian (role 3)) each of them has a specific role and I have a form to register the data, I would like to know if there is a way to register the data in a specific table when inserting the role, for example if in my form I selected the role 1 keep the record in the student table, if I select the role 2 keep the record in the teacher table etc ..

This code registers me but only in the table that I specify (students)

$trol = $_POST["trol"];<br>
$tnombres = $_POST["tnombres"];<br>
$tapellidos = $_POST["tapellidos"];<br>
$tcorreo = $_POST["tcorreo"];<br>
$tcelular = $_POST["tcelular"];<br>
$tdireccion = $_POST["tdireccion"];<br>
$tnombres = $_POST["tnombres"];


$buscarUsuario = "SELECT * FROM $tbl_name
WHERE id_estudiante = '$_POST[tusuario]' ";

$result = $conexion->query($buscarUsuario);

$count = mysqli_num_rows($result);

if ($count == 1) {
echo "<br />". "El Nombre de Usuario ya a sido tomado." . "<br />";
echo "<a href='index.html'>Por favor escoga otro Nombre</a>";
}<br>
else{<br>
$query = "INSERT INTO estudiantes (id_estudiante, nombre, apellido, correo, telefono, direccion, id_rol, contrasena)
           VALUES ('$_POST[tusuario]', '$tnombres', '$tapellidos', '$tcorreo', '$tcelular', '$tdireccion', '$trol', '$hash')";<br>
 }

I hope you made me understand, thank you in advance.

    
asked by Sergiio Rodriguez 20.09.2018 в 17:42
source

2 answers

1

It could be adding a switch to generate the query depending on the role

$query="";
switch($trol)
{
  case 1:
    $query="insert into Estudiante() values()";
  break;
  case 2:
     $query="insert into Profesor() values()";
  break;
  case 3:
     $query="insert into Acudiente() values()";
  break;
}
    
answered by 20.09.2018 / 18:03
source
0

Simplifying the fields a bit for a more compact response, let's say that your tables have fields correo and nombres in addition to the primary key that is auto-incremental and you do not need to insert it explicitly. That said, you have three problems:

  • You are not checking if all the fields are coming. You may try to search $ _POST for a key that was not sent.
  • Assume that in advance the person who wants to register knows their ID. If the person does not exist in the database does not have ID, how could the user guess?
  • Your logic has security flaws, because you are interpolating what happens to you by direct POST in your sentences.
  • For point 1 , you should wrap everything up in a check:

    if(!isset($_POST["trol"],$_POST["tcorreo"],$_POST["tnombres"])) {
      echo "<br />". "No ha enviado todos los campos." . "<br />";
      echo "<a href='index.html'>Por favor complete todos los campos</a>";
    } else {
      // tu lógica acá
    }
    

    For point 2 , you should use another field (such as email) to know if the user already exists

    For point 3 , the first thing would be to cast $_POST['trol'] to an integer and then see if the role exists between your dictionary rol => tabla

    $diccionario = [
     1 => 'estudiante',
     2 => 'profesor',
     3 => 'acudiente'
    ];
    
    $trol = (int) $_POST['trol'];
    
    if(!array_key_exists($trol, $diccionario)) {
       echo "<br />". "Ese rol no existe en el sistema." . "<br />";
       echo "<a href='index.html'>Por favor escoja otro rol</a>";
    } else {
      // tu lógica
    }
    

    When you cast it you make sure you do not insert into the table an arbitrary string that could lend itself to an SQL injection. If the role exists in the dictionary you can get the name of the table from it, which you will inevitably interpolate in your sentences. But at least you handle the dictionary so that interpolation is harmless.

    Done the checks, you can already do:

    $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
    $correo = $_POST['tcorreo'];
    $nombres = $_POST['tnombres'];
    $tabla = $diccionario[$trol];
    
    $stmt1 = $mysqli->prepare("SELECT correo from $tabla WHERE correo=?");
    $stmt1->bind_param("s", $correo);
    
    $stmt1->execute();
    
    $stmt1->store_result();
    $count = $stmt1->num_rows;
    $stmt1->close();
    
    if($count > 0) {
       echo "<br />". "El email de Usuario ya ha sido tomado." . "<br />";
       echo "<a href='index.html'>Por favor escoga otro email</a>";
    } else {
    
      // lógica de inserción
    
    }
    

    Then we got to the point where you checked that:

    • The role exists in your dictionary
    • The fields were populated in the form
    • The email is free in the table you get from the dictionary

    So you apply the insert:

    $stmt2 = $mysqli->prepare("INSERT INTO $tabla (correo, nombres) VALUES (?, ?)");
    
    $stmt2->bind_param("ss", $correo, $nombres);
    $stmt2->execute();
    $stmt2->close();
    

    Putting everything together and rearranging so as not to have so many nested if:

    $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
    
    $diccionario = [
        1 => 'estudiante',
        2 => 'profesor',
        3 => 'acudiente',
    ];
    
    $trol = isset($_POST['trol']) ? (int) $_POST['trol'] : 0;
    
    if (!array_key_exists($trol, $diccionario)) {
        echo "<br />" . "Ese rol no existe en el sistema." . "<br />";
        echo "<a href='index.html'>Por favor escoja otro rol</a>";
    } else if (!isset($_POST["trol"], $_POST["tcorreo"], $_POST["tnombres"])) {
        echo "<br />" . "No ha enviado todos los campos." . "<br />";
        echo "<a href='index.html'>Por favor complete todos los campos</a>";
    } else {
        $correo = $_POST['tcorreo'];
        $nombres = $_POST['tnombres'];
        $tabla = $diccionario[$trol];
    
        $stmt1 = $mysqli->prepare("SELECT correo from $tabla WHERE correo=?");
        $stmt1->bind_param("s", $correo);
    
        $stmt1->execute();
    
        $stmt1->store_result();
        $count = $stmt1->num_rows;
        $stmt1->close();
    
        if ($count > 0) {
            echo "<br />" . "El email de Usuario ya ha sido tomado." . "<br />";
            echo "<a href='index.html'>Por favor escoga otro email</a>";
        } else {
    
            $stmt2 = $mysqli->prepare("INSERT INTO $tabla (correo, nombres) VALUES (?, ?)");
    
            $stmt2->bind_param("ss", $correo, $nombres);
            $stmt2->execute();
            $stmt2->close();
    
            echo "<br />" . "Todo funcionó: Usuario creado en la tabla $table." . "<br />";
        }
    
    }
    

    This script lacks checks to handle cases where you can not connect or the statements are executed poorly, but that is your task.

        
    answered by 20.09.2018 в 19:02