trouble with ordering MYSQL numbers

Anon

I have a MySQL database and am using PHP to bring out the values. The site is a real estate site and each of the listings have been entered with price values (column name price) without the $ but WITH the commas. For Example, a house listed at $105,000 is entered into the database at 105,000 and my php (html) outputs the $. I have set up the page and added the "ORDER BY price ASC" to the Query. The problem that I am having is that the PHP is ordering the price column using the first digit in the price value. For example,

105,000
235,000
479,000
59,000
65,000
670,000

How can I get the PHP to read in the value as not having the comma or at least ignoring the comma? Can this be done in the query?

What can I do?

Andrew

Anon

Probably not. If you are only getting prices from the query you could loop through your resultset and add each price to an array without the comma and then sort the array and then display each element of the array. Something like this:

$result = mysql_query("SELECT price from tablename", $db);

if (mysql_num_rows($result) > 0) {

while ($myrow = mysql_fetch_array($result)) {

$prices[] = ereg_replace(",", "", $myrow["price"]);

}
}

asort($prices);

while (list ($key, $val) = each ($prices)) {
print $val . "<br";
}

If you need to get more fields as well you could simply use this instead:

$result = mysql_query("SELECT * from tablename", $db);

if (mysql_num_rows($result) > 0) {

while ($myrow = mysql_fetch_array($result)) {

$prices[] = ereg_replace(",", "", $myrow["price"])."|".$myrow["field2"]."|".$myrow["field3"];//And so on....

}
}

asort($prices);

while (list ($key, $val) = each ($prices)) {
$display = explode("|", $prices);
print "Price= ". $display[0] . "<br";
print "Field2= ". $display[1] . "<br";
print "Field3= ". $display[2] . "<br";
}

Hope that helps!

Anon

Your approach is not good. Store numeric data as numbers not as strings, which is what is happening in your case, the sorting you are seeing is because the values are being treated strictly as a string of chars, and thus the ordering you are getting is the correct one from that point of view. In other words, the db is doing the correct thing in view of the type of data is processing.

Store the info as numbers, and then use something like printf() or better yet number_format() in PHP to display the values as your heart desires.

Of course it would be trivial to write a PHP script to read the value, use str_replace() to eliminate the comma, and then stick it back into a new numeric field in the db. Although I would rather, dump the db to a text file and use awk/sed/perl (any of those) to eliminate the comma from the appropriate field, redefine the table so the value is numeric, and then load it back.

HTH

Anon

Of course, there is nothing that can replace good db design from the get-go, but if you already have a buttload of records in this poor form, and you just need a quick fix, I think my suggestion will work, again this is making a fix to an already hosed up db design.