My Favourite MYSQL functions

My Favourite MYSQL functions

For the previous years I've been trying to point out the areas of my workflow that were taking most of my time to try to optimise them.

One of these areas was database interactions meaning the famous 'SELECT', 'INSERT', 'UPDATE' and 'DELETE' statements. When I was still discovering PHP, I spent far too much time hard coding every single database query. It was time consuming, but also very annoying.

Then, for a project, I discovered Zend, a powerful PHP framework. I loved the way it handled the queries, by having a database class with predefined methods for each interaction. The only problem was that Zend is far too bulky and complicated for simple projects.

After investigating if there were easier ways of achieving the same results, I am presenting here today a list of my favourites mysql functions, with their brief description and how you can use them. I hope that it will save you time in your future developments like it did for me.

Thank You for Downloading

I'm glad that you found my work useful. If you did, it might help some other people too. So why not helping me spread the word with a tweet? You could also buy me a coffee to thank me for my code and explanations if you prefer ;).
I hope you'll enjoy your download. Regards. Jeremy.

Database Connection

First things first, if you want to interact with a database, the first thing to do is to connect to it using the native php functions mysql_select_db() and mysql_connect(), which make us in total of around 5 parameters. If you rewrite all the connection procedure every time you want to make a query, it's a pure waste of time and it will be a nightmare to maintain. The solution? Place all the code in a connect() function such as this one:
  1. function connect() {
  2. $HOST="dbhost";
  3. $PORT="dbport";
  4. $DB_USER="dbusername";
  5. $DB_PWD="dbpassword";
  6. $DB_BASE="dbname";
  7. $DB_HOST = (!empty($PORT)) ? $HOST.":".$PORT : $HOST;
  8. return $connection = mysql_select_db(
  9. $DB_BASE,mysql_connect($host,$DB_UTILISATEUR,$DB_MDP)
  10. );
  11. }
  12. //Example of use:
  13. connect();
As you can see from line 3 to 7, we specify the required parameters used to connect to the database (the host and port, the user and password, and the database name). At line 8, we just specify that if a port is being used, the port number will be added after the host and a column to become a string like 'host:port', otherwise, we just use the hostname. We then connect to the database and return the result of the connection attempt. Now, every time you wish to connect to the database, you can use the connect() function.

Simple Database Query

What is interesting with the query statement, is that it can be used in a lot of different ways. By using query, we can actually perform all the statements that we are looking at at the moment providing that the slq code you are sending is valid. I personally like to have a query function in case I need to send a particular request to the database, but I do not use this query function to perform the usual interaction.
  1. function query($sql) {
  2. if(mysql_query($sql)) { return true; }
  3. else { die("SQL Error: <br>".$sql."<br>".mysql_error()); return false; }
  4. }
  5. //Example of use:
  6. $sql = "USE mydb";
  7. query($sql);
Very simply, if the query is successful, the function returns true, otherwise, it shows you an error message so you know what went wrong, and then it returns false.

The Select Statement

The process of selecting data from a table can be summarized in several steps. First you need to write the SELECT query, then you need to send this query to the database, and then you need to get the results back. When you think about it, 2 of those 3 steps can be similar. It is therefore a waste to copy paste the same code all the time. The following select() function takes your sql query as a parameter, and gives you back a php array of results that you can use right away.
  1. function select($sql) {
  2. $result=array();
  3. $req = mysql_query($sql) or die("SQL Error: <br>".$sql."<br>".mysql_error());
  4. while($data= mysql_fetch_assoc($req)) {
  5. $result[]=$data;
  6. }
  7. return $result;
  8. }
  9. //Example of use:
  10. $sql = "SELECT * FROM mytable";
  11. $results = select($sql);
  12. print_r($results);
Your sql query is sent to the database on line 4 thanks to the mysql_query() function. If there's anything wrong with the query, the function will let you know so you can correct it. Otherwise, from line 5 to 7, it loops through the results and store them in a php array along the way. The final array containing all the data is then returned. This little function is very easy to use. You just pass it the sql query and you'll know you'll get your results on the other end.

