CODEWITHSUNDEEP
phpmysqlsql
StackOverflowed
StackOverflowed

Reputation: 5975

MySQL - record farthest trip

Assuming I have the following 2 tables:

trips:

driverId (PK), dateTravelled, totalTravelDistance

farthest_trip (same structure)

driverId (PK), dateTravelled, totalTravelDistance

where only the most recent journey is stored:

REPLACE INTO JOURNEYS ('$driverId', '$totalTripDistance');

I want to store the farthest trip travelled for each driverId as well, but unfortunately you can't have a condition on an INSERT... ON DUPLICATE KEY UPDATE statement, or else I'd have a trigger such as:

INSERT INTO farthest_trip(driverId, dateTravelled, totalTravelDistance) ON DUPLICATE KEY UPDATE dateTravelled = new.dateTravelled, totalTravelDistance = new.totalTravelDistance WHERE totalTravelDistance < new.totalTravelDistance;

so what I do is after inserting into the first table, in PHP I check if the current distance is farther than the one previously recorded, and if so, then update farthest_journey. It feels like there should be a better way but I can't quite figure it out, so is there a more efficient way of doing this?

Upvotes: 0

Views: 55

Answers (1)

Daniel Basedow
Daniel Basedow

Reputation: 13396

You could create a trigger. Something like

CREATE TRIGGER farhest_trigger BEFORE INSERT ON trips
  FOR EACH ROW
  BEGIN
    UPDATE farthest_trips SET date=NEW.date, distance=NEW.distance WHERE driverId=NEW.driverId AND distance < NEW.distance;
  END;

But then you would have code that gets executed "magically" from a PHP perspective.

I think the better solution would be appending new trips to the trips table and selecting the maximum and latest journey with SELECT statements.

Upvotes: 3

Related Questions