Almost every web developer has to deal with data insert, update or delete on a Database. You create a beautiful form that is responsive and build with javascript evaluation and then you realize that all this data have to be inserted on a database. Here is where you have to deal with PHP and MySQL. So you need to get all these data and prepare them to insert into a database. In this post you will see how you can have time saving PHP auto functions for MySQL databases.
Usually you create an SQL query like this one below:
<?php $query = "INSERT INTO `table_name` (field1,field2,field3) VALUES ('".$value1."','".$value2."','".$value3."')"; mysqli_query($database_connection,$query); ?>
It’s ok when you have to deal with 4 to 5 variables. What about when you have to face more and not just one query but more. So, for this problem you can have some very smart and time saving function to insert your data just by calling the function. Let’s see the insert function.
PHP Auto INSERT function
The most important for this function is the structure of the $data variable. It has to be an array where the keys will be the fields name in the database columns.
<?php $data = array( 'field1' => $value1, 'field2' => $value2, 'field3' => $value3, 'field4' => $value4 ); ?>
With this format we are able to use the php function: array_keys() to retrieve the field names and implode the array itself to build our query. Let’s see how it can be done:
<?php function autoInsert($table_name, $data) { $fields = array_keys($data); $query = "INSERT INTO `".$table_name."` (`".implode('`,`', $fields)."`) VALUES('".implode("','", $data)."')"; return mysqli_query($database_connection,$query); } ?>
That’s it! Simple with a few lines of code. Of course before passing the data to the function, make sure that your data have been passed through a sanitazing function or at least the passed from the mysqli_real_escape_string() function. Doing this help us to prevent SQL injections.
PHP auto UPDATE function
While we saw the insert auto function let’s see more examples for updating data.
<?php function autoUpdate($table_name, $data, $where='') { // check for optional where clause $whereSQL = ''; if(!empty($where)) { // check to see if the 'where' has been passed if(substr(strtoupper(trim($where)), 0, 5) != 'WHERE') { // if not found, add key word $whereSQL = " WHERE ".$where; } else { $whereSQL = " ". strtoupper(trim($where)); } } $query = "UPDATE `".$table_name."` SET "; $sets = array(); foreach($data as $column => $value) { $sets[] = "`".$column."` = '".$value."'"; } $query .= implode(', ', $sets); // append the where statement $query .= $whereSQL; return mysqli_query($database_connection,$query); } ?>
To use the autoUpdate() function the $data variable has to be an array like the example we mentioned above. Also in this function we have the $where variable.
PHP auto DELETE function
<?php function autoDelete($table, $where_clause='') { $whereSQL = ''; if(!empty($where_clause)) { // check to see if the 'where' if(substr(strtoupper(trim($where_clause)), 0, 5) != 'WHERE') { // not found, add keyword $whereSQL = " WHERE ".$where_clause; } else { $whereSQL = " ".strtoupper(trim($where_clause)); } } // build the query $sql = "DELETE FROM ".$table.$whereSQL; return mysqli_query($database_connection,$query); } ?>
PHP auto SELECT function
<?php function autoSelect($table,$cols,$where_clause='') { $whereSQL = ''; $fields_arr = explode("^", $cols); //explode into an array the incoming string which is seperated with symbol ^ $comma_seperated_fields = implode("`,`", $fields_arr); //takes the array fields and creates the selected fields for SQL if(!empty($where_clause)) { if(substr(strtoupper(trim($where_clause)), 0, 5) != 'WHERE') // check to see if the 'where' word exists in the incoming variable { $whereSQL = " WHERE ".$where_clause; } else { $whereSQL = " ".strtoupper(trim($where_clause)); } } $sql = "SELECT `".$comma_seperated_fields."` FROM ".$table.$whereSQL; return mysqli_query($database_connection,$query); } ?>
With these four php auto functions you can save time and lines of code when you have to deal with data forms. Thanks for reading!
Why didnt you include mysqli_real_escape_string() in your examples?
It would have been trivial to do so, and then any newbies (which is what this article is aimed at) won’t just blindly copy and paste your code without that important part.
Hi Alex and thank you for your comment! We accept your opinion and your advice. We mentioned the mysqli_real_escape_string() inside the text. As you understand it’s just for how to write “clever” function. Each developer can do it with his way and skills!
Have you never heard of SQL injection? You can’t just use variables submitted from a form to construct SQL, especially just interpolating a where clause! This is insane!
Thank you for your comment! I understand you about SQL injection. This is just an example of how you can made some functions. The post will not teach you to code but to give you ideas of creating such functions!
Are you kidding me? You are showing how to expose a system to enable SQL Injection in 2019?
Thank you for your comment! I understand you about SQL injection. This is just an example of how you can made some functions to handle sql queries. You can use it even with PDO or other libraries!