Help, insert records in related tables using php oo

Question

Help, insert records in related tables using php oo

Navigation

#1 by (0 votes)

0

Greetings friends, since yesterday I'm trying to solve an exercise but I can not find a solution for now. I have 2 tables in my DB called ts_client and another ts_quipment , the relationship is 1: n a client can have many computers. Previously I could append records successfully, but after I created the relationship 1: n it was a bit complicated, since it is the first time I work with related tables.

I was reading some solutions that they present on the internet, as I understood I should take the id of my table ts_cliente and then work based on that id with the table ts_equipment ; I got to work but only I get error after error. I clarify that I am using pdo oo , I leave a reference image:

By clicking on Register team, the values are sent by means of a function which will insert the data in the DB.

if(isset($_POST['agregar_equipo'])){

    $ced = $_POST['cedu_cli'];
    $nomb = $_POST['nomb_cli'];
    $aped = $_POST['aped_cli'];
    $telf = $_POST['telf_cli'];
    $direc = $_POST['dire_cli'];

    $equipo = $_POST['equipo'];
    $marca = $_POST['marca'];
    $model= $_POST['modelo'];
    $serial = $_POST['serial'];
    $fech = $_POST['fecha'];        

    $persona = new usuario();
    $persona->insertarCliente($ced, $nomb, $aped, $telf, $direc, $equipo, $marca, $model, $serial, $fech);
}

Here is the function that is responsible for processing the data

public function insertarCliente($ced, $nomb, $aped, $telf, $direc, $equipo, $marca, $model, $serial, $fech){

    // Compruebo que las variables no esten vacias
    if(empty($ced) || empty($nomb) || empty($aped) || empty($telf) || empty($direc)){
        header('Location: pagina_clientes.php');
    }else{

        $comprobar = "SELECT COUNT(*) FROM ts_cliente WHERE cli_ced = $ced LIMIT 1";
        $existe = $this->db_conexion->query($comprobar);

        // Si ya existe la cedula simplemente registro el equipo
        if($existe->fetchColumn() > 0){

            $valor = "SELECT cli_id FROM ts_cliente WHERE cli_ced = $ced LIMIT 1";
            $cli_id = $this->db_conexion->query($valor);
            $last_id = $cli_id->fetch();

            $id = $last_id['cli_id'];

            $insertar = "INSERT INTO ts_equipo (equ_nom, equ_mar, equ_mod, equ_ser, equ_fec, cli_id) VALUES ($equipo, $marca, $model, $serial, $fech, $id)";
            $resultado = $this->db_conexion->query($insertar);

            header('location: pagina_equipos.php');
            $resultado->closeCursor();
            $cli_id->closeCursor();

        }

        // Caso contrario registro al cliente y el equipo
        else{

            $insertar = "INSERT INTO ts_cliente (cli_ced, cli_nom, cli_ape, cli_tel, cli_dir) VALUES (:a, :b, :c, :d, :e)";
            $resultado = $this->db_conexion->prepare($insertar);
            $resultado->execute(array(':a'=>$ced,':b'=>$nomb,':c'=>$aped,':d'=>$telf,':e'=>$direc));
            $last_id = $this->db_conexion->lastInsertId();

            $insertar2 = "INSERT INTO ts_equipo (equ_nom, equ_mar, equ_mod, equ_ser, equ_fec, cli_id) VALUES ($equipo, $marca, $model, $serial, $fech, $last_id)";
            $resultado2 = $this->db_conexion->query($insertar2);

            header('location: pagina_equipos.php');

            $resultado->closeCursor();              
            $resultado2->closeCursor();
        }

        $existe->closeCursor();
        $this->db_conexion = null;
    }
}

