CODEWITHSUNDEEP
phpmysql
eoiny1
eoiny1

Reputation: 5

Updating 2 tables from 1 html form using 2 mysql queries

I am trying to update 2 tables on my database using 1 form. There are a couple of things that happen before the queries execute:

  1. search the customers table using their phoneNum to see if they exist
  2. If there is a match it populates the booking form with the name, address, phoneNum from the customer table. If there is no match the form fields stay empty.
  3. Hit submit and the values in the booking form are posted to addbooking.php
  4. The first query in addbooking.php either INSERTS a new customer (if none matched in the previous search) or UPDATES the existing record (if there was a match)
  5. The second query should add a record to the job table using the same values.
  6. The first query works correctly doing an INSERT or an UPDATE
  7. The second query works if the customer exists and will not work if the customer has to be created.

The issue is with the cutomerId that is passed from the form. If the cutomerId is passed from the form (meaning they exist) the second query works. If they don't exist then nothing is passed but the first query creates a new customer hence generating a new customerId.

So my question is how do I get the customerId from the first query and use it in the second? I have all the code but hopefully I have explained the problem enough that the queries themselves will be enough. Many thanks!

    $query1 =   "INSERT INTO customer (customerId, name, address, phoneNum)" . 
            "VALUES ( '$customerId','$name', '$address', '$phoneNum')" .
            "ON DUPLICATE KEY UPDATE name='$name', address='$address', phoneNum='$phoneNum'";

$result = mysqli_query($dbc, $query1)
or die('Error adding new customer.');
//edit as per Jessica
$customerId = mysqli_insert_id($dbc);

$query2 = "INSERT INTO job (jobType, carType, customerId, phoneNum, name, address, destAdd, bookingDate, BookingTime)" . 
            "VALUES ('$jobType', '$carType', '$customerId', '$phoneNum', '$name', '$address', '$destAdd', '$bookingDate', '$bookingTime')";

$result = mysqli_query($dbc, $query2)
or die('Error adding booking.');

Upvotes: 0

Views: 95

Answers (1)

Jessica
Jessica

Reputation: 7005

mysqli_insert_id

$customerId = mysqli_insert_id($dbc);

PS: Why on earth are you inserting raw data in your queries instead of using parameters?

Upvotes: 2

Related Questions