Php Ensuring a unique username

Question

I have a register page and I want to ensure that usernames are unique. I have tried all the solutions on here already and nothing is working for me.

This is the error I'm currently getting:

Warning:  mysqli_query() expects at least 2 parameters, 1 given 
in C:\xampp\htdocs\project\projectregistered.php on line 16
Query was empty

Here is my php:

 <?php
 $con=mysqli_connect("localhost","root","","test");
 // Check connection
 if (mysqli_connect_errno()) {
     echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }

$username = $_POST['username'];  

echo $username;

$query = mysqli_query("SELECT * FROM projectuser WHERE username='$username'");

$result1 = mysql_query($query) or die (mysql_error().$query);    
$count = mysql_num_rows($result1);

if ($count>0) {
echo 'Sorry! This Username already exists!';
} else {
    $surname = $_POST['surname'];
    $sql = "INSERT INTO projectuser (firstname, surname, username, password, location)
            VALUES
            ('$_POST[firstname]', '".mysql_real_escape_string($surname)."', '$_POST[username]', '$_POST[password]', '$_POST[location]')";
}

if (!mysqli_query($con,$sql)) {
    die('Error: ' . mysqli_error($con));
}
echo "1 record added ".$sql;

mysqli_close($con);

?>

Answer 1

Making the username column unique in your MySQL table is the easiest way to achieve this

CREATE UNIQUE INDEX uname_uniq ON projectuser (username);

This will prevent 2 rows having the same username, on INSERT or UPDATE

MySQL Reference

Answer 2

most mysqli_* functions expect a mysqli-instance as first parameter.

Or you can use $con->function() without extra parameter.

p.s.: Do not mix mysqli and mysql.

To ensure that the username is unique, simply add an unique-index to the column... (and check if $con->error was set)

Answer 3

As you're using the procedural approach, you also need to pass the connection handle as first argument. It should be

mysqli_query($con, "SELECT * FROM projectuser WHERE username='$username'");

Additionally, make the username column in your DB table UNIQUE.

Update:
I just realised you're using both mysql_ as well as mysqli_ functions. Better stick to the second one. So your code would be:

$query = "SELECT * FROM projectuser WHERE username='$username'";
$result = mysqli_query($con, $query);
$count = mysqli_num_rows($result);

But as already mentioned above, if you set your username column as UNIQUE you won't need that check at all. You could use mysqli_insert_id after the INSERT to check whether a record has been inserted or not. IF it hasn't been inserted, then the UNIQUE constraint had fired off. This way is more efficient than SELECTing first just to check whether the username already exists.