Useful MySQL Functions

The popularity of PHP as a server scripting has increased manifold mainly due to the fact that it is quite simple to develop database driven applications/websites. Database driven websites allow dynamic content which means that users as well as search engines get constantly new content.

In this guide, we look at some of the most basic and critical mySQL functions. We assume that you have connected to the mySQL server and linked the database. So, we will not look at mysql_connect and mysql_select_db functions.

mysql_query($query)

This is one of the most frequently used functions when working with a mySQL database. This function executes the query and returns the result. This function acts like the gateway to the database. The code snippet shows a sample usage:

  $query = “select empname from emp where sal < 10000”;    $result = mysql_query($query);  mysql_fetch_row

We got $result in the previous example. We will now see how to read the result of the query. For this, we use the mysql_fetch_row function. This function browses through the result of the mysql_query function record by record. Consider the example below:

  $query = “select empname from emp where sal < 10000”;    $result = mysql_query($query);    while ($empname = mysql_fetch_row($result)){    echo(“Employee  name is “.$empname);  }

As you would expect, all employee names whose salary is less than 10,000 will be printed.

mysql_affected_rows

What happens if you pass an update, insert or delete query to the mysql_query function? The result will be a boolean value depending on whether the operation was successful or not. In such cases, it is important to find out the number of rows affected by the query. For this purpose, we use the mysql_affected_rows function.  To use this function, there is no need to use any argument if you are working with only one connection. In case, you are using multiple connections in your application, then you need to pass the database link also.

  $no_of_rows_affected = mysql_affected_rows(); // when working with a  single connection    $no_of_rows_affected = mysql_affected_rows($dblink); // when working  with multiple connections

Using the above three functions along with the connection related functions, you should be able to develop basic database driven applications.