Making Queries Fail?

Yep, you heard it right!

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

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?!



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.

If the field “photo_approved” is defined as a tinyint, then how in the world can this query run…

UPDATE member
SET photo_approved = '8888', last_activity = now()
WHERE id = 57

…without an error?!

photo_approved = 127

And how can this query run…

UPDATE member
SET photo_approved = 'xxx', last_activity = now()
WHERE id = 57

…without an error?!

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??? :wall:

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



Another method to make a query give an error on purpose would be for example to spell UPDATE as UPDATEE

Update a non existing table?
Insert more values than there are columns in the table?


Doing that means my code won’t even run!

I am trying to get this…

mysqli_stmt_affected_rows($stmt2) == -1

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.


That won’t even run. Again, that is not what I’m testing for.

See above.

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

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


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).

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.

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”.