php and mysql_num_rows not detecting if something isnt in the database

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开发者_Go百科(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 :)

---

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.

---

It's best to check the result of mysql_query too. It may return a result set for which you can get the number of rows, but it could return false when the query fails. In that case you will not have a result set, and mysql_count_rows will fail to. That fail you interpret as 0 rows.

Apart from all the great suggestions Matty gave you, I'd do some extra checking and strict type checking too.

if ($result = mysql_query('....') === false)
{
  die('Your query failed in the first place. Error: ' . mysql_error());
}

There are many improvements you can make (like using count in the query and such), but I think you should have at least these kinds of checks. It will help you understand what is actually going wrong instead of having to guess. It'll save you tons of debugging time wether your a beginner or an experienced programmer.