[RESOLVED] Sorting tables

[sgtPepper]

I use PHP script to retrieve data from MySql tables and then display it in HTML tables. However, I couldn't find a way of sorting the tables by a certain data field. I'm not even sure at which level the sorting should be done: MySql, PHP or HTML?
Your help will be much apreciated .

[kows]

sorting is done by MySQL. I'm not sure how you would consider HTML being able to sort.

anyhow, you're looking for the "order by" clause in a SELECT statement. simple syntax:

SELECT fields FROM table WHERE conditions ORDER BY fieldname sort-order;

"sort-order" can either be ASC (ascending) or DESC (descending).

you could also sort by multiple fields, like so:

SELECT * FROM movies WHERE movieName LIKE '%Terminator%' ORDER BY year ASC, title DESC, rating DESC;

[sgtPepper]

Thanks a lot kows.

[penagate]

As a general rule, you should aim to do as much work as possible in the database layer. Database systems exist solely to store and retrieve data; doing such work at the business layer tends to be reinventing the wheel as a hexagon.

[sgtPepper]

OK penagate, I see what you mean, thanks.

And yet another question: When displaying the table in HTML page, I want to apply different color for each table entry by a certain condition (i.e. in a high scores table, different colors for people from different countries).

Hope I made myself clear and again, your help is appreciated.

[kows]

there are multiple ways you could achieve this, but I'm partial to using CSS to do my styling, so you could set up some country classes with background colors set and then have something like this in your code (and this is assuming you're using abbreviations for country names):

<table>
<?php
  while($row = mysql_query($query)){
    $country = strtolower($row['country']); //make this lowercase
?>
  <tr class="<?php echo $country; ?>">
    <td><?php echo $row['name']; ?></td>
    <td><?php echo $row['score']; ?></td>
    <td><?php echo $row['date']; ?></td>
  </tr>
<?php
  }
?>
</table>

and then you might have a stylesheet that looks like this:

