Hi David,
Have a field in my database containing numbers 1-20.
Trying to display a list of items from this field in descending order using
$sql = "SELECT DISTINCT(field8) as field8 FROM `".$config_databaseTablePrefix."products` ORDER BY field8 DESC";
I get
9
8
7...
12
11
10
Any ideas on how to get it displaying
12
11
10
9...
Thanks
Adrian
Hi David,
Changed the AS Clause values to myfield8 but still the same. Here is the full code as using rewrite aswell
<?php
$sql = "SELECT DISTINCT(field8) as myfield8 FROM `".$config_databaseTablePrefix."products` ORDER BY myfield8 DESC";
if (database_querySelect($sql,$rows))
{
foreach($rows as $row)
{
if ($config_useRewrite)
{
$href = $config_baseHREF."numbers/".tapestry_hyphenate($row["myfield8"])."/";
}
else
{
$href = $config_baseHREF."search.php?q=myfield8:".urlencode($row["myfield8"]).":";
}
print "<a href='".$href."'>".$row["myfield8"]."</a><br />";
}
}
?>
Does the code above look ok
Hi Adrian,
Sorry - I just realised; it's doing a text sort; so i'm assuming that you are using
VARCHAR as the data type. In order to MySQL perform a numeric sort, the field must
be of a numeric type - so if this field is always going to contain numbers, I would
use type INT which should then work as expected!
Cheers,
David.
Hi David,
Changed to INT and it works great, thanks.
I plan to change the fields to contain text and as I see it, they will sort as follows.
9 Megapixels
8 Megapixels
7 Megapixels and less
6 Megapixels
12 Megapixels and More
11 Megapixels
10 Megapixels
Do you know of any way of sorting the above using varchar or any other type.
Thanks
Adrian
Hi Adrian,
Just came across a trick that might work, where you add 0 to the VARCHAR field in the SORT clause,
making it numeric and therefore sorting normally. This seems to work even when text is included
in the field after the number.
So, reverting back to VARCHAR, try:
$sql = "SELECT DISTINCT(field8) as myfield8 FROM `".$config_databaseTablePrefix."products` ORDER BY myfield8+0 DESC";Cheers,
David.
Hi Adrian,
It looks fine, so i'm assuming that the sort order is side-effect of the way DISTINCT() works. As an experiment; what about using a different name in the AS clause; and then sorting by that name? For example:
$sql = "SELECT DISTINCT(field8) as myfield8 FROM `".$config_databaseTablePrefix."products` ORDER BY myfield8 DESC";...this should remove any ambiguity and might do the trick..
Cheers,
David.