Using mysqli to insert to MySQL database

Posted in Tutorials

In previous tutorial, we will saw how to query from MySQL database, now we use mysqli to insert to the “example” table of the a MySQL database. Like before, we call mysqli_connect with the database host, database username, password, and database name.  Remember that database host is “localhost” if the MySQL database is on the same server as your PHP file.  mysqli_connect will give us back a $database handle which we store in $db.

As before, use mysqli_connect_errno to detect if a non-zero error number has occurred.  If so, display error and exit out…

Assume that we got an username value and an email value from a form submission.  We must escape these untrusted user inputs with mysqli_real_escape_string (which takes the database handle and the value to be escaped as shown above) before using them to construct our “$query” string.

Run this query string through mysql_query(), telling it the database handle, and we get back an $result which will be false if something is wrong with the query.  In that case, you can call mysql_error($db) to find out the error message.  But display this only during development and not in production systems because you don’t want to reveal debugging info to hackers.

If query is successful, you can check how many rows were affected by mysqli_affected_rows (again passing in the database handle).

Since an insert doesn’t give back a full resultset like in a query, we don’t have to do mysqli_free_result() on the result set.  However, it is still good practice to close the database handle with mysql_close().

In the next tutorial, we will continue this code by adding an update statement.

 

---