That was the last code I tested, previously I had a function to register the equipment and from the function that registers the clients I called the team function ... But I generated several errors, and I thought that with this form I could work but the result was the same: (

Excuse me if I have errors in the implementation of the code, but this way was that I understood (and I take it for granted that it is wrong) ... That is why I appeal to you colleagues, to guide me on the subject; already understanding this part of the code I can finish to relate my other tables. Blessings

mysql sql html5 php pdo

asked by Kevin Melendez 18.11.2017 в 21:29

source

1 answer

Network Inventory (Visual Basic Script) How to have an entry method for mathematical equations

score 0 · Accepted Answer

Your code may be failing because some of the INSERT creates duplicate data, or because there is syntax error.

I think it is necessary to normalize your insertion queries, applying prepared queries in each of them. In one you apply, but in another you do not. Also your query SELECT and all queries that you need data coming from the outside should apply this practice , otherwise your code will be highly vulnerable.

On the other hand, the first query you make with COUNT is unnecessary. In PDO, if you want to know that there is data, you can evaluate the same data. You do not need in this case to make a query with COUNT first to then do almost the same query to get the id. Better make the query that gets the id, make a fetch of that column and evaluate the variable. If it is FALSE it means that there is no data with that criterion. And if it is not, you already have the data, you do not have to make a second trip to look for it.

In the code I have tried to evaluate the possible occurrences doing the redirections only when the insertion is done. I have tried to control everything, but something may have escaped me.

I would rewrite the function like this:

public function insertarCliente($ced, $nomb, $aped, $telf, $direc, $equipo, $marca, $model, $serial, $fech){

    // Compruebo que las variables no esten vacias
    if(empty($ced) || empty($nomb) || empty($aped) || empty($telf) || empty($direc)){
        header('Location: pagina_clientes.php');

    }else{
        $ql = "SELECT cli_id FROM ts_cliente WHERE cli_ced = :a LIMIT 1";
        $stmt = $this->db_conexion->prepare($sql);
        $arrParams=array(':a'=>$ced);
        $stmt->execute($arrParams);
        $cli_id=$stmt->fetch(PDO::FETCH_ASSOC);

        if ($cli_id){
            $stmt->close();
            $id=$cli_id["cli_id"];
            $insertar = "INSERT INTO ts_equipo (equ_nom, equ_mar, equ_mod, equ_ser, equ_fec, cli_id) VALUES (:a, :b, c:, d:, e:, :f)";            
            $arrParams=array(":a"=>$equipo, ":b"=>$marca, ":c"=>$model, ":d"=>$serial, ":e"=>$fech, ":f"=>$id);
            $stmt = $this->db_conexion->prepare($insertar);
            $insertar=$stmt->execute($arrParams);

            if ($insertar){
                $stmt->close();
                $this->db_conexion = null;
                header('location: pagina_equipos.php');

            }else{
                $arrMensaje=array("mensaje"=>"Fallo insert".$stmt->errorInfo()[2]);
            }               

        }else{

            $insertar = "INSERT INTO ts_cliente (cli_ced, cli_nom, cli_ape, cli_tel, cli_dir) VALUES (:a, :b, :c, :d, :e)";
            $stmt = $this->db_conexion->prepare($insertar);
            $arrParams=array(':a'=>$ced,':b'=>$nomb,':c'=>$aped,':d'=>$telf,':e'=>$direc);
            $nuevo=$stmt->execute($arrParams);

            if ($nuevo){
                $id = $this->db_conexion->lastInsertId();
                $insertar = "INSERT INTO ts_equipo (equ_nom, equ_mar, equ_mod, equ_ser, equ_fec, cli_id) VALUES (:a, :b, c:, d:, e:, :f)";
                $arrParams=array(":a"=>$equipo, ":b"=>$marca, ":c"=>$model, ":d"=>$serial, ":e"=>$fech, ":f"=>$id);
                $stmt = $this->db_conexion->prepare($insertar);
                $insertar=$stmt->execute($arrParams);

                if ($insertar){
                    $stmt->close();
                    $this->db_conexion = null;
                    header('location: pagina_equipos.php');

                }else{
                    $arrMensaje=array("mensaje"=>"Fallo insert".$stmt->errorInfo()[2]);
                }

            }else{
                    $arrMensaje=array("mensaje"=>"Fallo insert".$stmt->errorInfo()[2]);
            }


        }
    }


    if ($arrMensaje){

        echo $arrMensaje["mensaje"];
    }


}