Checking if username exists in DB with PHP and MySQL

Question

Been struggling with this for a few days, and read a ton of similar problems but still haven't been able to figure it out. I am trying to take user information via an html form and update my database with it, but only if the username doesn't already exist... Issue 1: No matter what, it can keep creating entries with the same username Issue 2: I keep getting the error Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, bool given

I am sure they are connected but for the life of me I can't figure out what the fix is.

<?php
//declare variables
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "mypassword";
$db = "user_info";

if (!empty($_POST)) {

  //connect to mysqli
  $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);

  //check connection
  if ($mysqli->connect_error) {
    die('Connect Error: ' . $mysqli->connect_errno . ': ' . $mysqli->connect_error );
  }

  //declare user variables
  $first_name = $_POST['first_name'];
  $last_name = $_POST['last_name'];
  $user_name = $_POST['user_name'];
  $pass = $_POST['pass'];
  $address = $_POST['address'];
  $phone = $_POST['phone'];

  //get data from form
  $sql = "INSERT INTO users (first_name, last_name, user_name, pass, address, phone) VALUES
    ('{$mysqli->real_escape_string($first_name)}',
    '{$mysqli->real_escape_string($last_name)}',
    '{$mysqli->real_escape_string($user_name)}',
    '{$mysqli->real_escape_string($pass)}',
    '{$mysqli->real_escape_string($address)}',
    '{$mysqli->real_escape_string($phone)}')";

// ** HERE IS WHERE I AM HAVING ISSUES ** 

  //query to see if entered username already exists
  $result = $mysqli->query("SELECT * FROM users WHERE user_name =$user_name");

  //alert if user name taken
  if (mysqli_num_rows($result) > 0) {
    echo "<b>Username already taken! Please select another.</b>";
  } else {

  //continue to insert into database if username is unique
  $insert = $mysqli->query($sql);

  //print response from mysql
  if ($insert) {
    echo "Success! Row ID: {$mysqli->insert_id}";
  } else {
    die("error: {$mysqli->errno}");
  }
}
  //close our app
  $mysqli->close();

}
?>

not a good idea to concatenate POST variables, use prepared statements — , Feb 28 '21 at 20:06
I don't understand the point of the SELECT. If the INSERT fails (because you've designated columns as unique) then you know everything you need to know. — Strawberry, Feb 28 '21 at 20:07
Your problem is called SQL injection. The reason why you see this strange message is because you broke your own SQL with SQL injection and you don't have error reporting enabled so mysqli doesn't tell you about the error. Also this is an XY problem as this SELECT shouldn't even be there. — Dharman, Feb 28 '21 at 20:10
Thank you all for the assistance. I'm super new to PHP and programming in general, so I will take what you've all said and work on my project! — Tyler Gettel, Feb 28 '21 at 20:27

score -2 · Answer 1 · answered Feb 28 '21 at 20:10

Try like this

<?php


 if (!empty($_POST)) {

    //connect to mysqli
    $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);

    //check connection
    if ($mysqli->connect_error) {
        die('Connect Error: ' . $mysqli->connect_errno . ': ' . $mysqli->connect_error );
    }

    //declare user variables
    $first_name = $_POST['first_name'];
    $last_name = $_POST['last_name'];
    $user_name = $_POST['user_name'];
    $pass = $_POST['pass'];
    $address = $_POST['address'];
    $phone = $_POST['phone'];

    $query = $mysqli->prepare("SELECT count(*) FROM users WHERE user_name = ?");
    $query->bind_param('s', $user_name);
    $query->bind_result($cnt);
    $query->execute();
    $query->store_result();
    $query->fetch();
    $query->close();

    if ($cnt > 0) {
        echo "<b>Username already taken! Please select another.</b>";
    } else {
        $query = $mysqli->prepare("INSERT INTO users (first_name, last_name, user_name, pass, address, phone) VALUES (?,?,?,?,?,?)");
        $query->bind_param('ssssss', $first_name, $last_name, $user_name, $pass, $address, $phone);
        $query->execute();
        if ($query->execute()) {
            echo "Success! Row ID: {$query->insert_id}";
        } else {
            die("error: {$query->errno}");
        }
        $query->close();
    }
}

Please don't answer closed questions and when you answer please at least explain your solution. — Dharman, Feb 28 '21 at 20:12

Checking if username exists in DB with PHP and MySQL

1 Answers1