PHP - Get a single result using mysqli

Question

I have descided to change from mysql to mysqli part way through a project - and run into this hicup.

What is the best way to achieve this: //check if username is taken $result = mysql_result(mysql_query("SELECT COUNT(*) FROM user WHERE username='".$username."'"));

I can not find an exact version of mysql_result

I have tried this:

$result = mysqli_num_rows($db->query("SELECT * FROM user WHERE username='".$username."'"))

Which works but I think using count would be easier on the mySQL server. Is there a better way?

Answer 1

If you are simplying trying to determine whether the username already exists I might suggest you change your query to "SELECT 1 FROM user WHERE username = '" . $username . "'":

$res = $db->query("SELECT 1 FROM user WHERE username='".$username."'");
if (mysqli_num_rows($res) > 0) {
    die('username already taken');
}

It's also good practice to escape any and all user input prior to querying your database as you are leaving yourself wide open for SQL injection attacks. Here is the safe version:

$res = $db->query("SELECT 1 FROM user WHERE username='". mysqli_real_escape_string($username) ."'");
if (mysqli_num_rows($res) > 0) {
    die('username already taken');
}

Answer 2

Make sure you have an index on user table that references the username column and you're all set. The code you've written will work.

Using count() will make no difference as username is supposed to be unique. If you make sure the index is also of the UNIQUE type, you will have enforcing of unique usernames.