PHP MYSQLI query error?

Question

Hey This is my login script, using PHP5 and MYSQLi, I just had help spotting errors in my query, but still it will not let me login, even though the username and password are correct and in the database, it just keeps returning the error: your username and password do not match any in our db. But I know they do lol...could any body spot the problem?

//Check if the form has been submitted
    if (isset($_POST['login'])) 
    {   
        //Check if username and password are empty
        if ($_POST['username']!='' && $_POST['password']!='') 
        {       
            //Create query to check username and password to database
            $validate_user = $mysqli->query('SELECT id, username, password, active FROM users WHERE = username = "'.$mysqli->real_escape_string($_POST['username']).'" AND password = "'.$mysqli->real_escape_string(md5($_POST['password'])).'"');

            //We check if the query returns true
            if ($validate_user->num_rows == 1)
            {
                $row = $validate_user->fetch_assoc();

                //Check if the user has activated there account
                if ($row['activated'] == 1)
                {
                    $_SESSION['id'] = $row['id'];
                    $_SESSION['logged_in'] = true;
                    Header('Location: ../main/index.php');
                }
                //Show this error if activation returns as 0
                else {
                    $error = '<p class="error">Please activate your account.</p>';
                }
            }
                //Show this error if the details matched any in the db
                else {      
                    $error = '<p class="error">Your username and password are not in our database!</p>';        
                }
            }
                //Show this error if the username and password field have not been entered
                else {
                    $error = '<p class="error">Please enter your username and password.</p>';
                }
    }

Answer 1

To make it most reliable way, I'd suggest to trigger this error according to main error handling settings:

//just in sake of readability
$user = $mysqli->real_escape_string($_POST['username']);
$pass = $mysqli->real_escape_string(md5($_POST['password']));

$sql = "SELECT id, username, password, active FROM users 
        WHERE username = '$user' AND password = '$pass'";

$res = $mysqli->query($sql) or trigger_error(mysqli->error.$sql);

note that trigger_error function. it will bring error message to the standard error output. On the development PC it will be browser's screen or a log file on the production server.

Answer 2

Instead of

SELECT ... FROM users WHERE = username = ...

It should be

SELECT ... FROM users WHERE username = ...

If you keep getting problems like this, try storing the query in a variable and echo it, so you can copy-paste it into your database management tool and see if there are any query errors.