PHP Sorting only First two Digits

General PHP Help
#1

I have another really quick question. I have fields on a website that go up to $9,000,000 , but when PHP does the sort function, it only sorts it by the first two digits.

Such as, sorting would be like
$99,000
$98,000,000
$99,500
$99,600

If you can see, it is only sorting by the first two digits. Is there a quick/easy way to fix this? Thanks!

#2

Show some code where you are sorting… Then, maybe we can fix it…

#3

Here is the current code as it stands now. I had this issue in the past and there was a really easy fix - I just can’t remember now. It is sorting by the first two digits in all numeric fields, so when doing the ASC or DESC ordering, some of the bigger numbers are out of place. (see last post)

[php]

<?php //DATABASE SETTINGS $config['host'] = "REMOVED"; $config['user'] = "REMOVED"; $config['pass'] = "REMOVED"; $config['database'] = "REMOVED"; $config['table'] = "entries"; $config['nicefields'] = true; //true or false | "Field Name" or "field_name" $config['perpage'] = 40; $config['showpagenumbers'] = true; //true or false $config['showprevnext'] = true; //true or false /******************************************/ //SHOULDN'T HAVE TO TOUCH ANYTHING BELOW... //except maybe the html echos for pagination and arrow image file near end of file. include './Pagination.php'; $Pagination = new Pagination(); //CONNECT mysql_connect($config['host'], $config['user'], $config['pass']); mysql_select_db($config['database']); //get total rows $totalrows = mysql_fetch_array(mysql_query("SELECT count(*) as total FROM `".$config['table']."`")); //limit per page, what is current page, define first record for page $limit = $config['perpage']; if(isset($_GET['page']) && is_numeric(trim($_GET['page']))){$page = mysql_real_escape_string($_GET['page']);}else{$page = 1;} $startrow = $Pagination->getStartRow($page,$limit); //create page links if($config['showpagenumbers'] == true){ $pagination_links = $Pagination->showPageNumbers($totalrows['total'],$page,$limit); }else{$pagination_links=null;} if($config['showprevnext'] == true){ $prev_link = $Pagination->showPrev($totalrows['total'],$page,$limit); $next_link = $Pagination->showNext($totalrows['total'],$page,$limit); }else{$prev_link=null;$next_link=null;} //IF ORDERBY NOT SET, SET DEFAULT if(!isset($_GET['orderby']) OR trim($_GET['orderby']) == ""){ //GET FIRST FIELD IN TABLE TO BE DEFAULT SORT $sql = "SELECT length FROM `".$config['table']."` LIMIT 1"; $result = mysql_query($sql) or die(mysql_error()); $array = mysql_fetch_assoc($result); //first field $i = 0; foreach($array as $key=>$value){ if($i > 0){break;}else{ $orderby=$key;} $i++; } //default sort $sort="ASC"; }else{ $orderby=mysql_real_escape_string($_GET['orderby']); } //IF SORT NOT SET OR VALID, SET DEFAULT if(!isset($_GET['sort']) OR ($_GET['sort'] != "ASC" AND $_GET['sort'] != "DESC")){ //default sort $sort="desc"; }else{ $sort=mysql_real_escape_string($_GET['sort']); } //GET DATA $sql = "SELECT length,link_to_broker_listing,namelink,year,cost,advertisers,advertiserlink FROM `".$config['table']."` ORDER BY $orderby $sort LIMIT $startrow,$limit"; $result = mysql_query($sql) or die(mysql_error()); //START TABLE AND TABLE HEADER echo "\n"; $array = mysql_fetch_assoc($result); $i = 0; foreach ($array as $key=>$value) { if($config['nicefields']){ $field = str_replace("_"," ",$key); $field = ucwords($field); } $field = columnSortArrows($key,$field,$orderby,$sort); if($i !=2 && $i!=6) echo "\n"; $i = $i +1; } echo "\n"; //reset result pointer mysql_data_seek($result,0); //start first row style $tr_class = "class='odd'"; //LOOP TABLE ROWS while($row = mysql_fetch_assoc($result)){ echo "\n"; $i=0; //used to count fields... foreach ($row as $field=>$value) { if($i==1){ $namelink=$value; } elseif($i==0){ echo""; } elseif($i==2){ echo ""; } elseif($i==3){ echo""; } elseif($i==4){ echo""; } elseif($i==5){ $advertiserlink=$value; } elseif($i==6){ echo ""; } $i=$i+1; } echo "\n"; //switch row style if($tr_class == "class='odd'"){ $tr_class = "class='even'"; }else{ $tr_class = "class='odd'"; } } //END TABLE echo "
" . $field . "
$value " . $namelink . " $value $value " . $advertiserlink . "
\n"; if(!($prev_link==null && $next_link==null && $pagination_links==null)){ echo '
'."\n"; echo $prev_link; echo $pagination_links; echo $next_link; echo "
\n"; } /*FUNCTIONS*/ function columnSortArrows($field,$text,$currentfield=null,$currentsort=null){ //defaults all field links to SORT ASC //if field link is current ORDERBY then make arrow and opposite current SORT $sortquery = "sort=ASC"; $orderquery = "orderby=".$field; if($currentsort == "ASC"){ $sortquery = "sort=DESC"; } if($currentsort == "DESC"){ $sortquery = "sort=ASC"; } if($currentfield == $field){ $orderquery = "orderby=".$field; }else{ $sortarrow = null; } return ''.$text.' '. $sortarrow; } ?>

