Multiple $query = mysql_query in PHP

Question

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.

**Building SQL statements with outside variables makes your code vulnerable to SQL injection attacks.** Also, any input data with single quotes in it, like "O'Malley", will blow up your query. Learn about parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174) has many detailed examples. See also http://bobby-tables.com/php for alternatives & explanation of the danger. Running SQL statements built with outside data is like eating soup made from ingredients found on your doorstep. — Andy Lester, Feb 04 '15 at 00:06
Why don't you just insert the record? If it fails due to constraints you know there was already such a record. This assumes you have unique constraints set up already. — Ja͢ck, Feb 04 '15 at 00:27

score 1 · Answer 1 · answered Feb 04 '15 at 00:14

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.

Thank you so much! I really appreciate it. You are the first person on the site who has helped me by actually putting it into my code and stuff. Thanks :) — user2453646, Feb 04 '15 at 00:30
You're most welcome. But please *do* consider switching to PDO. We don't say that just to look cool, honest :) -- and trust me, you so do **not** want to plug a _POST or _GET variable straight into a query, *ever*. Check out the site suggested by @AndyLester - http://bobby-tables.com/ . — LSerni, Feb 04 '15 at 00:46

score 0 · Accepted Answer · answered Mar 03 '15 at 00:46

0

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.

answered Mar 03 '15 at 00:46

Taylor

2,981
2
29
70

You should have copy-pasted the definition of `$fields` from my own answer too, or the code has little chance of working; you'd just get a "$fields is undefined" message, and a SQL error if the script doesn't stop. – LSerni Mar 03 '15 at 08:29

Multiple $query = mysql_query in PHP

2 Answers2