Add multiple products to a single package mysql

1

Good afternoon
I have two tables encomenda and prod_encomenda . My goal is to assign a order multiple products. Both contain id_encomenda , so I assume that the structure of the tables is well done! What I mean exactly is:

  

Order
order_id = 1   Home   Prod_commend   Home   product_id = 20; order_id = 1;
  product_id = 42; order_id = 1;   

For this I did something like this:

if (isset($_POST['pagamento']))  //ao carregar no botão efetuar pagamento os dados serão inseridos na tabela encomenda e prod_encomenda
                {
                    //INSERIR NA TABELA ENCOMENDA 
                    $inserir=mysqli_query($link,"INSERT INTO encomenda (id_encomenda,email, data_encomenda) VALUES ('','".$_SESSION['user']."','$data')");
                    if (!$inserir) 
                    {
                        echo "Erro ao inserir na tabela";
                    }

                    $sql2=mysqli_query($link,"SELECT id_encomenda from encomenda where email='".$_SESSION['user']."'");
                    $registos_id_encomenda=mysqli_num_rows($sql2);

                    while ($registos_id_encomenda!=0) {
                        $get_id_encomenda=mysqli_fetch_array($sql2);
                        $registos_id_encomenda--;
                    }

                    //INSERIR NA TABELA PROD_ENCOMENDA
                    $sql3=mysqli_query($link,"INSERT INTO prod_encomenda (id_encomenda, id_produto, quantidade, preco_total) VALUES ('".$get_id_encomenda[0]."', '$item_id','".$each_item['quantidade']."','$producttotalpricetotal')");
                    if (!$sql3) {
                        echo "Erro ao inserir na tabela";
                    }


But clearly when entering 3 products for example, it generates 3 orders in the database, instead of just being an order for the 3 products. Thank you in advance!

    
asked by anonymous 12.06.2018 / 15:57

2 answers

2

Do you have a cycle that encompasses the code that is within if (isset($_POST['pagamento'])) ?

The @Nuno Gonçalves code is more or less what you have to do.

  • Insert order;
  • Go fetch the id with which the order was saved (you already do, but you can use mysqli_insert_id for this instead of running a new query
  • A loop to insert each product that was in the shopping basket using the id of step 2 as the foreign key
  • Products can be $_SESSION , $_POST , $_COOKIES or even a temporary database table, as you have developed it.

    Given the comment you left, what you have to do is (remove the cycle you have before if and move it to include only the execution of $sql3

    $erros = [];
    foreach($_SESSION['carrinho'] as $cada_item) {
        //INSERIR NA TABELA PROD_ENCOMENDA
        $sql3=mysqli_query($link,"INSERT INTO prod_encomenda (id_encomenda, id_produto, quantidade, preco_total) VALUES ('".$get_id_encomenda[0]."', '$item_id','".$each_item['quantidade']."','$producttotalpricetotal')");
        if (!$sql3) {
            // como nao é ideal fazer echo aqui
            // podes sempre guardar num array e mais tarde mostrar
            //echo "Erro ao inserir na tabela";
            $erros[] = "Erro ao inserir produto: {$item_id}";
        }
    }
    
        
    12.06.2018 / 19:09
    2

    You basically have to return the generated key to the submitted order and then loop the products by assigning the same key. Example:

    // inserir a encomenda
    $sql = "INSERT INTO encomenda (...) VALUES (...)";
    mysql_query($sql,$conn );
    // retornar o último id
    $id_encomenda = mysql_insert_id( $conn );
    
    //percorrer as linhas através do $_POST recebido
    foreach ($_POST as $k=>$v) {
        // inserir linhas da encomenda
        $sql = "INSERT INTO prod_encomenda (...) VALUES ($id_encomenda,...))";
        mysql_query($sql,$conn );
    }
    
        
    12.06.2018 / 16:51