Having trouble getting a PHP mysql to return highest value

Question

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:

Varchar. And I have edited the post to show the Other Famous Birthdays code — Daniel Faulkner, Aug 20 '19 at 12:42
Try casting it to decimal: ```... ORDER BY cast(score as decimal(3,10))``` — Joan Lara, Aug 20 '19 at 12:44
If score is a number then storing in a varchar column isn't a good idea - for one thing an alphabetical sort is not the same as a numeric sort. You could potentially have other issues as well e.g. if you wanted to add them together or something. Use the correct column format to suit your data — ADyson, Aug 20 '19 at 12:45
How would I store as an INT but keep decimal points as they are quite important? — Daniel Faulkner, Aug 20 '19 at 12:54
Its working as expected for the side list :/ remove `AND name != '$s_name'` your se David show up on the right — Lawrence Cherone, Aug 20 '19 at 13:00
@JoanLaraGanau Not decimal(3,10). The first number (3) must be greater than the second number (10) and represents the total number of digits of precision. So, you need something like DECIMAL(16, 14) to have 14 places after the decimal point and 2 places before. — Booboo, Aug 20 '19 at 13:13

score 1 · Answer 1 · edited Aug 20 '19 at 13:17

1

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.

edited Aug 20 '19 at 13:17

Booboo

38,656
3
37
60

answered Aug 20 '19 at 12:45

ascsoftw

3,466
2
15
23

1

Please explain **why** that cast is neccessary. Keep in mind that such an explanation helps the OP understanding your answer – Nico Haase Aug 20 '19 at 12:47
1

Surely better to store the data in its correct format to begin with instead of keep on casting it? – ADyson Aug 20 '19 at 12:48
Unsigned? Doesn't that drop everything after the decimal point? – Booboo Aug 20 '19 at 12:58
1

@ascsoftw It does on my MySql server. Try: `select cast('1.123' as unsigned);` It produces: `1` with the following warning: `Warning (Code 1292): Truncated incorrect INTEGER value: '1.123'` – Booboo Aug 20 '19 at 13:03

Having trouble getting a PHP mysql to return highest value

1 Answers1