The Insert Statement

What annoyed me a lot with Insert statements is that you have to copy and match every field name and their value in what can be a very long string. I found it much more convenient to build a two dimensional array rather than a string. With this idea in mind, here's the insert() function, which takes two parameters, a table name, and an array of data, which keys are in fact the field names. In other words, you build your own array under the format = array('field-1'=>'value-1', ..., 'field-n'=>'value-n'), and then you send this array to the insert function, which will generate the according sql statement for you, and insert your new values in the table.
  1. function insert($table,$data) {
  2. $fields=""; $values="";
  3. $i=1;
  4. foreach($data as $key=>$val)
  5. {
  6. if($i!=1) { $fields.=", "; $values.=", "; }
  7. $fields.="$key";
  8. $values.="'$val'";
  9. $i++;
  10. }
  11. $sql = "INSERT INTO $table ($fields) VALUES ($values)";
  12. if(mysql_query($sql)) { return true; }
  13. else { die("SQL Error: <br>".$sql."<br>".mysql_error()); return false;}
  14. }
  15. //Example of use:
  16. $data = array(
  17. "field1"=>"value1",
  18. "field2"=>"value2",
  19. "field3"=>"value3",
  20. );
  21. insert("mytable",$data);
From the function code, we can see that lines 5 to 11, we build two strings from the passed array. The fields string is made with the array keys, and the values string is made with the corresponding values. Thanks to this approach, your fields and values are always matched by pairs, which is much more flexible as it is built dynamically. Then, once the insert statement is generated, we send it to the database line 13.

The Update Statement

Because this statement is quite similar to the insert one, I had the same issues with it in the past, finding it not very convenient to maintain. But because it is similar it means that we can tackle the problem with the same approach, working with an array first, and then sending this array to the function which works out what to do from there. The notable difference between the two is the appearance of a third parameter called $where, which must contain a string allowing you to target the row you want to update, for example 'fieldid = 1'.
  1. public function update($table,$data,$where) {
  2. $modifs="";
  3. $i=1;
  4. foreach($data as $key=>$val)
  5. {
  6. if($i!=1) { $modifs.=", "; }
  7. if(is_numeric($val)) { $modifs.=$key.'='.$val; }
  8. else { $modifs.=$key.' = "'.$val.'"'; }
  9. $i++;
  10. }
  11. $sql = ("UPDATE $table SET $modifs WHERE $where");
  12. if(mysql_query($sql)) { return true; }
  13. else { die("SQL Error: <br>".$sql."<br>".mysql_error()); return false; }
  14. }
  15. //Example of use:
  16. $newdata = array(
  17. "field1"=>"newvalue1",
  18. "field2"=>"newvalue2",
  19. "field3"=>"newvalue3",
  20. );
  21. update("mytable",$newdata,"myfieldid = 1");
In the same way than in the insert function, between the lines 5 and 11, the array is used to build the update statement. The modifications are matched by pairs to build the $modifs string. Those modifications are then applied to the fields that are targeted by the where clause.

The Delete Statement

This statement is more straight forward than the previous ones. The delete() function takes two parameters, the table from where to delete (for ex 'mytable'), and a string $where allowing you to specify the condition you want to use to target the fields to delete (for ex: 'fieldid = 1'). The function then generates the according delete statement and sends it to the database.
  1. public function delete($table, $where) {
  2. $sql = "DELETE FROM $table WHERE $where";
  3. if(mysql_query($sql)) { return true; }
  4. else { die("SQL Error: <br>".$sql."<br>".mysql_error()); return false; }
  5. }
  6. //Example of use:
  7. delete("mytable","myfieldid = 1");


Using some custom functions to handle the database interaction is very useful in terms of maintenance and optimisation as you do not have to copy and paste the statements every time you need them. You can just call a function, and you know that it will deal with your request. It's also very handy when you wish to change the sql code within those functions, because you only have to do it in one place instead of browsing your code to change it everywhere. Finally, I personally think it is more convenient to work with arrays and to pass them to a function rather than having to produce the statement query yourself. They can easily be improved, for example, I have created a database php class that includes them all. I hope you'll find those sql functions as handy as I am.

