As the php.net site informs us, the mysql_connect() and similar functions are now deprecated and will be removed in a later version of php.

I will be upgrading the live LearnNet and ePortfolio servers over summer so I decided to stay ahead of any potential future issues by updating all of my own application and Moodle plugin code to the new mysqli functions (Which stands for MySql Improved Extension).

mysql_connect()

Here’s a quick, incomplete version of how database connections have been done up to now on the epmanager system.

   
function dbconnect() {
    $username=EP_DB_USERNAME;
    $password=EP_DB_PASSWORD;
    $database=EP_DB_DATABASE;
    $server=EP_DB_SERVER;

    mysql_connect($server,$username,$password) or die("no server");
    mysql_select_db($database) or die("could not connect to ep database");
}

function dbdisconnect() {
    mysql_close();
}

function get_level($user) { //return the user privilege
    $level =-1;
    dbconnect();
    $query = "select user_status from ep_admins where user_login = '$user'";
    $numlevels = mysql_query($query) or die("Select Failed!");
    $numlevel = mysql_fetch_array($numlevels);
    dbdisconnect();
    return $numlevel[0];
}

mysqli_connect()

Here’s the same code changed to use the new mysqli functions.

function dbconnect() {

    $username=EP_DB_USERNAME;
    $password=EP_DB_PASSWORD;
    $database=EP_DB_DATABASE;
    $server=EP_DB_SERVER;

    $link =  mysqli_connect($server,$username,$password) or die("no server");
    mysqli_select_db($link,$database) or die("no database connection");

    return $link;
}

function dbdisconnect($link)  {
    mysqli_close($link);
}

function get_level($user) { //return the user privilege
    $level =-1;
   
    $link=dbconnect();
    $user=mysqli_real_escape_string($link,$user);
    $query =  "select user_status from ep_admins where user_login = '$user'";
 
    $numlevels = mysqli_query($link,$query) or die("Select Failed!");
    $numlevel = mysqli_fetch_array($numlevels);

    dbdisconnect($link);
    return $numlevel[0];
  }

The database connection becomes an object ($link). The new mysqli functions have a dual interface one of which allows quick and easy upgrading of your code. The other uses an object orientated approach to working with your data which I would hope to use in the future. For now I have stuck to the easy procedural method in the example above because I have a vast amount to code to upgrade.

In the future the important lines of the above function could be updated to look like this:

$numlevels = $link->query("select user_status from ep_admins where user_login = '$user'");
$row = $numlevels->fetch_assoc();
$numlevel= $row['user_status'];
return $numlevel;

Updating my applications has led to a bit of a broader code cleanup. Revisiting some of the code in those applications after a number of years reveals areas where code could be simplified or made more robust simply because I’m learning new ways of doing things all the time.