mysql phpmyadmin User Input COMPARE database

Question

hi guys i'm new with php and mysql, i am currently trying to check the user input &username if there's already existing the same username in the database, and same with user input $email.

i have below codes, i tried having a line that echoes and compares user input against fetch rows/ data from database. I was able to verify that it reaches to the point where line echo $email."COMPARE".$result2; is both the same, yet it passes the condition $email == $result2 where result2 is email fetch from database. Can you please point me what's wrong? THanks in advance.

$extract= mysql_query("SELECT * FROM users");
$resultq = mysql_num_rows($extract);
while($row= mysql_fetch_array($extract))
{

    $result = $row['username'];
    $result2 = $row['email'];

    echo $email."COMPARE".$result2;

    if($username == $result) 
    {
         echo "<script type=\"text/javascript\">alert('Username Already Taken')</script>";
         echo "<meta http-equiv=\"refresh\" content=\"0;url=6signup.html\" />";
         break;

    }
    else if ($email == $result2)
    {
         echo "<script type=\"text/javascript\">alert('Email Already Registered')</script>";
         echo "<meta http-equiv=\"refresh\" content=\"0;url=6signup.html\" />";
         break;
    }
    else
    {
        //continues to execute the loop until no more to fetch
    }

Can you add a little more debugging? Instead of the 'echo()', try `var_dump($email)` and `var_dump($result2)`. Also, if you're looking to see if the username or email are taken, you can do that in your query and let the database tell you if they're available - you just need to add a WHERE clause. — andrewsi, Aug 23 '13 at 16:39
You are running while loop. just put break statement in both if and else if and no two results will be displayed — user1502952, Aug 23 '13 at 16:42
what's wrong with my statemetn? fetch array doesn't work? i do have break @user1502952. — Daryl, Aug 23 '13 at 17:47

score 1 · Answer 1 · edited May 25 '19 at 14:18

I would use a different approach and do the search in your SQL-query. SQL is better (and performs better) on that kind of searches.

You won't need a while loop for this query:

SELECT *
FROM users
WHERE username = '<input username here>'
OR email = '<input email here>'

If you get a result, it means that either the username or the e-mailaddress is already registered. Use an if-statement to check whether or not it's the username or the e-mailaddress.

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

The full PHP-code would be something like this (for PDO):

// Database connection
// -------------------
// I usually put this in a seperate file and include it.
try {
    $dbconn = new PDO('mysql:dbname=database;host=localhost', 'username', 'password');
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

// Prepare statement
// -----------------
// Build query using question mark parameters
$sql = "SELECT * FROM users WHERE username = ? OR email = ?";
// Create prepared statement
$sth = $dbconn->prepare($sql);

// Execute statement
// -----------------
// The parameters are automatically changed into your input data
$sth->execute(array($username, $email));

// Put all affected rows in $result (array!)
$result = $sth->fetchAll();

// Check array contents
// --------------------
if (count($result) > 0) {
    // Loop through results
    foreach ($result as $row) {
        if ($username == $row['username']) {
            echo "<script type=\"text/javascript\">alert('This username is already taken!')</script>";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=6signup.html\" />";
        } else { // If username doesn't match, it has to be the e-mailaddress
            echo "<script type=\"text/javascript\">alert('This e-mailaddress has already registered!')</script>";
            echo "<meta http-equiv=\"refresh\" content=\"0;url=6signup.html\" />";
        }
    }
} else {
    /* Code to execute when not registered yet */
}

Yep. SQL is exactly for this purpose! Would give +1 for the PDO stuff alone too, and you could mention SQL injection too at the same time. I think there should be a standard comment about PDO and SQL injection attached to all PHP mysql questions... — ppeterka, Aug 23 '13 at 17:59
The main advantage of PDO and MySQLi is, if used properly (prepared statements), that it's already protected against SQL injection... — Marty McVry, Aug 23 '13 at 18:00
@MartyMcVry Can you please convert my statement into a PDO as an exmaple? As a guide? Thank :S — Daryl, Aug 23 '13 at 19:53
Username Or Email already used, can't it be specific? like a separate condition checking for username and email? — Daryl, Aug 23 '13 at 21:04
You would have to add that in the `if (count($result) > 0) {` statement. `$result` contains an array, so you can use a `foreach` loop to loop through the found results. Check within the foreach loop for username or email. - see edit — Marty McVry, Aug 23 '13 at 21:07
@MartyMcVry A lot of us know how not to get into SQL injection but judging from the code, the OP would probably be glad for a basic mention or explanation of this threat... As long as one is physically able to concatenate $userName into a query, they will, too... — ppeterka, Aug 24 '13 at 08:51
You're right... Every year, when one of my students uses `mysql_*`-functions, I make it my mission to have them type something in the textbox that will screw up the application... — Marty McVry, Aug 24 '13 at 08:55

mysql phpmyadmin User Input COMPARE database

1 Answers1

Linked