I’m particularly interested in using of variables VAR_trip_time and VAR_cmimi from the first query
and then reuse those values in other queries below. the first code is how my query is, the second part is what i think to do .
BEGIN
DECLARE PRICE_val INT;
DECLARE trip_time_val INT;
UPDATE sku_rides R
INNER JOIN sku S ON S.skuID = R.skuID
SET R.endTime = NOW(),
R.zona = S.zona,
R.price = ROUND(TIMESTAMPDIFF(SECOND, R.startTime, NOW()) * price_per_1_sec),
R.trip_time = TIMESTAMPDIFF(SECOND, R.startTime, NOW())
WHERE R.skuID = skuID AND R.userID = userID AND R.endTime IS NULL ORDER BY rideID DESC LIMIT 1;
SELECT price INTO PRICE_val FROM sku_rides WHERE skuID = skuID AND userID = userID ORDER BY rideID DESC LIMIT 1;
UPDATE users SET kredit = kredit - PRICE_val WHERE userID = userID;
UPDATE sku SET userID = NULL WHERE skuID = skuID;
-- RETURN 1; // No need for RETURN statement in a procedure
END
i tried this
BEGIN
DECLARE VAR_trip_time INT;
DECLARE VAR_cmimi INT;
UPDATE sku_rides R
INNER JOIN sku S ON S.skuID = R.skuID
SET R.endTime = NOW(),
R.zona = S.zona,
VAR_trip_time = TIMESTAMPDIFF(SECOND, R.startTime, NOW()),
VAR_cmimi = ROUND(VAR_trip_time * cmimi_per_1_sekond),
R.cmimi = VAR_cmimi,
R.trip_time = VAR_trip_time
WHERE R.skuID = skuID AND R.userID = userID AND R.endTime IS NULL
ORDER BY rideID DESC
LIMIT 1;
UPDATE users SET kredit = kredit - VAR_cmimi WHERE userID = userID;
UPDATE sku SET userID = NULL WHERE skuID = skuID;
-- RETURN 1; // No need for RETURN statement in a procedure
END