Mysqli insert appears to be working but no new entry to database


#1

I created 3 separate pages which require input from the user. The first and second pages work perfectly in receiving the input from the user and passing it on to it’s database. For the third page I am testing the form, not receiving any errors when submitting the input, however there are no new entries to the database at all.

I tested every single line of code and everything seems to be in order, its just the last step of inserting the query through:

$query = “insert into products (product_title ,product_description ,product_sku ,product_price ,product_images ,product_tags ,product_metafield ,category_id ) values(’$product_title’,’$product_description’,’$product_sku’,’$product_sku’,’$product_price’,’$product_image’,’$product_tags’,’$product_metafields’,’$category_id’)”;

mysqli_query($conn, $query);

Been racking my brain for the past 6 hours, any help is appreciated.


#2

You have an error in your sql query. All database statements (connection, query, prepare, and execute) need to ALWAYS have error handling, so that you will know if and why they are failing. The easiest way of adding error handling to all database statements is to enable exceptions for errors and in most cases let php catch and handle the exception where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. When learning, developing, and debugging code/queries, you would display all errors. When on a live/public server, you would log all errors. Switching between these two things only involves changing php’s display_errors and log_errors settings.

To enable exceptions for the php mysqli extension, add the following line of code before the point where you make the database connection -

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

Next, you shouldn’t put external/unknown data directly into an sql query statement. You should use a prepared query instead. Unfortunately, the php mysqli extension is overly complicated when dealing with prepared queries and you should switch to use the much simpler and more consistent php PDO extension.

In addition to providing security for the sql query, a prepared query simplifies the sql query syntax, making it easier to write error free queries. Each php variable and the single-quotes around the variable are removed from the sql query and is replace with a ? place-holder.


#3

First off thank you so much for your help!

The error handling statement provided caught that I included a variable twice so the columns and variables didn’t match up.

Regarding the PDO extension, I will start reading up on it now.

Thank you again!