Trying to check if username already exists in MySQL database using PHP

Question

I've looked at the many other posts that were similar to my issue and implemented their solutions (as far as I can tell) as exactly as I could. However, every time I execute this script, the code in the else block is executed (even when the username inputted is one that is already present).

The table name is 'Users' and the column that I am trying to search is 'username'. The input from my form was read into $username and I verified that it was read in properly using echo. $con contains the connection to the server.

At some point I also put in echo $query (nothing was printed) and echo mysql_num_rows($query) (nothing was printed).

Here's the relevant segment of the code. Would really appreciate some tips.

$query = mysql_query("SELECT username FROM Users WHERE username=$username", $con);

  if (mysql_num_rows($query) != 0)
  {
      echo "Username already exists";
  }

  else
  {
    ...
  }

EDIT: Apparently I was supposed to be using mysqli for my server and the way I checked the num_rows for that was by doing $query->num_rows since it was a property of the object. Thanks for all the help!

Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in new code **[this happens](https://media.giphy.com/media/kg9t6wEQKV7u8/giphy.gif)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions and prepared statements. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Apr 20 '18 at 16:54
Does this answer your question? [How to check if a row exists in MySQL?](https://stackoverflow.com/questions/22252904/how-to-check-if-a-row-exists-in-mysql) — Dharman, Nov 27 '19 at 21:06
@Dharman The problem is the lack of single quotes around the username. I mean, one of the problems. — Félix Adriyel Gagnon-Grenier, Nov 27 '19 at 21:55
@FélixGagnon-Grenier No, it is not if `$username` is numerical. If anything the problem is lack of parameterized prepared statements. — Dharman, Nov 27 '19 at 21:56
@Dharman hence, why I said "one of" :P. While I appreciate that username could be numerical, I er... somehow doubt it. — Félix Adriyel Gagnon-Grenier, Nov 27 '19 at 21:57

score 9 · Answer 1 · edited Dec 07 '18 at 18:46

9

change your query to like.

$username = mysql_real_escape_string($username); // escape string before passing it to query.
$query = mysql_query("SELECT username FROM Users WHERE username='".$username."'");

However, MySQL is deprecated. You should instead use MySQLi or PDO

edited Dec 07 '18 at 18:46

Jack

3,271
11
48
57

answered Nov 30 '13 at 05:09

Renish Khunt

5,620
18
55
92

I think it would return the same – Oki Erie Rinaldi Nov 30 '13 at 05:13
3

DO NOT USE THIS CODE. It is vulnerable to SQL Injection... Look up prepared statements. – Jack Dec 07 '18 at 16:46
@JackNicholson MySQL is old and deperacted don't use MySQL my frind upgrade your self lol. – Renish Khunt Dec 07 '18 at 17:19
@Renish Khunt Please re-read my answer. I was not searching for anything. I was linked this and asked if it was okay. Which it certainly is not. You should state obviously that mysql is deprecated and have the prepared statement answer as an alternative – Jack Dec 07 '18 at 18:28
@JackNicholson If someone is asking MySQL question then how can I answer about PDO and Mysqli lol. Why are you arguing with me just read the question properly did you see anything related to PDO or Mysqli on question? – Renish Khunt Dec 07 '18 at 18:28
@Renish Khunt You should state obviously that mysql is deprecated and have the prepared statement answer as an alternative – Jack Dec 07 '18 at 18:29
@JackNicholson You are right? I agree with you MySQL is deprecated even I am not using MySQL but If someone is asking MySQL question then I must be answered in MySQL instead of PDO or Mysqli. – Renish Khunt Dec 07 '18 at 18:31
2

@Renish Khunt - If people who are new are finding this on Google (Which they are) and think it is okay to use then there is clearly a problem. Your answer is no longer satisfactory in today's standards. I just suggest you update your answer so people are not misguided. I think you took it as a personal insult when I am just trying to let new developers aware that it is not okay to use anymore. – Jack Dec 07 '18 at 18:32

Jack · Answer 2 · 2018-12-07T18:35:12.703

~~$query = mysql_query("SELECT username FROM Users WHERE username='$username' ")~~

Use prepared statements, do not use mysql as it is deprecated.

// check if name is taken already
$stmt = $link->prepare("SELECT username FROM users WHERE username = :username");
$stmt->execute([
    'username' => $username
]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

if (isset($user) && !empty($user)){
    // Username already taken
}

score 2 · Answer 3 · edited Dec 07 '18 at 18:50

Try this:

$query = mysql_query("SELECT username FROM Users WHERE username='$username' ")

Don't add $con to mysql_query() function.

Disclaimer: using the username variable in the string passed to mysql_query, as shown above, is a trivial SQL injection attack vector in so far the username depends on parameters of the Web request (query string, headers, request body, etc), or otherwise parameters a malicious entity may control.

score -1 · Answer 4 · answered Nov 17 '17 at 17:11

-1

PHP 7 improved query.........

$sql = mysqli_query($conn, "SELECT * from users WHERE user_uid = '$uid'"); if (mysqli_num_rows($sql) > 0) { echo 'Username taken.'; }

answered Nov 17 '17 at 17:11

Adnan Khan

511
4
5

score -2 · Answer 5 · answered Sep 09 '14 at 13:03

TRY THIS ONE

 mysql_connect('localhost','dbuser','dbpass');

$query = "SELECT username FROM Users WHERE username='".$username."'";
mysql_select_db('dbname');

    $result=mysql_query($query);

   if (mysql_num_rows($query) != 0)
   {
     echo "Username already exists";
    }

    else
   {
     ...
    }

score -2 · Answer 6 · edited Sep 19 '15 at 07:49

-2

Everything is fine, just one mistake is there. Change this:

$query = mysql_query("SELECT username FROM Users WHERE username=$username", $con);
$query = mysql_query("SELECT Count(*) FROM Users WHERE username=$username, $con");

if (mysql_num_rows($query) != 0)
{
    echo "Username already exists";
}
else
{
  ...
}

SELECT * will not work, use with SELECT COUNT(*).

edited Sep 19 '15 at 07:49

Pang

9,564
146
81
122

answered Aug 25 '15 at 06:54

Nikunj

280
3
15

score -2 · Answer 7 · answered Apr 20 '17 at 14:57

$firstname = $_POST["firstname"];
$lastname = $_POST["lastname"];
$email = $_POST["email"];
$pass = $_POST["password"];

$check_email = mysqli_query($conn, "SELECT Email FROM crud where Email = '$email' ");
if(mysqli_num_rows($check_email) > 0){
    echo('Email Already exists');
}
else{
    if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $result = mysqli_query($conn, "INSERT INTO crud (Firstname, Lastname, Email, Password) VALUES ('$firstname', '$lastname', '$email', '$pass')");
}
    echo('Record Entered Successfully');
}

score -2 · Answer 8 · answered Sep 12 '17 at 04:02

-2

Here's one that i wrote:

$error = false;
$sql= "SELECT username FROM users WHERE username = '$username'";
$checkSQL = mysqli_query($db, $checkSQL);

if(mysqli_num_rows($checkSQL) != 0) {
   $error = true;
   echo '<span class="error">Username taken.</span>';
}

Works like a charm!

answered Sep 12 '17 at 04:02

Cody MacLeod

87
13

provide an explanation regrading the changes that were made in order to get the desired result – Anantha Raju C Sep 12 '17 at 04:27

Trying to check if username already exists in MySQL database using PHP

8 Answers8

Linked