[/php]

#4

Well, I am guessing you set up your database incorrectly. I suspect you are using a string field to store the amount number. You should be using a NUMERIC filed for numbers not a string field. So, as-is, your string field is working as it should. To fix it, you could do several things, each are different and some can be tricky.
The best is to remember to store all numbers in a database as numeric, not strings. Then, when you display them, use the “number_format()” function to format them into a string for displaying.

Or, you could format the current string and add zeros to the “fake” numbers you are currently using.
So, $9,000,000, $9,999 and $123 would become $9,000.000, $000,009,999 and $000,000,123. This
is a little tricky, but, can be done in a correctly formulated format instruction…

Or, you could run your query and add an order-by option to sort by Len$($price-whatever) THEN order-by $price… (Or was that the other way around…?) So, it would sort by size of “fake” number, $123 would be before $9,999 due to length of the STRING, then sorted by actual values of the STRINGS.

Not sure if all this is making it harder for you to understand or clearing it all up. Basically, you are playing with a string, not a number. In my opinion, the database was not set up correctly for use of this number.
It always makes easier use of and faster database operations if using number fields for numbers.

Hope this helps… Good luck, which ever way you choose, we are here to help…

#5

Right now my database is setup to store the numbers in a CHAR format with length/values set at 100. I use phpmyadmin, what is a good format to store these numbers in that will have them display correctly?

Thanks

#6

Here are two links that explain field types for MySQL databases. Normally for dollar amounts, you use the field type “currency”. This allows for 15 digit plus 4 digits after the period. Usually fits most uses…
But, you have to pick the best field-type for your use. “CHAR” field type is an ODD choice for currency as it is RIGHT-PADDED with zeros. So, “123” is really “123000000” or whatever size you picked as the FIXED-SIZE of the field. (CHAR is a fixed-length field-type) Hope that helps… Here are the links…

http://help.scibit.com/mascon/masconMySQL_Field_Types.html

http://www.w3schools.com/sql/sql_datatypes.asp

One of those should help you!

#7

Thanks for the information.

I have updated my database to use the DECIMAL (15,0) storage mechanism for my dollar amounts.

My only remaining question now is how to format the script above using the number_format PHP function to display the values using commas between the hundreds markers on the currency.

Here is the current display parameter that I am using. Note that it is just displaying the plain value:

[php]
elseif($i==4){ echo"

$$value "; }
[/php]

Thanks for your help!

#8

You have a display of $$value where $value is the number, I assume…
Change this to:

$formatMoney($value)

This will take the $value string and break it into 9,999,999,999.99
(IF no decimals needed make sure that the $value is set up as an integer first (int))…

Hope that helps…

Sponsor our Newsletter | Privacy Policy | Terms of Service