PHP Migrating from mysql_* to mysqli_

Question

I just took up a old project and the first thing I needed to do was to migrate from the mysql_* extension to the mysqli_* one. I haven't worked with PHP much before... Mosts of the new code works but in the examples below I seems to mess things up...

Old function:

function user_id_from_username($username) {
    $username = sanitize($username);
    return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `username` = '$username'"), 0, 'user_id');
}

New(none working) function:

function user_id_from_username($username) {
    $username = sanitize($username);
    $id = mysqli_query(connect(),"SELECT `user_id` FROM `users` WHERE `username` = '$username'");
    return $id;
}

Another old one:

function login($username, $password) {
    $user_id = user_id_from_username($username);

    $username = sanitize($username);
    $password = md5($password);

    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'"), 0) ==1) ? $user_id : FALSE;
}

And the new one:

function login($username, $password) {
    $user_id = user_id_from_username($username);

    $username = sanitize($username);
    $password = md5($password);

    $check = mysqli_query(connect(),"SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'");
    return $check == $user_id ? TRUE : FALSE;
}

My sanitize Function:

function sanitize($data) {
    return htmlentities(strip_tags(mysqli_real_escape_string(connect(), $data)));
}

There's no equivalent for `mysql_result` in `mysqli`, [but you can write one](http://php.net/mysqli_result#109782) — Wrikken, Dec 07 '13 at 13:37
@Wrikken He isn't using mysql_result in his new code. Or am I missing your point? — thatonefreeman, Dec 07 '13 at 13:40
@Abbe What level of error flagging to you have enabled? You don't have any error suppression? What about logs? — thatonefreeman, Dec 07 '13 at 13:40
@thatonefreeman: he isn't, but in his new code he is assuming `mysqli_query()` works as the previous `mysql_result(mysql_query())` combo, hence my mentioning it. (I.e: currently the code is comparing a result set resource agains values, rather then the fetched results). — Wrikken, Dec 07 '13 at 13:41
@Abbe Have you tried using mysqli_fetch_row or mysqli_fetch_assoc to grab your result data? If you var_dump it, what do you get? — thatonefreeman, Dec 07 '13 at 13:43

score 5 · Accepted Answer · answered Dec 07 '13 at 13:47

Ok, so in the first function you are trying to replace

return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `username` = '$username'"), 0, 'user_id');

Let's first make clear what this does:

specify query
fetch the result
get 0. row ("1st" in English)
get column user_id

Now do this step-by-step with mysqli_:

//specify query
$result = mysqli_query(connect(),"SELECT `user_id` FROM `users` WHERE `username` = '$username'");
//fetch result
$row = mysqli_fetch_assoc($result);
//get column
return $row['user_id'];

You don't need to specify the row as fetch_assoc returns only one.

Now for the second function

return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'"), 0) ==1) ? $user_id : FALSE;

specify query
fetch result
get 0. row
if this equals 1: return user_id, otherwise FALSE

Now with mysqli_:

//specify query
$result = mysqli_query(connect(),"SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'");
//fetch result
$row = mysqli_fetch_row($result);
//if first returned column is equal to 1 return $user_id
//otherwise FALSE
return ($row[0]==1) ? $user_id : FALSE;

But wait - why did I use mysqli_fetch_row here whereas mysqli_fetch_assoc was used above? RTM ;)

What have we learned today? Only because you can write your code as short as possible doesn't mean you should. If the original code had been split up a bit more, the transition to MySQLi should have been quite easy, as you could have easily debugged smaller parts instead of a complex expression.

PHP Migrating from mysql_* to mysqli_

1 Answers1

Linked