Mental Block Again - Unique Information on two possible tables...

[GrumpyYoungMan]

I have two tables: users and vehicles, when the form is submitted it adds the user information to the user table and then the vehicle information into the vehicle table, which is simple, but the problem I have come across is how to handle the error management if

• the user information
• the vehicle information

already exists I do actually have a third table mots which only gets updated with the vehicle and user id from the first two queries...

I can not merge it into one query as if it fails I won't know why and secondly if it passes the user information but fails the vehicle I will then run into trouble if they resubmit the form as their details would be already into the database - but under a failed table? or do I just run a delete on the user table if hit an error? but that would cause a break in the auto-increment numbering on the user/and or vehicle tables?

Wasn't sure what code to post, so haven't posted any, more using this is a sounding board - also I am trying to insert the values and not select/insert.

[Dave]

If your tables are InnoDB use a transaction to "bundle" the inserts and roll back the changes if something doesn't go as expected.

And do not be concerned with whether or not your auto increment values are sequential, it doesn't matter at all.

[GrumpyYoungMan]

If your tables are InnoDB use a transaction to "bundle" the inserts and roll back the changes if something doesn't go as expected.

And do not be concerned with whether or not your auto increment values are sequential, it doesn't matter at all.

I'll look into that as that sounds better and cleaner than doing this:

Code: [Select]

echo 'It looks like it may be safe - so lets try to insert the vehicle...!';

$vehicleStatus = vehicles_Create( ['vrm' => vrm_Clean($_POST['vrm']), 'uid' => $clientStatus ] );

if( is_array($vehicleStatus) ) {

// Add the User Insert Errors to the maater insert error array
$insertErrors = array_merge($insertErrors, $vehicleStatus);

echo "Type: 1";
}
else {

$insertMOT = $DB->prepare("
INSERT INTO
mots
(
expiry, user_id, vehicle_id, added
)
VALUES
(
:expiry, :uid, :vid, NOW()
)
");

$insertMOT->execute( [ ':expiry' => $_POST['expiry'], ':uid' => $clientStatus, ':vid' => $vehicleStatus ] );

$MOTId = $DB->lastInsertId();

$updateVehicle = $DB->prepare("
UPDATE
vehicles
SET
mot_id=:motid
WHERE
id=:vehicleId
");

$updateVehicle->execute( [ ':motid' => $MOTId, ':vehicleId' => $vehicleStatus ] );

header("Location: ?module=MOT&action=View&id=$MOTId");

}

}

exit(mot_AddForm($insertErrors));
The only problem I see with the transaction method is I was using three queries - from three functions, so I would have merge them into the single query?

Because I am going in the order of user, vehicle and then the mot record? or if the transaction is started at the very query, the rollback will unroll all the queries?

[GrumpyYoungMan]

Code: [Select]

$DB->beginTransaction();

try {

// USER:
$createUser = $DB->prepare(user_Query_Create());

// Vehicle:
$createVehicle = $DB->prepare(vehicles_Query_Create());



    $createUser->execute([ ':firstname' => $_POST['firstname'], ':surname' => $_POST['surname'], ':email' => $_POST['email'], ]);

    $userId =  $DB->lastInsertId();

    $createVehicle->execute( [ ':vrm' => vrm_Clean($_POST['vrm']), ':uid' => $DB->lastInsertId() ]);
   
    $vehicleId = $DB->lastInsertId();
// MOT:
    $insertMOT = $DB->prepare("
INSERT INTO
mots
(
expiry, user_id, vehicle_id, added
)
VALUES
(
:expiry, :uid, :vid, NOW()
)
");

$insertMOT->execute( [ ':expiry' => $_POST['expiry'], ':uid' => $userId, ':vid' => $vehicleId ] );

$MOTId = $DB->lastInsertId();

$updateVehicle = $DB->prepare("
UPDATE
vehicles
SET
mot_id=:motid
WHERE
id=:vehicleId
");

$updateVehicle->execute( [ ':motid' => $MOTId, ':vehicleId' => $vehicleId ] );


    $DB->commit();


} catch (\Exception $e) {
   
    $DB->rollback();
    throw $e;

}
Something like that? needs tidying up and the variables for nothing removing...

[GrumpyYoungMan]

How would you handle this logic problem?

Obviously my form is only good for creating the vehicle, user and MOT record at the same time, but I could have the situation where the user could be already created along with the vehicle as I plan to use the vehicle for another part of my script so in theory they could just end up in the situation of needing an MOT record and that is before you throw in multiple vehicles? 

So do I add multiple tick boxes to my existing form saying existing vehicle, user? And if selected create the new records? Or Have a separate form so they can add MOT records via there user list?

[Dave]

Something like that? needs tidying up and the variables for nothing removing...

Yep, that's how you can do it. You've bundled all of your queries into a transaction and either they all work correctly or they fail and nothing happens.