Streamlining MySQL Insert Queries

By Karthik Viswanathan

PHP and MySQL have often been known as two peas in a pod. Unfortunately, when you use PHP to insert information into a MySQL database, you often have to write large queries that take up much of your time. Not only that, but you also have to clean user input. This quick tip aims to streamline your efficiency by writing a simple function to do this job for you.

Write helper functions

Before we start working on the main program, we first need to create two helper functions which will be used later on:

Notice how the value is cleaned before being stored inside the $fields array. In addition, if the type of the MySQL field is some sort of string, the value must be enclosed in single quotes for the query to work properly.

Construct the query and run it

Now that we have all the information, we just need to construct a query based off of the keys (which are MySQL field names) and values (the corresponding information stored in each field) of the $fields array:

PHP’s implode function is perfect in this situation, as it places all array elements into a string separated by a given delimiter. By using a comma and space as the delimiter, the MySQL query is easily formed.

Conclusion

The function is now complete. It can be used in a form handler by simply passing in the $_POST or $_GET array as a parameter:

For those of you who prefer something more MySQLi-related (MySQL Improved), take a look at my active records database class on GitHub, which can streamline your select, insert, update, and delete queries. It supports where clauses, joins, ordering, limits, and more.