Display HTML table from SQL DB in custom order

Question

I have created this table with a sql query that looks like this

$result = mysql_query("SELECT * FROM 5050goosedown");

echo "<table border='0' width='975'>
<tr>
<th width='12.5%'>name:</th>
<th width='12.5%'>width:</th>
<th width='12.5%'>height:</th>
<th width='12.5%'>fill:</th>
<th width='12.5%'>our fill:</th>
<th width='12.5%'>old price:</th>
<th width='12.5%'>price:</th>
</tr>";

$i = 1;
while($row = mysql_fetch_array($result)){
    echo "<tr bgcolor='#F5F5F5'>";
  if($i%2 == 0){
      echo "<tr bgcolor='#E5E5E5'>";
  }
  $i++;

  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['width'] . "</td>";
  echo "<td>" . $row['height'] . "</td>";
  echo "<td>" . $row['normal_fill'] . "</td>";
  echo "<td>" . $row['our_fill'] . "</td>";
  echo "<td>" . $row['old_price'] . "</td>";
  echo "<td>" . $row['price'] . "</td>";
  echo '<td bgcolor=#FFFFFF><form method="post" action=""><input type="hidden" name="goosedown_id" value="'.$row['goosedown_id'].'" /><input type="submit" name="sumbit" value="Delete" /></form></td>';
  echo "</tr>";
  }
echo "</table>";

?>

I was wondering what would be the best way to go about displaying the items in the table in a specific order because at the moment is is displaying in alphabetical order. However, I would like it to display in a order of size, i.e., single, super single, queen, king, super king…

I just dont know how to attempt this. Do you think maybe I need another entry in the database?

this is what the table looks like.. as you can see there is a section where people can enter their own values

enter image description here

mysql

CREATE TABLE `5050goosedown` (
    `goosedown_id` int(11) unsigned NOT NULL auto_increment,
    `name` varchar(100) NOT NULL default '',  
    `width` int(8),
    `height` int(8),
    `normal_fill` int(8),
    `our_fill` int(8),
    `old_price` DECIMAL(3,2),
    `price` DECIMAL(3,2), 
    PRIMARY KEY  (`goosedown_id`)
    ) TYPE=MyISAM;

Are you talking about the ability to dynamically sort the results after they've been served to the user? Or are you talking sorting them initially? If the latter, you can do that by means of "ORDER BY" within your select statement. — Mike
– Mike, Commented Oct 3, 2012 at 7:54
Would ORDER BY width be good enough for that? You don't actually explain what the fields mean, so we can't tell you what fields you do or don't need, can or can't use. — MatBailie
– MatBailie, Commented Oct 3, 2012 at 7:55
yea sorry I forgot to add that the user can add their own values to the table.. its a sort of price list for a website for my mum.. — HurkNburkS
– HurkNburkS, Commented Oct 3, 2012 at 7:59

Mahmoud Gamal · Accepted Answer · 2012-10-03 07:55:45Z

3

However I would like it to display in a order of size, i.e., single, super single, queen, king, super king…

Then try this:

ORDER BY
  CASE Size
    WHEN 'sigle' THEN 0 
    WHEN 'super' THEN 1
    WHEN 'single' THEN 2
    WHEN 'queen' THEN 3
    ...
  END , Name

edited Oct 3, 2012 at 7:55

answered Oct 3, 2012 at 7:50

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

HurkNburkS Over a year ago

I should have said.. their is a section I have enaled for them to enter new values.. so I will not always know the what values will be entered.. I am adding an image to my question so you get a better idea of what Im doing

Mahmoud Gamal Over a year ago

@HurkNburkS - So you have these values stored in name columns right? But where are the values in the order you mentioned in your question single, super single, queen, king, super king are stored with that order? Are they stored somewhere else in the order you want? Or where did you get that order if you don't need to write it hardcoded?

HurkNburkS Over a year ago

I have added a screen shot of the table in question. I am woundering if I should add something to my db that allows you to set the row you want your entry to appear in.. just im not sure how else to do this if im allowing the user to enter values in later on...

Alan Shortis Over a year ago

Do you have a seperate table with your sizes which is then joined by ID? If you have a facility to add new sizes, I would also add an option to choose the sort order of the sizes (as a number) then just sort by that. This also means you can change the sort in the DB without touching code.

Mahmoud Gamal Over a year ago

@HurkNburkS - you can make a new table for that with the values in the order you want to sort them with then JOIN that table with your original table to get the order you wish here is a demo for this

|

Collectives™ on Stack Overflow

Display HTML table from SQL DB in custom order

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related