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:
- How should I pick the vehicle_id from table 1.
- How will my insert statement go to write the data to table 2
Advertisement
Answer
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.