0

I have 3 tables that are connected. The insurance table is connected with the customer table and rental details which are also connected with the customer table. I want to insert data into these tables at the same time. What should I put in ID's columns (not the main ID but correlated)? All main ID's are set on auto-increment. I tried with @@IDENTITY and mysql_insert_id. Here is my code

//inserting into insurance table
    if($connection->query("INSERT INTO insurance (Collision_Coverage, 
    Medical_Coverage) VALUES ('$collision_coverage','$medical_coverage')"))
    {
        //inserting into customer table
        if($connection->query("INSERT INTO customer (People_ID, Driving_License_No, Insurance_id) VALUES ('$current_user_id', '$driv_lic_num', @@IDENTITY)"))
        {
            //inserting into rental_details table
            if($connection->query("INSERT INTO rental_details (Vehicle_ID,Customer_ID, Hire_Date, Hire_Days, Total_cost, Return_date) VALUES ('$current_vehicle_id', @@IDENTITY, '$pick_up_date', '$days_hired', '$total_cost', '$returning_date')"))
Ruchi
  • 55
  • 5
kurwinox87
  • 21
  • 5
  • 1
    Is this mysqli or PDO? Whichever it is you should be using parameters instead of sticking strings in your queries like that, but regardless I think the last insert ID is different between the two. `mysql_insert_id` won't work because the old `mysql_` functions aren't in PHP any more, and you're not using them for the rest of the operation. – droopsnoot Nov 02 '21 at 12:45
  • What type of database are you using? e.g. `@@IDENTITY` is for MS SQL Server only, it would not work if you're using Mysql. – ADyson Nov 02 '21 at 12:50
  • This is mysqli, I am using XAMPP and phpMyAdmin – kurwinox87 Nov 02 '21 at 12:51
  • 1
    In that case: https://www.php.net/manual/en/mysqli.insert-id.php – ADyson Nov 02 '21 at 12:52
  • P.S. Terminology note: PhpMyAdmin is just a web based administration tool for managing MySQL databases. (It is one of many such administration clients). Your code connects directly to the MySQL database server and doesn't have anything to do with PhpMyAdmin. – ADyson Nov 02 '21 at 12:53
  • Duplicate of [mysqli last insert id](https://stackoverflow.com/questions/19738283/mysqli-last-insert-id) (and several others, all discoverable by a fairly simple google search) – ADyson Nov 02 '21 at 12:54
  • **Warning:** Your code is likely to be vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. – ADyson Nov 02 '21 at 12:55
  • https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. – ADyson Nov 02 '21 at 12:55

1 Answers1

0

use insert_id to get the last insert id

$lastId = $connection->insert_id;
echo $lastId;

here is the reference

Syed Muhammad Shakayb Athar
  • 101
  • 7