Instructobit
If you have experience using mysqli in PHP, you have probably run into errors with vague or no descriptions at all that take precious time to figure out. For this tutorial we will be going through several errors you may encounter, for examples we'll be using mysqli object-oriented style but most debugging methods can apply to procedural style as well.
This should be the first step in the process in which you check whether there was a specific error that caused your issue. The error file can usually be found through your local server (if you're using WAMP or XAMPP) or within your files on your server. This could point out any simple mistakes you've made which can be easily corrected, if this doesn't help then try another method below.
If you need more help accessing your error log, have a look at this
.
Sometimes an improperly configured connection to the database can result in the rest of your database interactions not functioning. If this is the case, review the arguments you are passing to the connect function, it is very likely that the given host, user or password is incorrect. To check whether you are properly connected use:
if( $con->connect_error ){
echo("connection error"); // object-oriented style
}
if( !$con ){
echo("connection error"); // procedural style
}
Sometimes there can be a simple SQL syntax error that you missed within your query, this could be something like a miss-spelled table, columns or keyword. Slowly analyse your query to make sure it is valid.
If you are using statements that you are not entirely familiar with or that you are new to, consider studying more about them or practicing them in your SQL console before attempting to use them in your scripts, many SQL statements have specific requirements that may not be obvious at first.
Sometimes you can inadvertently name tables or columns with the same name as a reserved keyword (eg. naming a column "type"). To avoid this, make sure you familiarise yourself with the MySQL reserved keywords and avoid using these for table or column names entirely.
If you cannot rename the table or column, encapsulate it with accent quotes (``) in your SQL statements to make sure MySQL does not see it as a keyword. It is a good practice to surround all your table, column and database names with these quotes anyway.
If you've made an error within your SQL that you can't seem to find, you can call the last error from your connection object, this can then be printed to your log file using error_log(), alternatively you could also echo the error, whichever is more convenient for you.
error_log( $con->error ); // object-oriented style
error_log( mysqli_error( $con ) ); // procedural style
In some cases this technique will give you helpful information which you can use to debug an SQL statement.
This is a common error when using prepared statements, this usually points to an error with your SQL syntax or not closing your statements. This error means that $con->prepare($sql) found that your SQL was not valid, and so instead of returning a statement object, it returns false. If you receive this error there is most likely an error in your syntax.
Sometimes it's a specific section of your SQL query that is the cause of an error. If you query is especially complicated, test the query without certain sections to see if it works, if it does work afterwards, then you will know the specific piece of the query that is the issue. If this does not work, try replacing your query with a simple select statement, if this does not work either, it will rule out your query as the issue.
To rule out your query as the root of the problem, copy it into your MySQL console and run it, if it does so successfully you can usually rule it out as your issue and focus on other pieces of your code.
Sometimes the variables you are using in conjunction with your SQL query can be improperly set, you should check this by echoing each of them or adding them to your error_log.
If you plan on using multiple queries in your script, you must remember to close your statements after executing if you are making use of prepared statements (more on those in
PHP secure database interaction
). If you do not close the used statement and you've made use of either parameter binding or result binding, the next time you create a statement, it will not run.
$sql="DELETE FROM testTable WHERE testColumn=1;";
$stmnt=$con->prepare($sql);
$stmnt->execute();
$stmnt->close(); // include this to ensure future statements function
Not closing your statements is a common error that can be mistaken for errors within future queries.
When inserting data into a table, you must make sure it matches the data type and length of the the columns you are inserting to. If you are trying to insert a float into an integer column, the database might not insert the data,
this won't return a relevant error message
which can lead to confusion.
If you are still struggling with an error, feel free to leave comment below.
