Foreign key - Insert ID of table 1 in Table 2 PHP / MYSQL

Question

Foreign key - Insert ID of table 1 in Table 2 PHP / MYSQL

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

Good afternoon, I'm a beginner in programming and I have the following difficulty. I have 2 tables:

CREATE TABLE produto(
  id_produto int(10)  NOT NULL PRIMARY KEY auto_increment,
  nome varchar(45) NOT NULL,
)ENGINE=InnoDB;

and the second table:

CREATE TABLE img_produto(
  id_img INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  id_produto int(10)  NOT NULL,
  nome_img VARCHAR(45) NOT NULL,
  FOREING KEY (id_produto) REFERENCES produto (id_produto) ON DELETE NO ACTION ON UPDATE NO ACTION
)ENGINE = InnoDB;

My question is how to proceed when registering a product with its image, the product ID (product_id) of the produto table is automatically inserted into the img_produto table in the id_produto field?

php mysql

asked by anonymous 05.07.2016 / 19:00

2 answers

0

It all depends on how your application will work. If the image registration is done separately you will need a field with the product id.
You can use <input type="hidden" name"id_produto" value"ID DO PRODUTO AQUI"> . Or you can make use of a select field with the products listed:

<select>
   <option value="id do produto aqui">produto 01</option>
   <option value="id do produto aqui">produto 02</option>
   <option value="id do produto aqui">produto 03</option>
</select>

If you insert the product and image in the same form you will need to insert the product and get the id to use in the image table.
link

I hope I have helped.

05.07.2016 / 19:16

Translate website automatically according to the netizen's country Deploy Laravel project by Git

score 3 · Accepted Answer

If I understand what the @Guilhermenascimento said , you can use LAST_INSERT_ID () to retrieve this value. It would look something like this:

INSERT INTO produto(nome) VALUES ('Produto1'); 

INSERT INTO img_produto(id_produto, nome_img) VALUES (LAST_INSERT_ID(),'Nome da Imagem');

The PHP manual has mysqli_insert_id () , which gets the id of the previous insert , as the site example itself:

<?php
    $link = mysqli_connect('localhost', 'mysql_user', 'mysql_password', 'mydb');
    if (!$link) {
        echo 'Debugging errno: ', mysqli_connect_errno(), PHP_EOL;
        echo 'Debugging error: ', mysqli_connect_error(), PHP_EOL;
        exit;
    }

    if (mysqli_query($link, "INSERT INTO mytable (product) values ('kossu')")) {
        printf("Last inserted record has id %d\n", mysql_insert_id());
    }

However, this form has some notes that must be observed to avoid errors. These notes can be viewed in the official PHP documentation.

Issue

As warned in the comments by @ rray , do not use functions that start with mysql_ because this is the old API that was deprecated in PHP 5.5.0 and removed in PHP 7.0.0 , as can be seen ma official documentation in English .

Alternatively, according to the manual, you have mysqli_insert_id and PDO::lastInsertId .