PHP MySQL ORDER BY level DESC LIMIT 1 not working on all searchs

Question

I have searched for an answer to this problem all over the web and here at Stackoverflow. Nothing seems to help. This should be a very simple function. I have multiple records with the same content EXCEPT a field called level. I want to find the record with the highest int value in level. An example would be that I have multiple records with the same userid and email but the first record starts at level 1, the next may be level 2 and then the next may be level 1 again. I want to find the record with level = 2. My code is very simple.

  $LoginRS__query=sprintf("SELECT userid, email, level FROM orders WHERE userid=%s AND email=%s ORDER BY level DESC LIMIT 1", GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text"));

For some this works correctly, returning the record with level = 2, 3, 8, 9, whatever. For others it will only return the record with level = 1. The field level is int length 1. I'm running php Version 5.3.28 and MySQL 5.5.36

Any ideas anyone? Like most, I'm in a rush. Thanks...

I didn't realize that level was a reserved word. I have been using the same table definitions and writing other scripts using level with no problems until now. I am checking for errors and it is not returning any. When checking the result with foreach ($row_LoginRS as $key=>$val) echo $key. ": ".$val. "
"; I normally get: userid: 9999 email: email@email.com level: 1 (or 2 or 3).
That looks correct.
I tried enclosing in ticks as suggested - SELECT userid, email, level ... and/or/both ORDER BY 'level' and it returns: userid: 9999 email: email@email.com level: level
My original query looks correct:

string(120) "
SELECT userid, email, level FROM orders WHERE userid='9999' AND email='email@email.com' ORDER BY level DESC LIMIT 1
" 

and when using ticks I get:

string(120) "
SELECT userid, email, 'level' FROM orders WHERE userid='9999' AND email='email@email.com' ORDER BY 'level' DESC LIMIT 1
"  

I'm not sure about the MySQL client question. phpinfo return: Active Persistent Links 0
Active Links 0
Client API version 5.5.36
MYSQL_MODULE_TYPE external
MYSQL_SOCKET /var/lib/mysql/mysql.sock
MYSQL_INCLUDE -I/usr/include/mysql
MYSQL_LIBS -L/usr/lib64 -lmysqlclient

Answer 1

You're not checking for errors.

• level is a MySQL keyword
• https://dev.mysql.com/doc/refman/5.5/en/keywords.html

It requires special attention, therefore either rename it to something other than a reserved word, or wrap ticks around it:

SELECT userid, email, `level` ...

Check for errors in your query using the same API as you are connecting with, which is unknownst to us.

You did tag as mysql, but MySQL has 3 different APIs under a PHP environment, not counting MSSQL.

• mysql_
• mysqli_
• PDO

Edit:

I tried enclosing in ticks as suggested

SELECT userid, email, `level` ... and/or/both ORDER BY 'level'
                      ^ tick                           ^ quote

and it returns: userid: 9999 email: email@email.com level: level

and...

My original query looks correct: string(120) "SELECT userid, email, level FROM orders WHERE userid='9999' AND email='email@email.com' ORDER BY level DESC LIMIT 1" and when using ticks I get: string(120) "SELECT userid, email, 'level' FROM orders WHERE userid='9999' AND email='email@email.com' ORDER BY 'level' DESC LIMIT 1"

• ORDER BY 'level' that is being treated as a string literal, rather than a column. That too needs to be wrapped in ticks.

Change your query to:

"SELECT userid, email, `level` FROM orders 
 WHERE userid='9999' 
 AND email='email@email.com' 
 ORDER BY `level` DESC LIMIT 1"

• Ticks are not the same as single quotes.

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Displaying errors should only be done in staging, and never production.

I'm not sure about the MySQL client question.

We need to know if you are mysql_, mysqli_ or PDO to connect with.

In either case, check for errors on the query. Choose from the following list:

• http://php.net/manual/en/function.mysql-error.php mysql_
• http://php.net/manual/en/mysqli.error.php mysqli_
• http://php.net/manual/en/pdo.errorinfo.php PDO

"No errors"

An example query to check for errors:

mysql_

$result = mysql_query($query, $con);
if ( !$result ) {
    trigger_error('query failed', E_USER_ERROR);
}

also or die(mysql_error($con)) to mysql_query()

or mysqli_

$result = mysqli_query($con, $query);

if (!$result)
{
    throw new Exception(mysqli_error($con));
}

else{ echo "Success."; }

or die(mysqli_error($con)) to mysqli_query()

or PDO

error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
    catch(PDOException $e){
        $e->getMessage(); for the real error
    }

Answer 2

Couple things jump out at me: You're sending the value of "password" as one of your query parameters, but not really using it in the query unless their password is their email (which would be... umm... "not a best practice" :) )

try doing a var_dump($LoginRS__query) to see what your actual query string looks like. Be sure your parameters are getting populated properly.