Check if this is duplicate

Question

I want to check if the username already exists and throw an error message if exist, any tips how can I do it? I've already tried to search but only found mysql_errno but it won't work for me.

  if ($valid) {
        $pdo = Database::connect();
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        //$password = md5($password);
        $sql = "INSERT INTO users (username,password,role) values(?, ?, ?)";
        $q = $pdo->prepare($sql);
        $q->execute(array($username,$password,$role));
        Database::disconnect();
        header("Location: index.php");
    }

sd

You should first run a `SELECT` to check if the username exists then, if not, run your `INSERT`. — Cynical, Dec 20 '18 at 10:40
But after the `SELECT username FROM users` what should I do? — André Miguel, Dec 20 '18 at 10:42
Possible duplicate of [Check for duplicates before inserting](https://stackoverflow.com/questions/7719039/check-for-duplicates-before-inserting) — Shibon, Dec 20 '18 at 10:43
@AndréMiguel, I think you should go through these tutorials first. `www.mysqltutorial.org` or `https://www.tutorialspoint.com/mysql/ ` — nazim, Dec 20 '18 at 10:44
If select count(username) from users wher username =@ > =1 then display a msg and call it an error or create a unique constrain and catch the error with a handler — ninjabber, Dec 20 '18 at 11:05

Mickaël Leger · Accepted Answer · 2018-12-20T11:08:02.367

1

If you want to check if the username is already used :

SELECT * FROM users WHERE username = :username

If you return one row : you throw your error, else you do your INSERT

So your code should look like this :

if ($valid) {
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql_check = "SELECT * FROM users WHERE username = :username";
    $test = $pdo->prepare($sql_check);
    $test->bindParam(':username', $username); 
    $test->execute;

Now here is two solution to check if you have one row :

    if($test->rowCount() > 0) {
        // error
    }

or

    $user = $test->fetch();

    if (!empty($user)) {
        // error
    }

And now if you don't have error, do you insert :

    else {
        //$password = md5($password);
        $sql = "INSERT INTO users (username,password,role) values(?, ?, ?)";
        $q = $pdo->prepare($sql);
        $q->execute(array($username,$password,$role));
        Database::disconnect();
        header("Location: index.php");
    }
}

edited Dec 20 '18 at 11:08

answered Dec 20 '18 at 10:43

Mickaël Leger

3,426
2
17
36

But how can I check the returned row? – André Miguel Dec 20 '18 at 10:59
@AndréMiguel with -`>fetch` – treyBake Dec 20 '18 at 11:00
And to display the error i just need to `echo` some error message right? or I'm missing something else? – André Miguel Dec 20 '18 at 11:13
I don't know your code, but you can use echo, display some flash message, if it's an ajax call you can return an error message and display it using js...you just show us the request part, no idea how your project looks ! – Mickaël Leger Dec 20 '18 at 11:16
It gives me this error now `Notice: Undefined property: PDOStatement::$execute in C:\xampp\htdocs\gestao\Colaboradores\create_user.php on line 68` – André Miguel Dec 20 '18 at 11:18
I'm not using ajax, do I could use an `echo – André Miguel Dec 20 '18 at 11:18
What did you use line 68 ? And yes you can try an echo to display some error message if the form is in the same page – Mickaël Leger Dec 20 '18 at 13:02

Jules R · Answer 2 · 2018-12-20T10:55:43.937

0

Two solutions:

You could make the username field unique, this way, MYSQL would throw an error if you try to insert a line with an existing username.

$sql = "INSERT INTO users (username,password,role) values(?, ?, ?)";
$q = $pdo->prepare($sql);
$result = $q->execute(array($username,$password,$role));
if(!$result) {
    // print out your error message
}

Otherwise, make a select with the wanted username and see if a row is returned (= already used) or not (=available).

$sql = "SELECT COUNT(*) as count FROM users WHERE username = ?";
$q = $pdo->prepare($sql);
$q->execute(array($username));
$result = $q->fetchAll();
// test result, if 1 > print error, etc

edited Dec 20 '18 at 10:55

answered Dec 20 '18 at 10:41

Jules R

553
2
18

I've already that field as ´UNIQUE´ but I want to display an error message on the php page, and not the usual error message from MySQL – André Miguel Dec 20 '18 at 10:43
@AndréMiguel `execute` return true or false if the query was successful or not, you can make a condition on it to display your own error message – Jules R Dec 20 '18 at 10:48
And i've tried this `$sql2 = "SELECT COUNT(*) as count FROM users WHERE username = ?"; $q2 = $pdo->prepare($sql2); $q2->execute(array($username)); $result = $q2->fetchAll(); if ($result >1){ echo '
ERROR
'; } else{ $sql = "INSERT INTO users (username,password,role) values(?, ?, ?)"; $q = $pdo->prepare($sql); $q->execute(array($username,$password,$role)); }` but it skips the error message – André Miguel Dec 20 '18 at 11:07
does result contain directly the count ? (hint: print_r it to see the content) – Jules R Dec 20 '18 at 12:01
`Array ( [0] => Array ( [count] => 1 [0] => 1 ) )` – André Miguel Dec 20 '18 at 12:05
Then test $result[0]['count'] ^^' – Jules R Dec 20 '18 at 12:25
Don't execute the insert query if there's already an existing row dude ^^ – Jules R Dec 20 '18 at 13:13
I'm not executing it, it's on `else` so it shouldn't be any existing row – André Miguel Dec 20 '18 at 13:33

score -2 · Answer 3 · answered Dec 20 '18 at 10:48

-2

SELECT * FROM users WHERE username = $username

If found show error, else insert

answered Dec 20 '18 at 10:48

Rubin bhandari

1,873
15
20

score -2 · Answer 4 · edited Dec 20 '18 at 12:41

-2

 <?php
     $getUniqueUsername=$con->query("select * from user where name='$user_name");
     $rowCount=$getUniqueUsername->num_rows;

     if($rowCount) {          
          $insertQuery=$con->query("insert into table_name (field_name) Values('".$values."'));
     }
?>

edited Dec 20 '18 at 12:41

treyBake

6,440
6
26
57

answered Dec 20 '18 at 11:41

Jawa

17
4

this is open to SQL injection – treyBake Dec 20 '18 at 12:42
also, code only answers aren't answer IMO -- add explanation so OP/future readers can understand the solution/problem at hand – treyBake Dec 20 '18 at 12:42

Check if this is duplicate

4 Answers4

ERROR

Linked

Related