.ca td { background: #f00; color: #fff; }
.usa td { background: #00f; color: #fff; }
.uk td { background: #0f0; color: #000; }

if you don't store country abbreviations and instead use names, you could set up an array with abbreviations and do a substitution like that, or you could have a switch statement. if you have a lot of countries, though, an array or switch statement could be hard to maintain (unless you list out every country in it).

let me know if you have any more questions!

[sgtPepper]

Actually the "country" field condition was just for the sake of an example. In real life I have a false preset list of high scores of famous characters, just as the table initializer. And then when real people will start submitting scores, I want to distinguish between the real scores and the faked ones. I added an extra Boolean field to the table were "1" is for preset score and "0" is for real one. So that field (which I named "Preset") would be the condition field.
Hope I made myself clear.

[SambaNeko]

What kows described is still useful - you'll only need to change it slightly.

<table>
<?php
  while($row = mysql_query($query)){ ?>
  <tr<?php echo ($row['Preset']) ? ' class="preset"' :''; ?>>
    <td><?php echo $row['name']; ?></td>
    <td><?php echo $row['score']; ?></td>
    <td><?php echo $row['date']; ?></td>
  </tr>
<?php
  }
?>
</table>

If you're not familiar with it, that in-line if statement is called a ternary operator. It works like so:

(condition) ? value if true : value if false ;

So, if your $row['Preset'] if true (1), it will echo ' class="preset"'. If it's false (0), it'll echo nothing (an empty string).

[kows]

make sure it echos ' class="preset"' (note the space!); otherwise, it won't work correctly.

[sgtPepper]

Thank you guys for your help.
I'm not very good at CSS nor am I at PHP, so my questions might sound silly.
I understand that I should check for each table entry whether Preset is true or false, if it's true the class=preset is assigned to that row.
And then in the CSS file I should have something like:

.preset { background: #f00; color: #fff; }

Am I correct?
couldn't get it working so far though...

[kows]

it sounds correct, yes.

but if you're having problems, you should really be posting your code so that we can see if you're doing something correctly.

[sgtPepper]

OK, here it is:
(I'm not sure that Preset is a real boolean, perhaps it's just "0" or "1".

<h2><p style="color:#2554C7"><u>High Score Table</u></p></h2>


<table class="hallsoffame" cellspacing="0" cellpadding="0" width="97%">

<!--reading first row, which has a different format-->
<?php
$i=0;
$name=mysql_result($result,$i,"Name");
$city=mysql_result($result,$i,"City");
$country=mysql_result($result,$i,"Country");
$score=mysql_result($result,$i,"Score");
$date=mysql_result($result,$i,"Date");
$preset=mysql_result($result,$i,"Preset");
?>

           <tr>
           <td width="20" style="color:#c00;background:#fee">
           <b><?php echo $i+1; ?></b></td>
           <td width="400" style="color:#c00;background:#fee">
           <b><?php echo $name; ?></b> from <?php echo $city; ?><?php  echo $country; ?></td>
           <td width="60" align="center" style="color:#c00;background:#fee">
           <b><?php echo $score; ?></b></td>
           <td width="110" align="right" style="color:#c00;background:#fee">
           <?php echo $date; ?></td></tr>
           
<!---------readig rest of table---------------->
<?php
$i=1;
while ($i < $num) {
$name=mysql_result($result,$i,"Name");
$city=mysql_result($result,$i,"City");
$country=mysql_result($result,$i,"Country");
$score=mysql_result($result,$i,"Score");
$date=mysql_result($result,$i,"Date");
$preset=mysql_result($result,$i,"Preset");
?>

          <tr><?php echo ($row['Preset']) ? ' class="preset"' :''; ?>
          <td width="20">
          <b><?php echo $i+1; ?></b></td>
          <td width="400">
          <b><?php echo $name; ?></b> from <?php echo $city; ?>, <?php echo $country; ?></td>
          <td width="60" align="center">
          <b><?php echo $score; ?></b></td>
          <td width="110" align="right">
          <?php echo $date; ?></td></tr>						

<?php
$i++;
}
?>

[SambaNeko]

You shouldn't have a paragraph tag (<p>) within a headline tag (<h2>), and use of <u> is discouraged. If you're going to use inline styles, your fist line should look like:

<h2 style="color:#2554C7;text-decoration:underline;">High Score Table</h2>

Instead of using mysql_result() a bunch of times, it's simpler to use mysql_fetch_assoc() to get an array of the row's data. Using it will also increase the result set's internal pointer, so you don't have to manually advance it with $i.

<table class="hallsoffame" cellspacing="0" cellpadding="0" width="97&#37;">

<!--reading first row, which has a different format-->
<?php
$i=0;
$row = mysql_fetch_assoc($result);
?>

<tr>
<td width="20" style="color:#c00;background:#fee">
<b><?php echo $i++; ?></b></td>
<td width="400" style="color:#c00;background:#fee">
<b><?php echo $row["Name"]; ?></b> from <?php echo $row["City"]; ?><?php  echo $row["Country"]; ?></td>
<td width="60" align="center" style="color:#c00;background:#fee">
<b><?php echo $row["Score"]; ?></b></td>
<td width="110" align="right" style="color:#c00;background:#fee">
<?php echo $row["Date"]; ?></td></tr>

When doing a while loop of a query result, it's best practice to control it like so:

while($row = mysql_fetch_assoc($result)) {

mysql_fetch_assoc() will return an array while there are still rows in your result set, and it will return false when there are no more rows. So the loop will stop when all rows have been looped. Since you already used mysql_fetch_assoc() once, you're already one row in (and that's fine). Likewise with the above, you'll have an array of the row rather than single vars from mysql_result().

<!---------readig rest of table---------------->
<?php
//$i=1; this is no longer needed because of using $i++ instead of $i+1 in the above code

while($row = mysql_fetch_assoc($result)) {
?>

<tr><?php echo ($row['Preset']) ? ' class="preset"' :''; ?>
<td width="20">
<b><?php echo $i++; ?></b></td>
<td width="400">
<b><?php echo $row["Name"]; ?></b> from <?php echo $row["City"]; ?>, <?php echo $row["Country"]; ?></td>
<td width="60" align="center">
<b><?php echo $row["Score"]; ?></b></td>
<td width="110" align="right">
<?php echo $row["Date"]; ?></td></tr>						

<?php
//$i++; this is no longer needed because mysql_fetch_assoc() automatically increments the internal pointer on each loop
}
?>

And $row['Preset'] doesn't need to be a boolean; unless you're specifically testing both value and var type (like with a === comparison), then 0 evaluates as "false" and 1 as "true". The same can be said for the while() loop mentioned earlier: "$row = mysql_fetch_assoc($result)" yields an array, but that is an implicit "true" when used in evaluation. Only when there are no more rows and the operation returns an explicit false does the loop terminate.

[sgtPepper]

SambaNeko, thanks for your instructive remarks.
I'm trying to adapt them into my code and make it work properly, but so far the result is messy. I'm trying to figure it out myself, will inform later.

[sgtPepper]

The first mysql_fetch_assoc() call is not working for some reason (retrieve no data).
the while loop works fine, but it starts from the first row. So the first row appears twice: first one is empty and second one is good.

The preset thing echos class="preset" 13 times (once for every table's row) for some reason.

           <table class="hallsoffame" cellspacing="0" cellpadding="0" width="97%">

<?php
$row = mysql_fetch_assoc($result);
$i=0;
?>

           <tr><?php echo ($row['Preset']) ? ' class="preset"' :''; ?>
           <td width="20" style="color:#c00;background:#fee">
           <b><?php echo $i+1; ?></b></td>
           <td width="400" style="color:#c00;background:#fee">
           <b><?php echo $name; ?></b> from <?php echo $city; ?>, <?php echo $country; ?></td>
           <td width="60" align="center" style="color:#c00;background:#fee">
           <b><?php echo $score; ?></b></td>
           <td width="110" align="right" style="color:#c00;background:#fee">
           <?php echo $date; ?></td></tr>
           
<!-------------- Loop---------------->
<?php
$i=0;
while($row = mysql_fetch_assoc($result)) {
?>

<tr><?php echo ($row['Preset']) ? ' class="preset"' :''; ?>
<td width="20">
<b><?php echo $i+1; ?></b></td>
<td width="400">
<b><?php echo $row["Name"]; ?></b> from <?php echo $row["City"]; ?>, <?php echo $row["Country"]; ?></td>
<td width="60" align="center">
<b><?php echo $row["Score"]; ?></b></td>
<td width="110" align="right">
<?php echo $row["Date"]; ?></td></tr>						

<?php
}
?>

[SambaNeko]

In your first block of HTML output, you're mostly not using the $row array; variables like $name, $city, etc. should be $row["Name"], $row["City"], etc. This could explain why that row's showing up blank.

In both blocks, this line:

<tr><?php echo ($row['Preset']) ? ' class="preset"' :''; ?>

...should be...

<tr<?php echo ($row['Preset']) ? ' class="preset"' :''; ?>>

...because you want ' class="preset"' to be echoed inside of the <tr> tag; as you have it, it'll echo between the <tr> and <td> tags, which is not a valid location to put text, so browsers will render it outside of the table (why it's echoing it 13 times).

[sgtPepper]

works like a magic!
Now just that first table's row problem...

[kows]

that's because earlier on, Samba wrote this:

<tr<?php echo ($row['Preset']) ? ' class="preset"' :''; ?>>

and you wrote this:

<tr><?php echo ($row['Preset']) ? ' class="preset"' :''; ?>

the echo must be within the TR in order for the attribute to be correct.

the "first" item in your example won't work because you're not defining any of those variables. you are trying to use $name, $city and $country, but all you've defined is $row. $row is an associative array that contains all of that information.

either way, it looks like the first item in your table just has a different background color. you're writing a bunch of redundant code, and so I'm here to stop you ;) you have a counter variable ($i) and a loop. all you need to do is check if the counter variable is the initial value to know whether or not we're on the first iteration of this loop, and so instead of having the mess of code you have there, you can instead have something like:

<?php
  $i = 0;
  while($row = mysql_fetch_assoc($result)){
    $i++; //increment
?>
<tr class="<?php
  echo ($row['Preset']) ? 'preset ' :''; //for the preset class
  echo ($i == 1) ? 'first' :''; //for the first row class
?>">
  <td width="20"><b><?php echo $i; ?></b></td>
  <td width="400"><b><?php echo $row["Name"]; ?></b> from <?php echo $row["City"]; ?>, <?php echo $row["Country"]; ?></td>
  <td width="60" align="center"><b><?php echo $row["Score"]; ?></b></td>
  <td width="110" align="right"><?php echo $row["Date"]; ?></td>
</tr>
<?php } ?>

from the above, you'll need to make a new CSS class called first:

.first {
  background: #fee;
  color: #c00;
}

this will set the row's class to first when $i is equal to 1 (the first time the loop runs), and it will also set the row's class to preset whenever $row['preset'] has a result of true (not equal to 0 or false).

hope that helps! ask questions if any of it doesn't make sense.

edit: ergh, I wrote this a little while ago and was busy. I guess Samba already did reply, but you should take a look at what I've done above anyway!

[SambaNeko]

Mwa ha ha ha! So many times hath kows beaten me to a reply - well not today!

...Though his suggestions are valuable...

[sgtPepper]

That was a very nice piece of code kows!
And it's even working!

Thank you very much guys
See you at my next question...