Sorting Loaded Tables :) Small Fix Needed.

[hahaitwork]

I need the script to sort it after score, but also after id, because the person on top should be the first person that submitted the score.

like if someone got id 13, score 15 and someone later gets id 34 and score 15, it should be the person with id 13 that is on Top.

Any ideas? I think it could be something like "BY score, id" or so? tride that but then it ended up the showing the ID and score the wrong way.

 

$result = mysql_query("SELECT name, score FROM X ORDER BY score DESC LIMIT 50");

echo "<table border='20' width='450px'>
<tr>
<th>Name</th>
<th>Score</th>
</tr>";
while($row = mysql_fetch_array($result))
 {

 echo "<tr>";
 echo "<td>" .'&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp'. $row['name'] .'&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp'. "</td>";
 echo "<td>" .'&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp'. $row['score'] .'&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp'. "</td>";
 echo "</tr>";
 }
echo "</table>";
mysql_close($con);
?>
</html>

[Christian F.]

BY score DESC, id ASC

Yep, it's that simple.

 

Though, you really should strip those &nbsp's from your code, and use CSS paddings/margins instead. Not only are you abusing the non-breaking space character to create your layout, which should be controlled by the stylesheet, but you've also done it wrong.

[hahaitwork]

BY score DESC, id ASC

Yep, it's that simple.

 

Though, you really should strip those &nbsp's from your code, and use CSS paddings/margins instead. Not only are you abusing the non-breaking space character to create your layout, which should be controlled by the stylesheet, but you've also done it wrong.

 

I know that coders will wrist in pain when they see it but for me it works perfectly and I'm not going to change it for this "project"

Your code made NO change actually

What is "ASC" by the way?

Edited December 3, 2012 by hahaitwork

[Christian F.]

Ah, yes. You wanted the IDs in a DESCending order, if so then just change "ASC" (which stands for ascending) to "DESC".

[hahaitwork]

Ah, yes. You wanted the IDs in a DESCending order, if so then just change "ASC" (which stands for ascending) to "DESC".

ORDER BY score DESC, id DESC 50");

like this? Not working. seems abit special as well?

[Barand]

LIMIT 50

[hahaitwork]

LIMIT 50

You mean like:

ORDER BY score DESC, id DESC, LIMIT 50");

Even if that is correct it won't sort them the rigth way.

[trq]

You mean like:

ORDER BY score DESC, id DESC, LIMIT 50");

Even if that is correct it won't sort them the rigth way.

 

No.

 

 

ORDER BY score DESC, id DESC LIMIT 50

[trq]

If that still isn't sorting, your data types are wrong.

[Barand]

