CODEWITHSUNDEEP
phpmysql
Daniel Faulkner
Daniel Faulkner

Reputation: 59

Having trouble getting a PHP mysql to return highest value

I'm having issues getting a PHP MySQL query to return the highest value!

Already tired fixes like the exaple here: SQL select only rows with max value on a column. But this fix had the opposite effect.

$sql = "SELECT * FROM famous_birthdays WHERE sdob = '$today' ORDER BY score 
DESC LIMIT 1";
$result = $conn->query($sql); 
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {

Here is the output of the code above:

a busy cat

The number below the celebrity names and the Featured Birthday (yes I'm aware that featured is spelt incorrectly) is the score and the Featured birthday should be the celebrity with the highest score but as you can see that's not the case.

The Other Famous Birthdays box is populated using the following code:

$sql = "SELECT * FROM famous_birthdays WHERE sdob = '$today' AND name != 
'$s_name' ORDER BY score DESC ";
$result = $conn->query($sql); 
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {

Contents of the table: enter image description here

Upvotes: 0

Views: 80

Answers (1)

ascsoftw
ascsoftw

Reputation: 3476

Since your datatype is varchar, Mysql is not considering it as a number or decimal. It is considering them as alphabets and sorting accordingly.

So you have 2 values 8.5 and 10.96 in your image. Since it is Varchar, Mysql will start from the left. Since 8 is greater than 1, it will consider 8.5 greater than 10.96. It would not compare 8 and 10.

You need to cast varchar as number. You can use following query:

SELECT * FROM famous_birthdays WHERE sdob = '$today' ORDER BY cast(score as DECIMAL(16,14))
DESC LIMIT 1

Using cast Mysql will treat them as numbers and sort accordingly.

Upvotes: 1

Related Questions