How to take ID from another table and INSERT into another?

Question

I have two tables: destinations and input:

• On the table destinations: id_destination(AI) and destination;
• On the table entree: id(AI), id_destination(relation with table id_destination destinations).

I created a query to insert the first form field (destination) in the table destinations

$sqlinsert1 = mysql_query("INSERT INTO `destinos` VALUES('', '$destino')");

I created a second query to fetch the newly created Row:

$another_id = mysql_query("SELECT * FROM `destinos` WHERE destino = '$destino'");

And a third query to insert the data in the table entree:

 $sqlinsert2 = mysql_query("INSERT INTO `entrada_pt` VALUES('', '$another_id')");

Only when checking the table entree, nothing is inserted, other than the table destinations, which is filled.

How to take the id of the first table and insert it into the second table?

Answer 1

First, the answer to:

To get the ID of the last insert, just use the mysql_insert_id()

$sqlinsert1 = mysql_query("INSERT INTO `destinos` VALUES('', '$destino')");
$id_destino = mysql_insert_id();

$sqlinsert2 = mysql_query("INSERT INTO `entrada_pt` VALUES('', '$id_destino')");

A remark:

This code doesn’t make any sense.

$another_id = mysql_query("SELECT * FROM `destinos` WHERE destino = '$destino'");

The function mysql_query() does not bring the ID of record inserted, but a pointer to a set of results.

To use your 2nd query instead of mysql_insert_id(), would need this:

$sqlselect1 = mysql_query("SELECT * FROM `destinos` WHERE destino = '$destino'");
$row = mysql_fetch_assoc($sqlselect1);
if ($row)
   $another_id = $row['id'];

Most important:

No one should be using the functions anymore mysql_ in PHP!
They are insecure* and obsolete, and are no longer available as of PHP 5.5.
Use the functions mysqli_!

_{* In fact the functions themselves are not insecure, but are used insistently in the worst way. Some people change lib, but continues to place PHP variables directly within SQL. The ideal is to use only Binding.}

Answer 2

The mysql_* functions are deprecated the use of mysqli or PDO is recommended. In this question has a list of arguments for NOT using them.

To get the last id inserted use the function mysql_insert_id()

$sqlinsert1 = mysql_query("INSERT INTO `destinos` VALUES('', '$destino')");
$id_destino = mysql_insert_id();
$sqlinsert2 = mysql_query("INSERT INTO `entrada_pt` VALUES('', '$id_destino')");

Answer 3

You can use yes!! The only difference is in swapping mysql_ for mysqli_.... And put the connection link, as below.

include 'conexao.php';

//$conn é a variavel de conexão utilizada em conexao.php

$sqlinsert1 = mysql_query("INSERT INTO `destinos` VALUES('', '$destino')");

$id_destino = mysql_insert_id($conn);

$sqlinsert2 = mysql_query("INSERT INTO `entrada_pt` VALUES('', '$id_destino')");