Categories: Just Sayin'

The mysql_result in mysqli

Many of you have run into a problem when you were working on some old legacy php script that you wanted to adapt to PHP 7 where mysql_ functions no longer work (since they are deprecated).

The new PHP 7 comes with two database drivers: MySQLi and PDO. The PDO supports many different databases while MySQLi supports only MySQL.

So you probably thought, I’m just going to replace all mysql_ functions with mysqli_ and it’s done. Well, I’ve got bad news for you. It won’t! Also this way is also not safe. So take your time and learn about the changes and how you can overcome them.

One of the issues I found is that sometimes I just need to get a simple entry from the database: like a name, or email or some count of items, and before I could get away with something like this:

$new_car_count = mysql_result(mysql_query("SELECT COUNT('new') AS count FROM cars"), 0);

or like this:

$email = mysql_result(mysql_query(“SELECT email FROM users WHERE id=123”), 0);

So in a one line of code, I could grab a simple piece of information from the database. Some may argue that this doesn’t look very nice, but let’s leave that aside, for now.

In PHP 7 it seems that this is no longer possible. One of the solutions I found is this:

function mysqli_result($res,$row=0,$col=0){ 
    $numrows = mysqli_num_rows($res); 
    if ($numrows && $row <= ($numrows-1) && $row >=0){
        mysqli_data_seek($res,$row);
        $resrow = (is_numeric($col)) ? mysqli_fetch_row($res) : mysqli_fetch_assoc($res);
        if (isset($resrow[$col])){
            return $resrow[$col];
        }
    }
    return false;
}

So, declaring the function with this name that will handle the job. Now I could just replace mysql_result with mysqli_result and everything will work.

But I what if don’t want to use that function. What re are the alternatives? Is there something already built in that I could just use.

This is what I have come up with:

$new_car_count = mysqli_fetch_assoc(mysqli_query($con, "SELECT COUNT('new') AS count FROM cars"))['count'];

or the other example

$email = mysqli_fetch_assoc(mysqli_query($con, "SELECT email FROM users WHERE id=123"))['email'];

Now I know many will argue that this is not that readable, but in case you need a quick fix for some of your legacy code to hold the water until you rewrite everything properly, this might just work.