Add multiple products to a single mysql order

Question

Good afternoon
I have two tables encomenda and prod_encomenda. My goal is to assign to one order several products. Both contain the id_encomenda, so I guess the structure of the tables is well done! What I want exactly is for example:

Order
id_order = 1

Prod_encomenda
id_product = 20; id_order=1;
id_product = 42; id_order=1;

For that 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 for example 3 products, it generates 3 orders in the database, instead of just one order for the 3 products.. Thanks in advance!

Answer 1

Do you have a loop that encompasses the code that’s inside the if (isset($_POST['pagamento']))?

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

1. Place an order;
2. Fetch the id the package was stored with (you already do, but you can use the mysqli_insert_id instead of running a new query
3. A loop to insert each product that was in the shopping basket using as a foreign key the id of step 2.

The products may be on $_SESSION, in the $_POST, in $_COOKIES or even in a temporary table of the database, as you have developed.

Considering the comment you left behind, what you need to do is (remove the cycle you have before the if and moves 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}";
    }
}

Answer 2

Basically you have to return the generated key to the submitted order and then make a loop by products 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 );
}