Proper way of searching for maximum price in MySQL

Add a column to the table with a definition of decimal(10,2).  Run your query that corrects the existing format to decimal(10,2) and insert the results into the new column.  Then when you run a SELECT query you can get the prices ordered correctly.  To find the highest price, ORDER BY new_decimal_column DESC LIMIT 1

Doing that would require me to modify code in too many places and this is a live environment (plus I am on a deadline) .. any way to deal with the DB structure as is?

There are lots of ways to deal with this, and your exact choice would depend on the level of activity on the server, the size of the tables, etc.  You might consider copying the table, making the changes described above in the copied table, then testing your new query.  If it works as expected, take the site down for a few seconds, rename the tables so that the new copy assumes the name of the old table, then you can put the site back online.  Adding a column to a table is something we do all the time in database design and refactoring, and it has no effect on correctly written applications.

The normal design of web applications today is a Model-View-Controller pattern.  The Model (your database) is unconcerned with the View (what the client sees) and the Controller just shuffles data back and forth between them.  In the MVC design, the data would be kept in numeric format, probably as signed decimal values throughout the application.  Your application would be able to use the values for arithmetic.  The View might be the appropriate place to insert the number_format() or money_format() calls that produces the final output strings.  

If you have more than one place that prepares this output for use in the View, it's a big-time candidate for refactoring!

Ok, so if I change to a decimal as you suggest, can you give me the best PHP code to convert values to decimal format based upon the possible price value formats given?

Thanks, I will test it Monday when I get back.

It's not a flawless piece of code.  For example, it would pass "4.5-3+0.00017" and the database might not know exactly what to make of that :-)  So if there are edge cases in the input values it might be worth tightening it up some.  But for the test cases above it returns sensible values.  Please post back if you have any questions; we'll be here to help.

Ok, thanks

Works perfect, just 2 questions ...

1) with regard to the element '3.14159' .. I get a data truncated error when inserting this into MySQL ...
2) when I query the new decimal column, do I quote the parameter, example:
$value = currency_to_decimal('1450.00');
select * from table where DecimalCol<'$value' or is it
select * from table where DecimalCol<$value

Thanks

Here is the code

$things = array
( '1'
, '1.00'
, '5,000'
, '5,000.00'
, '15,000,000'

, '$3'
, '3.14159'
)
;

foreach($things AS $row) {
	echo currency_to_decimal($row)."\n";
}

function currency_to_decimal($original) {
	// AN EXPRESSION THAT WILL KEEP ONLY THE USEFUL NUMERIC INFORMATION
	$regex
	= '#'           // REGEX DELIMITER
	. '['           // START CHARACTER CLASS
	. '^'           // NEGATION - MATCH NONE OF THESE CHARACTERS
	. '0-9'         // DIGITS
	. '\.'          // ESCAPED DECIMAL
	. '\+'          // ESCAPED PLUS
	. '\-'          // ESCAPED MINUS
	. ']'           // END CHARACTER CLASS
	. '#'           // END REGULAR EXPRESSION
	;

	$converted = preg_replace($regex, NULL, $original);
	$final = (string) trim($converted);
	return $final;
	
}

Select allOpen in new window

Never mind, solved this is as follows however please let me know if the code could use improvement:

	public function currency_to_decimal($original) {
		// AN EXPRESSION THAT WILL KEEP ONLY THE USEFUL NUMERIC INFORMATION
		$regex
		= '#'           // REGEX DELIMITER
		. '['           // START CHARACTER CLASS
		. '^'           // NEGATION - MATCH NONE OF THESE CHARACTERS
		. '0-9'         // DIGITS
		. '\.'          // ESCAPED DECIMAL
		. '\+'          // ESCAPED PLUS
		. '\-'          // ESCAPED MINUS
		. ']'           // END CHARACTER CLASS
		. '#'           // END REGULAR EXPRESSION
		;

		if(!$original) $original = '0.00';
		$final = (string) number_format(trim(preg_replace($regex, NULL, $original)), 2, '.', '');
		return $final;
		
	}

Select allOpen in new window

And

	public function compare_sql_currency($sqlfieldname, $currency, $compareOperator='<=') {
		$currency = $this->currency_to_decimal($currency);
		return "`{$sqlfieldname}`{$compareOperator}'{$this->escape($currency)}'";
	}

Select allOpen in new window

It looks fine to me.

with regard to the element '3.14159' .. I get a data truncated error when inserting this into MySQL ...

That might make sense if the column was defined DECIMAL(4,2) because some of the digits were lost.

I made it 16,2 .. I saw the output didn't trim it to 2 decimal points but I just used number_format afterwards.. now all the price columns are in decimal :) Thanks