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.

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:

function connect()
{
  $HOST="dbhost";
  $PORT="dbport";
  $DB_USER="dbusername";
  $DB_PWD="dbpassword";
  $DB_BASE="dbname";		  
  $DB_HOST = (!empty($PORT)) ? $HOST.":".$PORT : $HOST;
  return $connection = mysql_select_db(
    $DB_BASE,mysql_connect($host,$DB_UTILISATEUR,$DB_MDP)
  );    
}
//Example of use:
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.

function query($sql)
{
  if(mysql_query($sql)) { return true; } 
  else { die("SQL Error: <br>".$sql."<br>".mysql_error()); return false; }
}
//Example of use:
$sql = "USE mydb";
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.

function select($sql)
{
  $result=array();
  $req = mysql_query($sql) or die("SQL Error: <br>".$sql."<br>".mysql_error());
  while($data= mysql_fetch_assoc($req)) {
    $result[]=$data;
  }
  return $result;
}
//Example of use:
$sql = "SELECT * FROM mytable";
$results = select($sql);
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.

function insert($table,$data)
{
  $fields=""; $values="";
  $i=1;
  foreach($data as $key=>$val)
  {
    if($i!=1) { $fields.=", "; $values.=", "; }
    $fields.="$key";
    $values.="'$val'";
    $i++;
  }
  $sql = "INSERT INTO $table ($fields) VALUES ($values)";
  if(mysql_query($sql)) { return true; } 
  else { die("SQL Error: <br>".$sql."<br>".mysql_error()); return false;}
}
//Example of use:
$data = array(
	"field1"=>"value1",
	"field2"=>"value2",
	"field3"=>"value3",
);
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'.

public function update($table,$data,$where)
{
  $modifs="";
  $i=1;
  foreach($data as $key=>$val)
  {
    if($i!=1) { $modifs.=", "; }
    if(is_numeric($val)) { $modifs.=$key.'='.$val; }
    else { $modifs.=$key.' = "'.$val.'"'; }
    $i++;
  }
  $sql = ("UPDATE $table SET $modifs WHERE $where");
  if(mysql_query($sql)) { return true; } 
  else { die("SQL Error: <br>".$sql."<br>".mysql_error()); return false; }
}
//Example of use:
$newdata = array(
	"field1"=>"newvalue1",
	"field2"=>"newvalue2",
	"field3"=>"newvalue3",
);
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.

public function delete($table, $where)
{
  $sql = "DELETE FROM $table WHERE $where";
  if(mysql_query($sql)) { return true; } 
  else { die("SQL Error: <br>".$sql."<br>".mysql_error()); return false; }
}
//Example of use:
delete("mytable","myfieldid = 1")

Conclusion

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.

Why not share this here:

Posted Comments

@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(

"field1"=>"newvalue1",

"field2"=>"newvalue2",

"field3"=>"newvalue3",

);

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?

From jdmweb at 10:55 21/04/10 { Reply }

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?

From Amit at 06:09 21/04/10 { Reply }

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

From MiaZoe at 02:02 18/04/10 { Reply }