PHP/MySQL: Check if username exists

Question

I'm a beginner in php and I want to check if the username entered already exists.

Here is my code.

<?php
ini_set('display_errors',1); 
error_reporting(E_ALL);

if (isset($_POST['submit'])) {
include "connect.php";
ValidateUser();
}


function ValidateUser() 
{
if (!empty($_POST['username']) AND !empty($_POST['password'])) {
$queryrow=mysqli_query("SELECT * FROM websiteusers WHERE username = '$_POST['username']'");
if ($rows=mysqli_num_rows($queryrow)=0) {
     RegisterUser();
  }
}

function RegisterUser() {
echo "works up to here";
}
?>

It doesn't even give me an error despite turning error reporting on.

also, array inside string requires curly braces. So `"...WHERE username='{$_POST['username']}'"`. But please just fix your queries to bind or else you are just asking to be screwed with on sql injection. — Jonathan Kuhn, Sep 17 '14 at 23:42
+ John Conde. By using: '$_POST['username']', you are allowing people to inject SQL into your query. They could do something like: **SELECT * FROM websiteusers WHERE username = '1' OR 1 = 1#** And even drop your tables. See: http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection — Kohjah Breese, Sep 17 '14 at 23:44
If you want to check if the user exists, use `if (mysqli_num_rows($queryrow) > 0)` — Funk Forty Niner, Sep 17 '14 at 23:48

score 0 · Answer 1 · answered Sep 17 '14 at 23:43

Have you even initialized a mysqli_connect?

$Connection =  mysqli_connect("host","user","pass","database");

Then pass it to a function which uses mysqli_query() by:

function foo ($DB){
  mysqli_query($DB,"QUERY HERE");
  // Do other stuff
  return /* Whatever you wish to return here*/
}
foo($Connection);

meda · Answer 2 · 2018-07-10T00:16:30.310

0

Here is the right way to do this:

<?php
ini_set('display_errors',1); 
error_reporting(E_ALL);

if (isset($_POST['submit'])) {
    $mysqli = new mysqli("localhost", "user", "password", "database");
    if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }

    if(check_user($mysqli, $_POST['username']){
        registerUser();
    }else{
        echo 'user exist, cannot register';
    }

}

function check_user($conn, $username){
    $query = "SELECT * FROM websiteusers WHERE username = ?";

    if ($stmt = $conn->prepare($query)) {
        $stmt->bind_param("s", $username);
        $stmt->execute();
        $stmt->close();
    }

    return $stmt->num_rows === 0;
}


function registerUser() {
    echo "registering user ...";
}

Read up on prepared statement

edited Jul 10 '18 at 00:16

answered Sep 17 '14 at 23:55

meda

45,103
14
92
122

1

@mickmackusa ok I fixed it, *3 years, 9 months* later :-S – meda Jul 09 '18 at 23:03
Wait a sec... does `$stmt->rowCount()` still work after you close the statement and the connection? I've never seen it done like that and can't test it from my phone. – mickmackusa Jul 09 '18 at 23:23
1

or you can correct it `:P`, I haven't coded in PHP in a while – meda Jul 10 '18 at 00:07
1

you obviously know the answer so you can contribute in less keystrokes than all your critics, this is part of stackoverflow community but I guess you call it janitor work. – meda Jul 10 '18 at 00:14
It is my opinion that this is important feedback to any user -- not just you. I will be ineffective if I repair content as an individual. If I urge other users to contribute to improved content, more ground gets covered. I am actually surprised that we are discussing this for so long. I thought you would have to decided to edit or delete by now. This isn't intended to be a "me-vs-you" thing, StackOverflow is supposed to be a non-zero-sum game. – mickmackusa Jul 10 '18 at 00:16
Just got home to check for my own benefit. Now your updated code faults with: _Couldn't fetch mysqli_stmt_ It's the premature statement closure and the _result_ must be _stored_ before you can call `num_rows`. Sorry to nitpick, but your answer says _Here is the right way to do this_ and isn't -- this will only confuse future researchers. – mickmackusa Jul 10 '18 at 06:37

jjonesdesign · Answer 3 · 2014-09-18T01:09:49.127

What you are trying to achieve can be done very easily with the following code. A bigger concern is security. It is good practice to both sanitize your input every time the user has a chance to input text.

Also, using prepared query's will put yet another layer of security.

Although this isn't using your provided code directly, I believe it is good to teach good habits.

If you have any questions feel free to ask.

$username = $_POST['username']; <-- sanitize this
$message = null;
$mysqli = new mysqli("localhost", "user", "password", "database");

$stmt = $mysqli->prepare("SELECT username FROM websiteusers WHERE username=?");
$stmt->bind_param('s', $username);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($usernamesql);
$stmt->fetch();

if ($stmt->num_rows() > 0) {
    RegisterUser();
} else {
    $message .= 'username already exists';
}

Later on when you require more items to be queried, or more results to be bound:

$stmt = $mysqli->prepare("SELECT username,password,other1,other2 FROM websiteusers WHERE username=?");
$stmt->bind_param('s', $username); <-- the "s" means the argument is a strings, if a argument is stored as an int use "i", but one character for each argument is required.
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($usernamesql);
$stmt->fetch();

Multiple Arguments:

$stmt = $mysqli->prepare("SELECT username,password,other1,other2 FROM websiteusers WHERE username=? AND authenticated=?");
$stmt->bind_param('si', $username,$isauthenticated); <-- second argument is a INT or BOOL
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($usernamesql,$passwordsql,$other1sql,$other2sql);
$stmt->fetch();

When your expecting multiple results, and lets say you want to dump them into arrays:

$userarray = array();

$stmt = $mysqli->prepare("SELECT username FROM websiteusers WHERE username=?");
$stmt->bind_param('s', $username);
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($usernamesql);

while($stmt->fetch()){
    array_push($userarray, $usernamesql);
}

$userarray is now an array of all the results fetched from the database.

PHP/MySQL: Check if username exists

3 Answers3