php and mysql_num_rows not detecting if something isnt in the database

Question

I have a form on a website that needs validating before entering the form data into the database.

Its checking whether a username already exists by user the mysql_num_rows function. But i cannot seem to get it working. When testing, it doesn't let a new username be added.

Here is the full code being used:

<?php
session_start();

include("databaseConnect.php");
// Insert a row of information into the table "example"


// check if username is already in database
if(mysql_num_rows(mysql_query("SELECT userName FROM registeredUsers WHERE userName =     '$_POST[userName]'"))){
 echo "Username: ". $_POST[userName]." already exists in the Database<br><br>";
    echo "You will be redirect back to the form in 5 seconds";
$ref = $_SERVER['HTTP_REFERER'];
header( 'refresh: 5; url='.$ref);

//check if hemis is already in database
}elseif(mysql_num_rows(mysql_query("SELECT hemis FROM registeredUsers WHERE hemis = '$_POST[hemis]'"))){
echo "Student [Hemis] Number: ". $_POST[hemis]." already exists in the Database<br><br>";
echo "You will be redirect back to the form in 5 seconds";
$ref = $_SERVER['HTTP_REFERER'];
header( 'refresh: 5; url='.$ref);


// if all the conditions above are fine, it will insert the data to MySQL
}else{
  mysql_query("INSERT INTO registeredUsers
(firstName, lastName, hemis, userName, MAC) VALUES('$_POST[firstName]', '$_POST[lastName]', '$_POST[hemis]', '$_POST[userName]', '$_POST[mac]' ) ")
or die(mysql_error());

echo "Data Inserted! <br><br>";
}

Thanks a lot :)

Answer 1

I'd rewrite this completely. It's subject to SQL injection, inefficient and a little too terse. Plus, you're generally better off using the PHP mysqli extension

Also, make sure you enclose the $_POST variable names in quotes. You've written them as constants, as opposed to strings. (Unless you've defined constants elsewhere in code that represent the string values, this is an error. Turn on PHP warnings while you're developing.)

$safe_username = mysqli_real_escape_string($_POST['userName']);
$sql = "SELECT userName FROM registeredUsers WHERE userName='$safe_username' LIMIT 1";
$result = mysqli_query($database_connection, $sql);
if (mysqli_num_rows($result))
{
    // username already found code
    mysqli_free_result($result);
}
else
{
    $safe_hemis = mysqli_real_escape_string($_POST['hemis']);
    $sql = "SELECT hemis FROM registeredUsers WHERE hemis='$safe_hemis' LIMIT 1";
    // Side note, LIMIT 1 tells the database engine to stop looking after it's found one hit. More efficient as you're only looking for a Boolean value anyway.
    $result = mysqli_query($database_connection, $sql);
    if (mysqli_num_rows($result))
    {
        // hemis found code
        mysqli_free_result($result);
    }
}

The rest you can probably figure out from this.

Please validate and escape all input. Validation includes checking for sanity - is the data within bounds (string length, numerical bounds, etc), etc. All input is evil!

You really don't want to depend on HTTP_REFERER either. User agents don't always pass referrers.

Also, I know it's not a big deal, but use CSS rather than <br>. If you're using the XHTML doctype, you have to properly close all tags, so <br> would become <br />. This is a good idea anyway.