SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,930
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Making Queries Fail?

    Yep, you heard it right!

    I need some help learning how to make my queries FAIL!!

    In testing right now, and I need to make some queries fail in order to make sure my error-handling is working properly.

    1.) What is the easiest way to make an UPDATE query fail?? (Multiple ways are even better.)

    2.) What is the easiest way to make an INSERT query fail?? (Multiple ways are even better.)

    The UPDATE query is driving me particularly made, because everything I tried last night would not break my Prepared Statements?!

    Sincerely,


    Debbie

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,286
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    The only way to get an update statement to truly fail is to put invalid data in fields (alpha characters in numeric fields, nulls in non-nullable fields, etc.) You can also get errors if you try and use values in FK fields which don't correspond to a record on the foreign table - IF you have the FK constraints defined properly.

    For inserts, you have the same possibilities as above, plus duplicate errors on primary key fields, and not providing values in non-null fields.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,930
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    If the field "photo_approved" is defined as a tinyint, then how in the world can this query run...
    Code:
    UPDATE member
    SET photo_approved = '8888', last_activity = now()
    WHERE id = 57
    ...without an error?!

    Code:
    photo_approved = 127

    And how can this query run...
    Code:
    UPDATE member
    SET photo_approved = 'xxx', last_activity = now()
    WHERE id = 57
    ...without an error?!

    Code:
    photo_approved = 0

    And more importantly, how in the world do I get mysqli_stmt_affected_rows() to error out and return -1 like the Manual says it should when there is an error???????

    http://php.net/manual/en/mysqli-stmt.affected-rows.php


    Every UPDATE query I try and concoct to make it fail ends up running?!

    Sincerely,


    Debbie

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Another method to make a query give an error on purpose would be for example to spell UPDATE as UPDATEE
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Update a non existing table?
    Insert more values than there are columns in the table?

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,930
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Another method to make a query give an error on purpose would be for example to spell UPDATE as UPDATEE
    Nope!

    Doing that means my code won't even run!

    I am trying to get this...

    PHP Code:
    mysqli_stmt_affected_rows($stmt2) == -

    Which means I need the UPDATE query to *run*, but fail while running due to an SQL error.

    Typing gobbly-gook obviously will break any PHP code.


    Debbie

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,930
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Update a non existing table?
    That won't even run. Again, that is not what I'm testing for.


    Quote Originally Posted by guido2004 View Post
    Insert more values than there are columns in the table?
    See above.


    I would be curious to know why the examples I gave above yield the results they do?!

    I don't see how you can UPDATE a not-null field with a NULL, or UPDATE an Integer field with Text?!


    Debbie

  8. #8
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,615
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    What type of failures are you looking for besides syntax errors or invalid data?

    Also, just because there might be data submitted that you don't want doesn't mean it can't be added to a database. There's a difference between invalid types of data going into a database (like trying to insert text into a numeric database column) and not getting the values you want or expect (like 888888 instead of 8).
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  9. #9
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Nope!

    Doing that means my code won't even run!

    I am trying to get this...

    PHP Code:
    mysqli_stmt_affected_rows($stmt2) == -

    Which means I need the UPDATE query to *run*, but fail while running due to an SQL error.

    Typing gobbly-gook obviously will break any PHP code.


    Debbie
    You're only going to get -1 when a query fails to run because of an error. If the query runs then the output of that function with be 0 if nothing was affected or a number >= 1. From the manual:

    An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records where updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Typing gobbly-gook obviously will break any PHP code.
    Writing a gobbly-gook query does not break any PHP code, since it isn't PHP. It should cause MySQL to return with an error. I thought that was what you wanted. I don't understand what you mean by "that won't even run".


Bookmarks

Posting Permissions

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