How to debug "query not executed" error in MySQLi?

Question

Please can someone help me in guiding me in the correct direction to get this code to work. I have migrated from PHP5.4 to PHP5.5 and I wonder if that might be the reason for the difficulty?

function auth($username, $password) {

    // hash password using md5 encryption
    $hash_pass = md5($password);

    // prepare SQL query
    $username = mysqli_real_escape_string($username);

    $query = "SELECT * FROM `area51_users` WHERE `user_name`='".$username."'";


    if ($result = mysqli_query($Connection, $query) or die (mysqli_error()." (query not executed)")) {  
        if (mysqli_num_rows ($Connection, $result) > 0) { 
            // record exits
            if ($row = mysqli_fetch_assoc($result) or die (mysqli_error())) {

                if ($hash_pass == $row['user_password']) {
                    // password is valid

                    // setup sesson
                    session_start();

                    $_SESSION['username'] = $username;
                    $_SESSION['CMS_AUTH'] = "YES";

                    return true;
                }
                else {
                    return false;
                }
            }
            else {
                return false;
            }
        }
        else {
            return false;
        }
    }
}

Currently I am getting the error "query not executed" from the first if statement.

I am new to PHP and trying to work this all out.

First, please be clear if this query worked in past (update question body). Please also be sure to have error catcher on when you create Connection, http://php.net/manual/en/function.mysqli-connect.php, and that you open phpmyadmin or even just sql commandline and run that query direct (with hardcoded username ofcourse), to ensure no typo in simple syntax error. That should rule out most of occam's razor — Daniel Brose, Jul 28 '15 at 08:12
I am slightly confused as to how this would fix the error that is occuring. Is it possible to elaborate? — , Jul 28 '15 at 08:32
Sometimes the issue might be not related to the line you think it is, and in any case good form to always be sure to error catch connections and other common code just to rule it out when something happens. If the query worked in the past is critical info. If you can connect successfully, if the query itself is not at fault, and the query as is (with those php variables as is) worked previously, then that rules out a hell of a lot of possible issues. Another sanity check is to add junk text to error msg to be sure that is the right line, in case of complex file with copy+pasted code. — Daniel Brose, Jul 28 '15 at 08:43
It also might be your DB itself, hence checking the query direct — Daniel Brose, Jul 28 '15 at 08:46

score 0 · Answer 1 · edited May 23 '17 at 12:14

The problem is the scope of $connection (it's not available in your function) -> Check php variable scope http://php.net/manual/en/language.variables.scope.php

Second your code has many unnecessary things.

You need no if/else or return false, when you use die. Instead of die you should use Exceptions!

Cleaned up code:

function auth($username, $password)
{
//you need this variable!!!
global $Connection;

// hash password using md5 encryption
$hash_pass = md5($password);

// prepare SQL query
$username = mysqli_real_escape_string($username);
$password = mysqli_real_escape_string($password);

$query = "
    SELECT 
        * 
    FROM `area51_users` 
    WHERE 
        `user_name`= '" . $username . "'
        AND `user_password` = '" . $password . "'
";

if ($result = mysqli_query($Connection, $query)) {
    if (mysqli_num_rows($Connection, $result) > 0) {
        // record exits
        if ($row = mysqli_fetch_assoc($result))) {

            // setup sesson
            session_start();

            $_SESSION['username'] = $username;
            $_SESSION['CMS_AUTH'] = "YES";

            return true;
        }
    }
}

echo  mysqli_error($Connection);

return false;
}

You could also enhance your query with prepared statements (safer) How can I prevent SQL injection in PHP?

Even this is causing errors, do you by any chance have a Git hub account where I can show you the rest? Perhaps it is something in the other files? — , Jul 28 '15 at 09:59

How to debug "query not executed" error in MySQLi?

1 Answers1