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

Asked

Viewed 4,430 times

1

Good afternoon, I am 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 so that when registering a product with the respective image, the product ID(id_product) of the table produto is automatically inserted into the table img_produto in the field id_produto ?

php mysql

2 answers

3


If I understood what the @Guillhermenascimento said, you can use the LAST_INSERT_ID() to recover 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 the mysqli_insert_id(), that gets the id of the previous insert, as the example of the site 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, so as not to cause errors. Such notes can be seen in the official PHP documentation.

Editing

As alerted in the comments by @rray, do not use functions that start with mysql_, because it is the old API that was discontinued in the 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 the mysqli_insert_id and the PDO::lastInsertId.

  • 2

    Do not use the documentation in Portuguese, look (difference) at the same link in English http://php.net/manual/en/function.mysql-insert-id.php O.o'

  • 2

    @rray Thanks for the warning. I knew the documentation in Portuguese was criticized, but I didn’t know I had this kind of problem.

0

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

<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 product and image insertion is done in the same form you will need to insert the product and pick up the id to use in the picture table.
http://www.w3schools.com/php/php_mysql_insert_lastid.asp

I hope I’ve helped.

  • Buddy, I see the idea, but I don’t know if this is going to work dynamically...

Browser other questions tagged php mysql

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