0

I have two tables in my database. When I insert record to first table I need to take id that automatically generate by Mysql and add it to a new record in 2nd table.

So I need to take id from this record:

$sql = "INSERT INTO movies (title, year, format);

and put it inside this record:

$sql = "INSERT INTO actors(name, last_name, movi_id);
-----------------------------------------------^
                                              HERE
Eugene Lisitsky
  • 12,113
  • 5
  • 38
  • 59
Jaikrexe
  • 13
  • 8
  • 1
    Possible duplicate of [How do I get the last inserted ID of a MySQL table in PHP?](https://stackoverflow.com/questions/1685860/how-do-i-get-the-last-inserted-id-of-a-mysql-table-in-php) – Ignatius Nov 23 '17 at 03:00
  • @jaikrexe You need to use mysqli_insert_id() function to resolve your issue. – Amit Gupta Nov 23 '17 at 03:06

1 Answers1

1

Use below in-built PHP function for getting id from your last Insert Query:

mysqli_insert_id();

Your code must be like below to resolve your issue:

$sql = "INSERT INTO movies (title, year, format) VALUES ($title, $year, 
$format)";    
$movi_id = mysqli_insert_id();
$sql = "INSERT INTO actors(name, last_name, movi_id) VALUES ($name, 
$last_name, $movi_id)";

It's better if you can write above query in Prepared Statements to make it more secure:

// prepare and bind Movies Query
$queryMovies = $conn->prepare("INSERT INTO movies (title, year, format) 
VALUES (?, ?, ?)");
$queryMovies->bind_param("sss", $title, $year, $format);

// execute Movies Query
$queryMovies->execute();

// Get last inserted Id of Movies Query
$movi_id = $queryMovies->insert_id;

// prepare and bind Actors Query
$queryActors = $conn->prepare("INSERT INTO actors (name, last_name, movi_id) 
VALUES (?, ?, ?)");
$queryActors->bind_param("sss", $name, $last_name, $movi_id);

// execute Actors Query
$queryActors->execute();

// Close Connections
$queryMovies->close();
$queryActors->close();
Amit Gupta
  • 2,771
  • 2
  • 17
  • 31
  • Downvoted because while this answer isn't technically wrong (although it's not actually executing queries), it says nothing of escaping data or using prepared statements and doesn't use a transaction for something that clearly should be atomic. – Chelsea Urquhart Nov 23 '17 at 05:23
  • @ChelseaUrquhart If by helping someone I get downvoting then I am ready for it :) – Amit Gupta Nov 23 '17 at 05:31
  • Apologies... I wouldn't have downvoted it just for the lack of a transaction but SQL injection vulnerabilities are extremely dangerous, insanely easy to avoid, and should not be spread in examples that could easily be copy-pasted by the OP or others with the same question. – Chelsea Urquhart Nov 24 '17 at 14:55
  • @ChelseaUrquhart Yes you have done right thing then. We only learn from criticism. I appreciate that you spend time to ascertain the issue and revealed me the correct approach. Thanks! – Amit Gupta Nov 24 '17 at 15:03
  • @ChelseaUrquhart I have updated my answer with prepared statements. Please check. Thanks again for showing me the right way :) – Amit Gupta Nov 27 '17 at 03:01
  • @ChelseaUrquhart Thanks a lot once again :) – Amit Gupta Nov 28 '17 at 04:12