PHP/HTML table data order by a variable?

Question

I took two datas out of my database and divided them & then made $Kdr print the calculated value.

<?php
    $query = mysql_query("SELECT * FROM `high` ORDER BY `$Kdr` DESC LIMIT 20");
    while($row = mysql_fetch_array($query)){
    if ($row["kills"] != 0) {
    $Kdr = $row["kills"] / $row["deaths"];
    }       
    echo "<tr><td><a href='personalhs.php?query={$row["playerName"]}'>".$row['Runecraftlvl']."</a></td><td>".$row['kills']."</td><td>".$row['Runecraftxp']."</td><td>".$Kdr."</td></tr>";
    }
?>

Ignore the $row['Runecraftinglvl'], and others.

Basically I want my table to order data by the variable "$Kdr" I declared above that prints the divided data.

I tried doing this:

    $query = mysql_query("SELECT * FROM `high` ORDER BY `" . $kdr . "` DESC LIMIT 20");

and this

    $query = mysql_query("SELECT * FROM `high` ORDER BY `$Kdr` DESC LIMIT 20");

Both giving me this error: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/justxpp1/public_html/gxx/highscores.php on line 90

What am I doing wrong? Thanks.

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Jan 28 '13 at 21:14
What is your database schema for the table 'high'? mysql_query can return `false` if there's an error, hence giving you the warning. Before your while-loop, type in the following and show us what it says: `echo mysql_errno() . ": " . mysql_error(). "
";` — Chris Forrence, Jan 28 '13 at 21:18
The connection with the schema & Table working, the error only happens when I put the $Kdr var inside the Mysql_query. — user1902133, Jan 28 '13 at 21:21
Where is $Kdr defined? Also, is it an uppercase or lowercase K? You may also want to try replacing the quotes surrounding $kdr with a single-quote — Chris Forrence, Jan 28 '13 at 21:23
I'ts upper case. and what do you mean by where is it defined? In the database or in the code? EDIT: If I only echo the $Kdr, it works indeed. But i want my table to order by $Kdr look here: http://justxp.plutohost.net/gxx/highscores.php — user1902133, Jan 28 '13 at 21:26
In the code; from what it looks like, your first reference to $Kdr is after you've actually executed the query! If so, you may want to look at Pankrates's answer for an improved SQL statement — Chris Forrence, Jan 28 '13 at 21:27

Pankrates · Accepted Answer · 2013-01-28T21:39:02.593

5

You are trying to sort on a value in the SQL query that is only calculated in php after you get the result from said query. Also the variable $Kdr you define is a number and you can not ORDER on a number but rather you have to order on a column name. You could do the sorting in sql with something like this

SELECT kills/deaths AS kdr
FROM high
ORDER BY kdr DESC LIMIT 20

Edit:

The complete code would look something like this. Note: please read the first comment in your original question carefully. It is highly ill-advised to use mysql_ functions.

$query = mysql_query("SELECT playerName, kills, deaths, kills/deaths AS kdr FROM high ORDER BY kdr DESC LIMIT 20)";
while($row = mysql_fetch_array($query)){
    $Kdr = $row["kdr"];    
    echo "<tr>
              <td>Name: ".$row["playerName"] ."</td>
              <td>Kills ".$row["kills"] ."</td>
              <td>Deaths: ".$row["deaths"] ."</td>
              <td>Kill death ratio: ".$Kdr ."</td>
          </tr>";
}

edited Jan 28 '13 at 21:39

answered Jan 28 '13 at 21:16

Pankrates

2,995
1
20
28

Thanks for answering Pank, I am pretty new to PHP and MySQL, could I please get a explanation on the code? I mean where does this code goes? Just organised like this? – user1902133 Jan 28 '13 at 21:29
It looks like you're taking kills and deaths in the query, dividing them, and making them as 'kdr', but what if I want to display the kills and deaths too? – user1902133 Jan 28 '13 at 21:38
See the edit for updated code – Pankrates Jan 28 '13 at 21:39
Worked like a charm! Thanks! – user1902133 Jan 28 '13 at 21:45

score 1 · Answer 2 · answered Jan 28 '13 at 21:30

1

You can calculate and sort directly in the query, something like:

SELECT kills/deaths as kdr, playerName, Runecraftlvl  FROM high GROUP BY playerName ORDER BY kdr DESC LIMIT 20;

answered Jan 28 '13 at 21:30

drcreazy

11
3

It looks like you're taking kills and deaths in the query, dividing them, and making them as 'kdr', but what if I want to display the kills and deaths too? – user1902133 Jan 28 '13 at 21:37
@user1902133, you could select all what you want from table, and calculate some values also. i.e. `code` SELECT kills/deaths as kdr, playerName, Runecraftlvl, deaths, kills, etc. Star also works, but it's bad practice SELECT *, kills/deaths as kdr – drcreazy Jan 28 '13 at 21:42

PHP/HTML table data order by a variable?

2 Answers2