Check record exists db - error show

Question

How do I check if username or email exists and then put a error message in my error array. Right now i have:

$sql = "SELECT username, email FROM users WHERE username = '" . $username . "' OR email = '" . $email . "'";

$query = mysql_query($sql);

if (mysql_num_rows($query) > 0)
{
 echo "That username or email already exists";
}

But I want to check if it is the username OR the email that is existing and then put:

error[] = "username is existing"; //if the username is existing

error[] = "email is existing"; //if the email is existing

How to do?

No not at all, because i have: $username = mysql_real_escape_string($_POST['username']); and the same for email — Jake, Nov 30 '10 at 18:38

OV Web Solutions · Answer 1 · 2010-11-30T18:54:13.173

Sounds like you're trying to let users know whether a username or email already exists at registration time. Here's what you can do:

<?php
//----------------------------------------
// Create first query
$usernameQuery = 'SELECT username FROM users WHERE username="'.mysql_real_escape_string($username).'"';

//----------------------------------------
// Query db
$usernameResult = mysql_query($userNameQuery);

//----------------------------------------
// Check if result is empty
if(mysql_num_rows($usernameResult) > 0){

   //----------------------------------------
   // Username already exists
   $error[] = 'Username already exists';

   //----------------------------------------
   // Return error to user and stop execution
   // of additional queries/code
} else {

  //----------------------------------------
  // Check if email exists

  //----------------------------------------
  // Create query
  $emailQuery = 'SELECT email FROM users WHERE email="'.mysql_real_escape_string($email).'"';

  //----------------------------------------
  // Query the db
  $emailResult = mysql_query($emailQuery);

  //----------------------------------------
  // Check if the result is empty
  if(mysql_num_rows($emailResult) > 0){

     //----------------------------------------
     // Email already exists
     $error[] = 'Email already exists';

     //----------------------------------------
     // Return error to user and stop execution
     // of additional queries/code
  } else {

     //----------------------------------------
     // Continue with registration...
  }
}
?>

Please note that you should always escape your values before executing the actual query.

Additional Resources:
http://us.php.net/manual/en/function.mysql-real-escape-string.php http://us.php.net/manual/en/function.mysql-escape-string.php

Orbling · Accepted Answer · 2010-11-30T18:58:12.857

It would be easier if you just did a quick true/false check in the SQL and checked the flag that came back.

$sql = "SELECT "
  . "(SELECT 1 FROM `users` WHERE `username` = '" . mysql_real_escape_string($username) . "'), "
  . "(SELECT 1 FROM `users` WHERE `email` = '" . mysql_real_escape_string($email) . "')";

$query = mysql_query($sql);

if (mysql_num_rows($query) > 0) {
  $foundFlags = mysql_fetch_assoc($query);
  if ($foundFlags['username']) {
    $error[] = "username is existing";
  }

  if ($foundFlags['email']) {
    $error[] = "email is existing";
  }
} else {
  // General error as the query should always return
}

When it does not find an entry, it will return NULL in the flag, which evaluates to false, so the if condition is fine.

Note that you could generalise it for a field list like this:

$fieldMatch = array('username' => $username, 'email' => $email);

$sqlParts = array();
foreach ($fieldMatch as $cFieldName => $cFieldValue) {
  $sqlParts[] = "(SELECT 1 FROM `users` WHERE `" . $cFieldName . "` = '" . mysql_real_escape_string($cFieldValue) . "')";
}


$sql = "SELECT " . implode(", ", $sqlParts);

$query = mysql_query($sql);

if (mysql_num_rows($query) > 0) {
  $foundFlags = mysql_fetch_assoc($query);
  foreach ($foundFlags as $cFieldName => $cFlag) {
    if ($foundFlags[$cFieldName]) {
      $error[] = $cFieldName . " is existing";
    }
  }
} else {
  // General error as the query should always return
}

NB. Note that assumes all fields are strings, or other string-escaped types (eg. date/time).

I like how you combined the two queries. @jake In some cases, you might want to separate the queries. The benefit is prompt error handling, and less mysql loads. Best — OV Web Solutions, Nov 30 '10 at 19:02
@OV Usually I might have coded that as multiple queries in my own code - but the OP seemed to be going after a shorter, more contained solution. It very much depends on your server setup what will function the best. Those queries are independent, so internally to the server it will just perform the queries as if they were separate, albeit with a slight overhead to combine the results. But there is a lot less overhead in that than performing two separate queries through the client interface. Most of the saving comes from returning 1 or NULL, rather than the value you already have. — Orbling, Nov 30 '10 at 19:07
I agree. The server setup can play a role on what solution is best to implement. Excellent job posting exactly what was needed. Best — OV Web Solutions, Nov 30 '10 at 19:39

score 0 · Answer 3 · answered Nov 30 '10 at 18:44

0

You can fetch one row and see if you got same email that you search or same username or both. You can do LIMIT 0,1 if you can stop after finding first row matching either this or that.

answered Nov 30 '10 at 18:44

Kamil Szot

17,436
6
62
65

Check record exists db - error show

3 Answers3

Linked

Related