4 Responses to My Favourite MYSQL functions

  1. These are very noteworthy functions for anyone delving into coding , MSQL and all things WordPress ( especially ) ; Being a <i>Noob</i> myself , this info is invaluable to me - and thank you for making the effort of putting this together ( <i>quite meticulously I would say </i>) .
    You are also a great <i>instructor </i> , aside from your other <i>skills</i> .
    Thank You for this article & the data resource :)

  2. This is really great. I am going to start using these right away.

    In the Insert function, could you explain the i iterator though, I know its a counter that is holding the current value of the array through each loop. But I don't understand the
    if($i!=1) { $fields.=", "; $values.=", "; }. Why is this needed?

    What does it do?

  3. @Amit.

    Hey Amit, I'm glad you like them, it will save you loads of time you'll see.

    The line you don't understand adds commas in the fields string and values string before each element but the first.

    In other words, if you are using :

    $newdata = array(

    It will generate $fields = field1 comma field2 comma field3. The particular line will add a comma before field2 and field3, but not before field1 because at this stage $i = 1.
    Same for $values which will be value1 comma value2 comma value3.

    Does that make sense?

  4. Thanking you with just a coffee seems inadequate (but that's what I've done). Thanks so much for this plugin! P.S. If you ever do add json import/export functionality to it, I would love to hear about it. Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *

Other Useful Resources

How to turn any jQuery plugin into a WordPress one illustration

WordPress and jQuery are both very famous for their plugins. In the case of jQuery, plugins allow developers to extend the library's capacities in order to create beautiful effects. In WordPress, they allow anyone to benefit from the work of others, as they come under the form of a module that you can easily activate from within the administration interface, and start using right away.

Creating one of those modules is not that difficult, but if you are not familiar with their syntax, it could end up being a headache. There are many jQuery plugins that have been transferred into a WordPress one already, but what can you do when it is not the case? How do you do when you have found the perfect jQuery plugin for your latest site, but it is not yet available as a WordPress plugin?

In this tutorial, we'll see how you can easily create a WordPress plugin from a jQuery one. We'll review what a jQuery, or a WordPress plugin is actually made of, so we'll find the elements they both have in common. From there, we'll show how it leads us to perform some simple operations to allow us to benefit from our jQuery plugins in a WordPress template.

Read more
Creating a simple image gallery with PHP & jQuery illustration

An image gallery is a very common feature for a website to have. It is an efficient way to nicely present a set of pictures. With the increasing popularity of JavaScript plugin, image galleries are nowadays implemented with rich features, effects, and transitions, in order to emphasize the given content.

When you wish to create a picture gallery, there are several steps involved in the process. In this tutorial, we will see how you can set everything up, and then add, remove, or edit pictures in your gallery, in a simple and dynamic manner. Finally, we will explain how we can setup the jQuery lightbox plugin in order to work with our pictures.

What are the benefits of this solution? This methods is sooo easy to setup, but still achieves quite a lot. Little efforts, great rewards. You only have to put your images in a folder, and give that folder path to the class. You also tell the class how big you want your pictures to be, and it handles all the hard work for you. Just by calling a method, you can see your gallery on the page. So if you are looking for a fast and convenient way to put an image gallery on a site, read on.

Read more
How to integrate your instagram pictures with php illustration

Ever looked for a straightforward way to integrate you instagram pictures on your website? Without a plugin nor an iframe? Well this article featured on the NOE interactive's blog presents how you can do so with PHP.

It's a neat and simple solution that uses the instagram's API to get a particular users' latest pictures, and then display them in a ul list.

It is accessible to most developers as it is a "personalize, copy and paste" kind of solution. Just two steps are required, configuration, and implementation.

In this article featured on NOE interactive, you'll learn how you can install the solution on your website. There's a complete step by step guide as well as a demonstration. If you wish to download the solution, it's also hosted on Github.

Read more
@marcelpociot @botman_io Sure, I've got big plans. Do you think I should hold until the botman 2 release? (When wou… Jul / 09:54