IF id=13 should sort before id=34, then you want ASC for id (or leave out ASC as it's the default)

 

ORDER BY score DESC, id LIMIT 50

[hahaitwork]

IF id=13 should sort before id=34, then you want ASC for id (or leave out ASC as it's the default)

 

ORDER BY score DESC, id LIMIT 50

None of them worked actually.. could be because it doesn't select id.. let me retry.

 

$result = mysql_query("SELECT id, name, score FROM high_scores_avoidthecars ORDER BY score DESC, id LIMIT 50 ");

 

Tried this.. still not working as it should >_<

Edited December 3, 2012 by hahaitwork

[trq]

If that still isn't sorting, your data types are wrong.

[hahaitwork]

 

Anymore info you could give me about what you think is wrong ?

[trq]

What data types are the score and id columns ?

[hahaitwork]

What data types are the score and id columns ?

Score = int

id = int

if that is what you mean?

[hahaitwork]

Ehm, how come it got so silence? was it because my answer was totally wrong, or because people got unsure about what it could be?

if it is a easy edit some how, any idea how I could make it say like ?

| 1 | *name* | *score* |

| 2 | *name* | *score* |

| 3 | *name* | *score* |

| 4 | *name* | *score* |

 

That means, somehow add 1-50 on the side of the name and score

Edited December 5, 2012 by hahaitwork

[Christian F.]

If you post an example of what you got, along with how you'd want the end result to look, I'm sure someone can help you out.

[hahaitwork]

If you post an example of what you got, along with how you'd want the end result to look, I'm sure someone can help you out.

 

http://loff-production.com/highscores/avoid-the-cars.html

Here is what I got at the moment.

1. I need numbers next to the players name (hopefully in a own little table) left of the name.

2. I need them sorted after best score, but ALSO after who submitted first of those with the same score.

[Psycho]

1. I need numbers next to the players name (hopefully in a own little table) left of the name.

You can do that through the query, but it is easier, in my opinion, to just do that through the display logic

$position = 0;
while($row = mysql_fetch_assoc($result))
{
   $position++; //Use this value as the number to the left of the player name

   //Code to display the record follows
}

 

2. I need them sorted after best score, but ALSO after who submitted first of those with the same score.

The image you linked to shows the records sorted by best score. Since you don't show the date, there is no way to determine if it is sorting by date as you want. Do your records have a date field? If so, use that. Otherwise you could use the ID field IF that is an auto-increment field AND you only create records and do not edit them. If you don't think the records are in the appropriate order provide:

 

1. A similar image as you posted, but add the ID and/or date field that you are using for sorting

2. The actual query that you are currently using.

[hahaitwork]

You can do that through the query, but it is easier, in my opinion, to just do that through the display logic

$position = 0;
while($row = mysql_fetch_assoc($result))
{
$position++; //Use this value as the number to the left of the player name

//Code to display the record follows
}

 

 

The image you linked to shows the records sorted by best score. Since you don't show the date, there is no way to determine if it is sorting by date as you want. Do your records have a date field? If so, use that. Otherwise you could use the ID field IF that is an auto-increment field AND you only create records and do not edit them. If you don't think the records are in the appropriate order provide:

 

1. A similar image as you posted, but add the ID and/or date field that you are using for sorting

2. The actual query that you are currently using.

 

I do not have a date but I do have "ids" so, the lowest id will be the first that was submitted anyhow.

I know how I want it, the problem is i'm not sure how to write the script .. =)

[Barand]

Is this what you wanted

<?php
include("testDBconnect.php");

$sql="SELECT
  @rownum := @rownum + 1 AS Rank
  , name as Name
  , score as Score
  , id  as ID
FROM high_scores_avoidthecars
   JOIN (SELECT @rownum := 0) as initialise
ORDER BY score DESC, id
LIMIT 50";

$res = $mysqli->query($sql);
$row = $res->fetch_assoc();
echo  "<table border='1'><tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
do {
   echo "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
} while ($row = $res->fetch_assoc()) ;
echo "</table>\n" ;
$res->free();
?>

[hahaitwork]

Is this what you wanted

<?php
include("testDBconnect.php");

$sql="SELECT
@rownum := @rownum + 1 AS Rank
, name as Name
, score as Score
, id as ID
FROM high_scores_avoidthecars
JOIN (SELECT @rownum := 0) as initialise
ORDER BY score DESC, id
LIMIT 50";

$res = $mysqli->query($sql);
$row = $res->fetch_assoc();
echo "<table border='1'><tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
do {
echo "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
} while ($row = $res->fetch_assoc()) ;
echo "</table>\n" ;
$res->free();
?>

 

Error:

Fatal error: Call to a member function query() on a non-object in /customers/a/3/f/loff-production.com/httpd.www/avoidthecars/highscore.php on line 25

 

Line 25:

$res = $mysqli->query($sql);

[Barand]

Sorry about that the mysqli connect is in my included file. You need

 

$mysqli = new mysqli('host', 'user', 'password', 'database');

 

before the query.

[hahaitwork]

Sorry about that the mysqli connect is in my included file. You need

 

$mysqli = new mysqli('host', 'user', 'password', 'database');

 

before the query.

ahh.. I just copied what I had to connect.. my bad Will try it again!

Here is what I tried, got error on line 22 this time (which is the marked text)

Fatal error: Call to a member function fetch_assoc() on a non-object in /customers/a/3/f/loff-production.com/httpd.www/avoidthecars/highscore.php on line 22

 

<html>

<head>

<link href="styleofc.css" rel="stylesheet" type="text/css">
</head>

<?php
$mysqli = new mysqli("x","x","x");

$sql="SELECT
@rownum := @rownum + 1 AS Rank
, name as Name
, score as Score
, id as ID
FROM high_scores_avoidthecars
JOIN (SELECT @rownum := 0) as initialise
ORDER BY score DESC, id
LIMIT 50";

$res = $mysqli->query($sql);
$row = $res->fetch_assoc();		   <-------------- LINE 22! <-------------------------------
echo "<table border='1'><tr><th>" . join('</th><th>', array_keys($row)) . "</th></tr>\n";
do {
echo "<tr><td>" . join('</td><td>', $row) . "</td></tr>\n";
} while ($row = $res->fetch_assoc()) ;
echo "</table>\n" ;
$res->free();
?>
</html>

Edited December 7, 2012 by hahaitwork

[hahaitwork]

*bump*