CUTCODEDOWN
Minimalist Semantic Markup

Welcome Guest
Please Login or Register

If you have registered but not recieved your activation e-mail in a reasonable amount of time, or have issues with using the registration form, please use our Contact Form for assistance. Include both your username and the e-mail you tried to register with.

Author Topic: Handling SQL duplicate record error  (Read 1130 times)

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Handling SQL duplicate record error
« on: 27 Jan 2020, 03:55:48 am »
Code: [Select]
// Insert User, if all other tests are happy
if( ( $_POST['name'] != "" && $_POST['email'] != "" ) && ( $errors['count'] == 0 ) )  {

try {

$insertUser = $DB->prepare("
INSERT INTO
users (
u_user, u_email, u_password, u_added
)
VALUES (
:user, :email, :password, NOW()
)
");

$insertUser->execute( [
':user' => $_POST['email'],
':email' => $_POST['email'],
':password' => password_hash(randomPassword(), PASSWORD_DEFAULT)
] );
 
}
catch (PDOException $e) {

    if ($e->getCode() == "23000" ) {

    $errors['count']++; $errors['desc'] = [ "user_duplicate_record" ];
   
    }
}

}

Can I wrap this test into a database wrapper - as having to duplicate this code on every possible scenario where this may happen seems cumbersome? or this a better way to do this kind of thing?
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

Jason Knight

  • Administrator
  • Hero Member
  • *****
  • Posts: 1049
  • Karma: +188/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: Handling SQL duplicate record error
« Reply #1 on: 27 Jan 2020, 05:07:47 am »
I'm of the opinion that if a try/catch is so "complex" you need a wrapper for it, you probably shouldn't be writing PHP.

That said, use !empty() instead of =="" just so that you don't risk a fatal comparison, and rather than counting errors and constantly overwriting them, why not use the error array as an ARRAY of errors so you can track multiple errors at once and use count($errors) instead?

so initialize as:
Code: [Select]
$errors = [];
use loose compare with count to test for errors:
Code: [Select]
if (count($errors)) {
name your errors
Code: [Select]
$errors['newUser'] = 'user_duplicate_record';
That way you can handle multiple errors in the output instead of being restricted to just one.
We are all, we are all, we are all FRIENDS! For today we're all brothers, tonight we're all friends. Our moment of peace in a war that never ends.

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: Handling SQL duplicate record error
« Reply #2 on: 27 Jan 2020, 06:06:39 am »
I'm of the opinion that if a try/catch is so "complex" you need a wrapper for it, you probably shouldn't be writing PHP.

That said, use !empty() instead of =="" just so that you don't risk a fatal comparison, and rather than counting errors and constantly overwriting them, why not use the error array as an ARRAY of errors so you can track multiple errors at once and use count($errors) instead?

so initialize as:
Code: [Select]
$errors = [];
use loose compare with count to test for errors:
Code: [Select]
if (count($errors)) {
name your errors
Code: [Select]
$errors['newUser'] = 'user_duplicate_record';
That way you can handle multiple errors in the output instead of being restricted to just one.
I  like that - this is why I post here. Ill update the code later and post it back...

Thank you so much Jason!
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

Dave

  • Junior Member
  • *
  • Posts: 38
  • Karma: +12/-0
Re: Handling SQL duplicate record error
« Reply #3 on: 27 Jan 2020, 10:17:31 am »
FWIW I usually let my top level error catching handle errors for single statement like you have in your try/catch block. Only if there are multiple things being done which rely on everything completed without error do I use try/catch so I can roll back things if needed.
« Last Edit: 28 Jan 2020, 05:24:44 am by Dave »
Dave

benanamen

  • Full Member
  • ***
  • Posts: 188
  • Karma: +18/-0
Re: Handling SQL duplicate record error
« Reply #4 on: 27 Jan 2020, 01:27:18 pm »
One small change from Jason's code.

This
Code: [Select]
if (count($errors)) {
Can simply be
Code: [Select]
if ($errors) {
To save time, let's just assume I am never wrong.

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: Handling SQL duplicate record error
« Reply #5 on: 31 Jan 2020, 11:15:16 am »
Am I right in thinking only one query can be added per try{} loop?
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

Jason Knight

  • Administrator
  • Hero Member
  • *****
  • Posts: 1049
  • Karma: +188/-1
    • CutCodeDown -- Minimalist Semantic Markup
Re: Handling SQL duplicate record error
« Reply #6 on: 31 Jan 2020, 01:39:01 pm »
Am I right in thinking only one query can be added per try{} loop?

You can do multiples, "try" will catch any and all system level errors.

BUT it leaves you with a huge problem; You won't know which one failed.
We are all, we are all, we are all FRIENDS! For today we're all brothers, tonight we're all friends. Our moment of peace in a war that never ends.

GrumpyYoungMan

  • Hero Member
  • *****
  • Posts: 787
  • Karma: +8/-0
    • Grumpy Young Man
Re: Handling SQL duplicate record error
« Reply #7 on: 31 Jan 2020, 02:12:47 pm »
Am I right in thinking only one query can be added per try{} loop?

You can do multiples, "try" will catch any and all system level errors.

BUT it leaves you with a huge problem; You won't know which one failed.
and that is exactly why I thought it didn't think it work... I had a query error...  :/
Trying to learn a new trick to prove old dogs can learn new ones...

Total Novice have-a go Amateur Programmer - not sure that is the right thing to say... but trying to learn...

 

SMF spam blocked by CleanTalk

Advertisement