Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts

    mySql $DBI error issue.

    Hi,

    I can't seem to work out how to make an error of my own, output, without the $DBI::errstr being shown.

    here is my insert
    Code:
     my $sth = $connect->prepare ("insert into  
                                  main_database.mailing_lists( business_id, email_address, first_name)
                                    values (?, ?, ?) 
                                  ");
    
    
      
      $sth->execute( $business_id
                   , $sender_email
                   , $sender_name
                   ) or print "This person is already on your mailing list\n";
    duplicate entries throw up the $DBD::errstr

    Code:
    <h1>Software error:</h1>
    <pre>DBD::mysql::st execute failed: Duplicate entry '456-aaa@example.com' for key 1 at mailing_list line 665.
    </pre>
    What have I forgotten.

    Edit:
    oh yeh, if I use insert ignore, the script doesn't error but I don't get the notification that this person is already in the db.

    bazz
    Last edited by bazz; 01-19-2011 at 03:42 PM.
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Does your connect statement have RaiseError and PrintError enabled?

    If a duplicate entry is found, do you want to update it with this (possibly) new data, or do you want to keep the old data?

    If you want to update, then use replace instead of insert.
    http://dev.mysql.com/doc/refman/5.0/en/replace.html
    REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 12.2.5, “INSERT Syntax”.

    REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL—that either inserts or updates—see Section 12.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

  • #3
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Thanks FishMonger.

    It's a mailing list sign-up form.

    If the same data is to be entered, I want a message to show, which states that the person is already in the mailing list. Otherwise I want the details to be added. A user may have signed up through the website and the business may enter someone's details through the control panel. I want to have the control panel entry show when someone is already in the db.

    I'll play about with the RaiseError and PrintError part of the connection. Maybe printError isn't enabled which is why it won't print out the message.

    Bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #4
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Code:
    PrintError => 1
    Still, the output is as follows, when a duplicate entry is attempted.

    Code:
    <h1>Software error:</h1>
    <pre>DBD::mysql::st execute failed: Duplicate entry '456-aaa@example.com' for key 1 at mailing_list line 665.
    </pre>
    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #5
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Oh, Blimey, I made the PrintError adjustment to the wrong db connection.

    I have now set it like this

    Code:
    'RaiseError' => 0, 'PrintError' => 1
    OK, I seem to have it resolved because I am now getting the correct message outputted. Just to test all other subs that use that connection in case their outputted message might change from that of $DBD::errstr or $DBI::errstr.

    As ever, thanks for the help FishMonger.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •