How to reference foreign keys in registration forms?

Asked

Viewed 1,206 times

2

I’m making a library website as college work, which has the following table in the database:

1

My problem is that I don’t know how to handle php code and the form of fields that receive foreign keys.

For example: In the loan registration I need to reference the book that is being borrowed and for this I have the foreign key 'keeping books_code books', but I do not know how to make the code that would enable the work would perform this function correctly on the registration screen.

If useful, the codes for registration I used in other tables that do not have foreign keys follow this format:

<?php
    require_once ("InscricaoClass.php");

    $user = livros::getInstance();

    if (isset($_GET['codigolivros'])) {
        $codigolivros=$_GET['codigolivros'];
        $user->__set('codigolivros', $codigolivros);
        $user->carregar();
    } else {
        $codigolivros=0;
    }

    if ($_SERVER['REQUEST_METHOD']=='POST') {
        $user ->__set('titulo', $_POST['titulo']);
        $user ->__set('editora', $_POST['editora']);
        $user ->__set('autor', $_POST['autor']);
        $user ->__set('genero', $_POST['genero']);

        if ($_POST ['codigolivros']>0) {
            $user-> alterar();
        } else {
            $user->gravar();
        }
    }

?>
<!DOCTYPE html>
<html>
    <head>
    <meta charset="utf-8">
        <title>Cadastro Livros</title>
    </head>
    <body>
        <form action="" method="post">
            <h2> Cadastro Livros</h2>

            <label>Código</label>
            Código:
            <input type="number" name="codigolivros"  value="<?php echo $codigolivros;?>" >
            <br><br><br>

            Titulo: <br>
            <input type="text" name="titulo" value="<?php echo $user::$titulo;?>" placeholder=""> 
            <br><br><br>

            Editora: <br>
            <input type="text" name="editora" value="<?php echo $user::$editora;?>" placeholder=""> 
            <br><br><br>

            Autor: <br>
            <input type="text" name="autor" value="<?php echo $user::$autor;?>" placeholder=""> 
            <br><br><br>

            Genero: <br>
            <input type="text" name="genero" value="<?php echo $user::$genero;?>" placeholder=""> 
            <br><br> 

            <input type="submit" name="" value="Gravar">
        </form>
    </body>
</html>

The above code requires this:

    <?php

class livros
{
    //inicio dos atributos
    public static $codigolivros;
    public static $titulo;
    public static $editora;
    public static $autor;
    public static $genero;  

    public static $instance;

    //inicio dos métodos
    public function __construct()
    {
        require_once("Conexao.php");
    }

    public static function getInstance()
    {
        self::$instance = new livros ();
        return self::$instance;
    }

    public function __set($var,$val)
    {
        $this->$var = $val;
    }

    public function __get($var)
    {
        $this->$var;
    }
    //Fim dos métodos padrões

    public function gravar()
    {
        try {
            $sql="insert into manterlivros (titulo, editora, autor, genero) values (:p1, :p2, :p3, :p4)";
            $con=Conexao::getInstance () ->prepare($sql);
            $con->bindValue (":p1", $this->titulo);
            $con->bindValue (":p2", $this->editora);
            $con->bindValue (":p3", $this->autor);
            $con->bindValue (":p4", $this->genero);
            $result=$con->execute();

            return $result;
        } catch (Exception $e) {
            echo "ERRO".$e->getMessage();
        }
    }

    public function consultar()
    {
        try {
            $sql ="select * from manterlivros";
            $con= conexao::getInstance()->prepare($sql);
            $con -> execute ();

            return $con;
        } catch (Expection $e) {
            echo "ERRO NO CONSULTAR";
        }
    }


    public function excluir($codigo)
    {
        try {
            $sql = "delete from manterlivros where codigolivros = '".$codigo."'" ;
            $con = Conexao::getInstance()->prepare($sql);

            return $con->execute();echo "excluindo";
        } catch (exception $e) {
            echo "ERRO NO EXCLUIR";
        }
    }

    public function alterar()
    {
        try {
            $sql="update manterlivros set titulo=:p1, editora=:p2, autor=:p3, genero=:p4 where codigolivros=:p0";

            $con=Conexao::getInstance()->prepare($sql);
            $con->bindValue (":p1", $this->titulo);
            $con->bindValue (":p2", $this->editora);
            $con->bindValue (":p3", $this->autor);
            $con->bindValue (":p4", $this->genero);
            $con->bindValue ("p0", $this->codigolivros);

            $result=$con->execute();

            return $result;
        } catch (Exception $e) {
            echo "Erro no Alterar";
        }
    }


    public function carregar()
    {
        try { 

            $sql ="select * from manterlivros where codigolivros=:p1";

            $con= Conexao::getInstance()->prepare($sql);
            $con->bindValue (":p1", $this->codigolivros);
            $con->execute ();

            foreach ($con as $linha) {
                $this::$titulo = $linha['titulo'];
                $this::$editora = $linha['editora'];
                $this::$autor = $linha['autor'];
                $this::$genero = $linha['genero'];
            }

            return $con;
        } catch (Expection $e) {
            echo "ERRO NO ALTERAR";
        }
    }
}
php mysql foreign-key insert

  • The 'rule' of the foreign key is, take as example product and category. When registering a product there must be a category if a value that does not exist in the category table is registered, the Insert in the product table will fail. Edit the question specify your problem/question better.

  • I edited it, I hope it’s clear.

2 answers

0

Most complete version:

Your form must be mounted with at least this information, a text box for the name, after typed the name is made an ajax request for PHP to fetch the matricula based on the name, made the matricula field is filled.

Now make a button that opens a modal to add the books for loan. By clicking save the main data received should be, matricula and livros(an array and contains the id_livro and other data).

This information should be recorded in a table emprestimos the main fields are matricula, id_livro, data_emprestimo, data_devolucao.

Simple version (only for example)

Create a single form with the fields matricula, and for example 3 or limit of loans per student combobox his name should be in brackets, so: <select name="livros[]">, to record see the previous paragraph, it will be necessary foreach to register all the loans, also remember that sometimes only a book can be borrowed.

  • I could superficially understand what you said, but I don’t know how to make the code to perform these functions. If you had a way, you could pass a simple syntax for me to try to understand in use?

0

In your Books() class create a method to query the book ID entered in the field name="codigolivros" so Voce will get FK to record.

Browser other questions tagged php mysql foreign-key insert

You are not signed in. Login or sign up in order to post.