CODEWITHSUNDEEP
phpmysql
user2453646
user2453646

Reputation: 104

Multiple $query = mysql_query in PHP

I need to check for duplicate usernames and check if they already exist in the database or not. If they do, I show them an error message.

I am doing it with username but I also need to do it with the email address.

Here's my code so far:

if(isset($_POST['submit'])){
    $first_name = $_POST['first_name'];
    $last_name = $_POST['last_name'];
    $username = $_POST['username'];
    $password = $_POST['password'];
    $email = $_POST['email'];
    $IP = $_SERVER['REMOTE_ADDR'];

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

...

else if (mysql_num_rows($query) != 0)
    {
        echo "<p><b><center> <font color=\"red\">Username already exists.<br> </b>If you already have an account, please<a href = '../user/login.php'> click here </a> to login.</font></center></p>"; 
    }

My question is how can I do the same for email? Do I need to have one another query for email so like $query = mysql_query("SELECT email FROM Users WHERE email='".$email."'"); and add it after the first query in the above code?

I tried doing it but it gives me an error. Thanks.

Upvotes: 1

Views: 491

Answers (2)

Taylor
Taylor

Reputation: 3141

foreach ($fields as $field => $value) {
$value = mysql_real_escape_string($value);
$query = mysql_query("SELECT COUNT({$field}) AS n FROM Users WHERE {$field}='{$value}';");
$tuple = mysql_fetch_assoc($query);
if ($tuple['n'] > 0) {
    $dup = true;
    break;
}

This is what you are looking for. But be careful, it is vulnerable to SQL Injection.

Upvotes: 0

LSerni
LSerni

Reputation: 57408

The best way is probably to run multiple queries; you could benefit from indexing and better maintainability.

You should not use mysql_* functions; they are deprecated and will soon be removed. Use PDO instead (or mysqli).

That said, at least escape your values in order to avoid SQL injection, or good faith errors if someone has a name with a quote - D'Artagnan, or O'Brien. The code below is not proof against all problems, but can intercept the most common ones. Again, PDO and prepared queries would help you no end:

// Fields to check for duplicate.
$fields = array(
    'username' => $username,
    'email' => $email
);

$dup = false;
foreach ($fields as $field => $value) {
    $value = mysql_real_escape_string($value);
    $query = mysql_query("SELECT COUNT({$field}) AS n FROM Users WHERE {$field}='{$value}';");
    $tuple = mysql_fetch_assoc($query);
    if ($tuple['n'] > 0) {
        $dup = true;
        break;
    }
}

if ($dup) {
    echo "<p><b><center> <font color=\"red\">{$field} already exists.<br> </b>If you already have an account, please<a href = '../user/login.php'> click here </a> to login.</font></center></p>"; 
}

The use of COUNT() instead of retrieving the query count could be slightly more efficient depending on indexing.

Upvotes: 1

Related Questions