How to check duplicate username in the database?

Question

I have a problem to save the data into the database. My case is when I save the data into the database table (user), the new username must cannot same with exist username in the table.

Below is my code to check whether insert username is the same for the existing username if not the same then can insert to the table, but my code doesn't work, anyone can guide me which part I get wrong.

save

$arr_val = $_POST;
$arr_val = $_POST;

$loc = $arr_val['loc'];
$action = $arr_val['action'];
$id = $arr_val['id'];
$type = $arr_val['type'];

unset($arr_val['loc']);
unset($arr_val['action']);
unset($arr_val['filter_id']);
unset($arr_val['type']);
$table = 'user';
if ($action == 'save') {

$query_user = mysqli_query($mysql_con, 'SELECT * FROM user WHERE username="' . $arr_val['username'] . '"');
$num_user = mysqli_num_rows($query_user);


if(count($num_user)>0){
                        echo "Duplicate username";
                    }elseif($num_user == 0) {
$key_relationship_1[] = 'is_active';
$val_relationship_1[] = convert_db_value('1');
$key_relationship_1[] = 'user_type';
$val_relationship_1[] = convert_db_value('1');
$key_relationship_1[] = 'created';
$val_relationship_1[] = convert_db_value($cur_dt);
$key_relationship_1[] = 'createdby';
$val_relationship_1[] = convert_db_value($user_name);
$key_relationship_1[] = 'modified';
$val_relationship_1[] = convert_db_value($cur_dt);
$key_relationship_1[] = 'modifiedby';
$val_relationship_1[] = convert_db_value($user_name);
$key_relationship_1[] = 'username';
$val_relationship_1[] = convert_db_value($_POST['username']);
$key_relationship_1[] = 'name';
$val_relationship_1[] = convert_db_value($_POST['name']);
$key_relationship_1[] = 'email';
$val_relationship_1[] = convert_db_value($_POST['email']);

$row_insert = db_conn_insert('user', $key_relationship_1, $val_relationship_1);
$sql_insert = $row_insert['sql'];
$error_insert = $row_insert['error'];
$record_id = $row_insert['record_id'];


unset($key_relationship_1);
unset($val_relationship_1);
                    }
elseif ($row_insert) {
    echo 'Saved successfully';

}

The output shows me like the below:

Output of save

This output I try to insert the same username with the table "user".

If remove the "count", the output show like below:

enter image description here

@HasithaJayawardana I have tried to remove `count`, you can see in my question there. — , Dec 10 '19 at 04:22
What is the PHP error you got? Did you use `if($num_user > 0)`? — Hasitha Jayawardana, Dec 10 '19 at 04:23
Where you get `loading fail` error? Don't you have a PHP error log? — Hasitha Jayawardana, Dec 10 '19 at 04:27
@HasithaJayawardana My code too long. Can you teamviewer help me? — , Dec 10 '19 at 06:06
I will not be able right now. But you can show us the code. No need for the whole code but the full code that belongs to this process. And you can use [pastebin](https://pastebin.com/) to show us the code. — Hasitha Jayawardana, Dec 10 '19 at 19:45

score 1 · Accepted Answer · answered Dec 10 '19 at 04:03

Never rely solely on the result of SELECT to determine if a record is unique. It's possible for another process to INSERT a record with the same value between the time when your SELECT completes and when your subsequent INSERT completes -- and then you've got duplicates. The only way you should be ensuring unique values in a database field is by using a UNIQUE constraint on it. Implementations vary, but here is an example in MySQL.

With a UNIQUE constrait in place, a duplicate INSERT attempt will fail, so you'll need to ensure that you're checking the return status of all your database function calls.

Also note that your code is vulnerable to SQL injection attacks. You should use prepared statements with bound parameters, via either mysqli or PDO. This post has some good examples.

score 0 · Answer 2 · answered Dec 10 '19 at 04:31

You are returning a number of users from the following query,

$num_user = mysqli_num_rows($query_user);

mysqli_num_rows will always return the number of rows in the result set.

But in the following statement,

if(count($num_user) > 0) {
   echo "Duplicate username";
}

you are using count() function. The count() function returns the number of elements in an array. Since $num_user isn't an array you should get an error as follows,

count(): Parameter must be an array or an object that implements Countable

You don't need to use count() function because $num_user is already a number.

Just use as follows,

if($num_user > 0) {
   echo "Duplicate username";
}

Hope this helps you!

How to check duplicate username in the database?

2 Answers2