check if username and email already exists in database

Question

I just started with php and i tried to create system to reject "create account" if you type already existing username or password.Thank you,and sorry for my bad english. I already tried this.Not working: link

This is my php:

<?php
session_start();

$db = mysqli_connect("localhost", "root", "", "itsnikola");

if (isset($_POST['register_btn'])) {
    $name = mysqli_real_escape_string($db, $_POST['name']);
    $lastname = mysqli_real_escape_string($db, $_POST['lastname']);
    $username = mysqli_real_escape_string($db, $_POST['username']);
    $email=mysqli_real_escape_string($db, $_POST['email']);
    $password=mysqli_real_escape_string($db, $_POST['password']);
    $password2=mysqli_real_escape_string($db, $_POST['password2']);

    if ($password == $password2) {
        session_start();
        $password = ($password);
        $sql="select * from account_info where (username='$username' or email='$email')";

        if (mysqli_num_rows($res) > 0) {
          $row = mysqli_fetch_assoc($res);
          if ($username==$row['username'])
          {
              $_SESSION['message'] = "Username je vec registrovan";
          }
          else($email==$row['email']){
              $_SESSION['message'] = "Email je vec registrovan"
          }
       else
        {
        $sql = "INSERT INTO users(name, lastname, username, email, password) VALUES('$name' , '$lastname' , '$username' , '$email' , '$password')";
        mysqli_query($db, $sql);
        $_SESSION['message'] = "Sada si ulogovan";
        $_SESSION['message'] = $username;
        header("location:login.php");
    }else {
        $_SESSION['message'] = "Ne podudaraju se lozinke!";
    }
}
?>

It would be helpful if you can explain exactly what is not working -- error message? not inserted into the database? not detected duplicate? etc. — user202729, Oct 20 '18 at 13:35

Jaswinder · Accepted Answer · 2018-10-20T20:43:43.600

First of all, session_start() is called 2 times.
Remove the repeated call inside if ($password == $password2) {
You code is also missing a ; and some } (for properly closing your if conditions)

Now the solution:
Before you can process Database query's result, you need to connect to a DB and execute appropriate SQL command, only then you will get the result you want. Your code is missing this process.
Check my comments in your code below↓ and then check again in corrected code

if ($password == $password2) {
        session_start(); // remove this repeated call
        $password = ($password);
        $sql="select * from account_info where (username='$username' or email='$email')";
        if (mysqli_num_rows($res) > 0) { // $res isn't defined
          $row = mysqli_fetch_assoc($res);
          if ($username==$row['username'])
          {
              $_SESSION['message'] = "Username je vec registrovan";
          }
          else($email==$row['email']){  // `else` doesn't work this way, use `elseif`
              $_SESSION['message'] = "Email je vec registrovan"  // ; missing
          }

Corrected Code:

if (isset($_POST['register_btn'])) {
    $name      = mysqli_real_escape_string($db, $_POST['name']);
    $lastname  = mysqli_real_escape_string($db, $_POST['lastname']);
    $username  = mysqli_real_escape_string($db, $_POST['username']);
    $email     = mysqli_real_escape_string($db, $_POST['email']);
    $password  = mysqli_real_escape_string($db, $_POST['password']);
    $password2 = mysqli_real_escape_string($db, $_POST['password2']);

    if ($password == $password2) {
        $password = ($password);
        $sql      = "SELECT * FROM users WHERE (username='$username' OR email='$email')";
        $res      = mysqli_query($db, $sql); // you were calling $res but it wasn't defined; this connects to the DB and executes SQL and then assigns the result
        if (mysqli_num_rows($res) > 0) {
            $row = mysqli_fetch_assoc($res);
            if ($username == $row['username']) {
                $_SESSION['message'] = "Username je vec registrovan";
            } elseif ($email == $row['email']) {  // changed `else` to `elseif` to include the condition, `else` doesn't accept conditional checks
                $_SESSION['message'] = "Email je vec registrovan";  // added ;
            }
        } else {
            $sql = "INSERT INTO users (name, lastname, username, email, password) VALUES ('$name', '$lastname', '$username', '$email', '$password')";
            if (mysqli_query($db, $sql)) {
                // New record inserted
                $_SESSION['message'] = "Sada si ulogovan";
                $_SESSION['message'] = $username;
                header("location: login.php");
            } else {
                echo("Error: " . mysqli_error($db));
            }
        }
    } // required to close the password checking condition
    else {
        $_SESSION['message'] = "Ne podudaraju se lozinke!";
    }
}

Suggestions:

Use prepared statement instead of directly passing user provided input into SQL
(critical, your current code is vulnerable to SQL injection)

Use an IDE that supports PHP and offers syntax highlighting (Atom, Visual Studio Code, PhpStorm etc.)

A l w a y s S u n n y · Answer 2 · 2018-10-20T13:48:09.297

1

You're missing the very important line here before this line if (mysqli_num_rows($res) > 0)

$sql="select * from account_info where username='$username' or email='$email' ";

$res = mysqli_query($db, $sql); // you're missing this line to query by $sql 

if (mysqli_num_rows($res) > 0) {
// you're other code goes here 
}

edited Oct 20 '18 at 13:48

answered Oct 20 '18 at 13:39

A l w a y s S u n n y

36,497
8
60
103

ItsNikola ツ · Answer 3 · 2018-10-20T20:28:43.853

I've tried this

<?php
session_start();

$db = mysqli_connect("localhost", "root", "", "itsnikola");
if (isset($_POST['register_btn'])) {
    $name      = mysqli_real_escape_string($db, $_POST['name']);
    $lastname  = mysqli_real_escape_string($db, $_POST['lastname']);
    $username  = mysqli_real_escape_string($db, $_POST['username']);
    $email     = mysqli_real_escape_string($db, $_POST['email']);
    $password  = mysqli_real_escape_string($db, $_POST['password']);
    $password2 = mysqli_real_escape_string($db, $_POST['password2']);

    if ($password == $password2) {
        $password = ($password);
        $sql      = "select * from account_info where (username='$username' or email='$email')";
        $res      = mysqli_query($db, $sql); // you were calling $res but it wasn't defined; this connects to the DB and executes SQL and then assigns the result
        if (mysqli_num_rows($res) > 0) {
            $row = mysqli_fetch_assoc($res);
            if ($username == $row['username']) {
                $_SESSION['message'] = "Username je vec registrovan";
            } elseif ($email == $row['email']) {  // changed `else` to `elseif` to include the condition, `else` doesn't accept conditional checks
                $_SESSION['message'] = "Email je vec registrovan";  // added ;
            } else {
                $sql = "INSERT INTO users(name, lastname, username, email, password) VALUES('$name' , '$lastname' , '$username' , '$email' , '$password')";
                mysqli_query($db, $sql);
                $_SESSION['message'] = "Sada si ulogovan";
                $_SESSION['message'] = $username;
                header("location:login.php");
            }
        } // required to close `if (mysqli_num_rows($res) > 0)`
    } // required to close the password checking condition
    else {
        $_SESSION['message'] = "Ne podudaraju se lozinke!";
    }
}
?>

But not working Error code:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in Z:\Programs\XAMPP\htdocs\register.php on line 17

check if username and email already exists in database

This is my php:

3 Answers3