CODEWITHSUNDEEP
phpmysqlforeign-keys
Nickson Nyakambi
Nickson Nyakambi

Reputation: 47

Inserting to multiple mysql table based on an primary key from first table

I am trying to write to two mysql tables.

Table 1: vehicles

-----------------------------------------------------------------
|  vehicle_id  |  vehicle_name  | vehicle_type  |  status  | 
-----------------------------------------------------------------

The field vehicle_id is auto incremented. I need to use this field in the next table speed_log.

This is the other table that.

Table 2: speed_log

 --------------------------------------
 |  id  |  vehicle_id  | speed  |
 --------------------------------------

As above, the id is auto incremented but I need to pick the vehicle_id from the first table when the script runs. The vehicle_id in the second table is the foreign key.

This is my syntax for writing data to the table 1

//query
$query = "INSERT INTO vehicles SET vehicle_name=:vehicle_name, vehicle_type=:vehicle_type, status=:status";

//prepare query
$stmt = $this->conn->prepare($query);

// bind values
$stmt->bindParam(":vehicle_name", $this->vehicle_name);
$stmt->bindParam(":vehicle_type", $this->vehicle_type);
$stmt->bindParam(":status", $this->status);

// execute query
if($stmt->execute()) {
    $this->response_message = "Vehicle was registered successfully.";
    return true;
}
else {
    $this->response_message = "Unable to register vehicle ".json_encode($stmt->errorInfo()).".";
}
return false;

Now my issues are two:

  1. How should I pick the vehicle_id from table 1.
  2. How will my insert statement go to write the data to table 2

Upvotes: 1

Views: 40

Answers (1)

O. Jones
O. Jones

Reputation: 108676

This is a job for LAST_INSERT_ID() or its PDO variant.

Do something like this

// execute query
if($stmt->execute()) {
    $this->response_message = "Vehicle was registered successfully.";
    $vehicleId = $this->conn->lastInsertID();
    /* now do another INSERT to your second table using the value of `$vehicleId`. */
    return true;
}

Whatever you do, do not do anything like

SELECT 1 + MAX(vehicle_id) FROM vehicles;  /* wrong! */

because that is a notorious way of making a huge mess (race conditions) if more than one user is using your php program concurrently.

Upvotes: 1

Related Questions