How do I save an array of quantities and sizes for a product? PHP

Question

I have a crud where I register the product, within this register I have a modal where it is possible to inform the sizes and quantities related to the product I am registering, after this modal returns me an array of sizes and an array of quantities in this case, could make a foreach to insert into the database.. so far so good, but I want to store the sizes and quantities in my STOCK table, where I have the following fields

id | item | quantidade | tamanho

as I do to know the code of the item that I will record in this table, because I will be registering the item in question, even if I run first the query that saves in the product table would have some way to find the id of the last item inserted in the database or something like this?

Answer 1

Well I’ll try to organize things better, because your explanation was not very clear to me at least.

Let’s say we have a table of products first:

CREATE TABLE IF NOT EXISTS `produtos` (
  `ID_PRODUTO` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRICAO` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ID_PRODUTO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This product has in turn various quantity and sizes. There are some approaches around this issue, I could have for example a TEXT field in this product table to store this array in JSON for example, but there is no Array field in Mysql, this is usually found in Nosql database, as is the case with Mongodb.

Knowing this we will need to create a new table with the link of this product with its quantity and sizes. Soon we will have a table products_sizes_quantities:

CREATE TABLE IF NOT EXISTS `produtos_tamanhos_quantidades` (
  `ID_PRODUTO_TAMANHO_QUANTIDADE` int(11) NOT NULL AUTO_INCREMENT,
  `ID_PRODUTO` int(11) NOT NULL,
  `TAMANHO` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `QUANTIDADE` int(11) NOT NULL,
  PRIMARY KEY (`ID_PRODUTO_TAMANHO_QUANTIDADE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Remember I’m not creating any Foreign Key here, but could be easily added by linking the size and quantity table with the product table.

Before that when recording the product in the product table, you will then go through its array of quantities and sizes by inserting a row for each size and quantity in the table "products_sizes_quantities". A basic PHP example would look like this:

<?php
    $conecta = mysql_connect("HOST", "LOGIN", "SENHA") or print (mysql_error()); 
    mysql_select_db("BANCO", $conecta) or print(mysql_error());

    $sql = "INSERT INTO produtos (DESCRICAO) VALUES ('Caixa de Papelão')"; 
    mysql_query($sql, $conecta);

    $idProdutoInserido = mysql_insert_id();

    $tamanhosQuantidade = array('15cm' => 30, '30cm' => 50, '45cm' => 20);

    foreach($tamanhosQuantidade as $key => $value){
        $sql = "INSERT INTO produtos_tamanhos_quantidades (ID_PRODUTO, TAMANHO, QUANTIDADE) VALUES (" . $idProdutoInserido . ", '" . $key . "', $value)"; 
        mysql_query($sql, $conecta);
    }
?>

In the above code we connect to the database, run the Insert of a product in the table, and then take the ID that Mysql generated for that product. Then I created an example array with sizes and quantities, ran this array through the command foreach, performing an Insert for each size.