Authentication Script PHP Help #2 -- I need help tweaking it - it wont work

[FirefoxRocks 0]

Okay, my first issue about the MySQL echo problem has been solved, thank you to those who helped.
Now I am focusing on the login portion of my site, and I have this so far:

<?php// we must never forget to start the sessionsession_start();$errorMessage = '';if (isset($_POST['username']) && isset($_POST['password']))[tab][/tab]{   $username = $_POST['username'];   $password = $_POST['password'];//Connect to database$con = mysql_connect("localhost","myDatabaseUsername","myDatabasePassword");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("myTable2", $con);   // check if the user id and password combination exist in database   $sql = "SELECT name FROM users WHERE name = '$username' AND password = PASSWORD('$password')";   $result = mysql_query($sql) or die('Query failed. ' . mysql_error());   if (mysql_num_rows($result) == 1)[tab][/tab] {[tab][/tab]  // the user id and password match, check for authorization[tab][/tab][tab][/tab][tab][/tab]$sql_a = "SELECT auth FROM users WHERE auth = '$auth'";[tab][/tab][tab][/tab][tab][/tab]$result = mysql_query($sql_a)[tab][/tab][tab][/tab][tab][/tab] or die('Query failed. ' . mysql_error());[tab][/tab][tab][/tab][tab][/tab]if ($result == YES)[tab][/tab]  // set the session[tab][/tab]  $_SESSION['db_is_logged_in'] = true;[tab][/tab]  // after login we move to the main page[tab][/tab][tab][/tab][tab][/tab]header("(anti-spam-content-type:) $mime;charset=$charset");[tab][/tab]  header('Location: moderate.php');[tab][/tab]  exit;   }[tab][/tab] else[tab][/tab] {[tab][/tab] $errorMessage = 'Sorry, wrong user id / password';[tab][/tab] header("(anti-spam-content-type:) $mime;charset=$charset");[tab][/tab] }[tab][/tab]}?>

I cannot figure out what is wrong with this! I tried moving parts around, removing parts, didn't work.
Edited November 29, 2009 by FirefoxRocks (see edit history)

[faulty.lee 0]

One of the most important thing to learn in programming is to "Troubleshoot". So you need to first troubleshoot where is your problem. This is where debugging comes in. Simplest way to debug in php is "echo" or "print".

I've tried your link, the the authentication failed. So, you should find out why it fail.

else	 {		 //Add echo here	 $errorMessage = 'Sorry, wrong user id / password';	 header("(anti-spam-(anti-spam-content-type:)) $mime;charset=$charset");	 }

You should maybe try to echo the number of rows, since you're checking the number of rows to determine the first step of authentication. Maybe it's returning 2 instead of 1. Maybe you have 2 identical record with the same username and password. To prevent identical username, you can either enable "unique" on the username column or do a check before you insert any new username. The later one is preferable, simply because you want to allow deleted/terminated username to be reuse.

On the second check

$sql_a = "SELECT auth FROM users WHERE auth = '$auth'";

i don't see the var $auth being initialized. What value should it contain?

That should get you started. But before you go futher, i have a few personal tips, might not be the most appropriate one, but should help you in your case.

1. For you second check, $auth, you should do it in 1 query.

//before   $sql = "SELECT name FROM users WHERE name = '$username' AND password = PASSWORD('$password')";  //after   $sql = "SELECT name FROM users WHERE name = '$username' AND password = PASSWORD('$password') AND auth = '$auth'";

You can do this, since auth is compulsory. Or you can also retrive the value of auth from the db, then compare later.

//before   $sql = "SELECT name FROM users WHERE name = '$username' AND password = PASSWORD('$password')";  //after   $sql = "SELECT name, auth FROM users WHERE name = '$username' AND password = PASSWORD('$password')";   $result = mysql_query($sql) or die('Query failed. ' . mysql_error());   if (mysql_num_rows($result) == 1)   {		$row_result = mysql_fetch_array($result)		if ($row_result['auth'] == $auth)		{			//success		}

Good luck