Proper way of searching for maximum price in MySQL

I have 2 seperate tables:

In one, the price column is char(10),
In the other, the price column is decimal(16,2)

Some prices are strored with comma's, some with decimals, some not. Example:

1
1.00
5,000
5,000.00
15,000,000

My subroutine which I inject directly into the SQL statement is as follows:
      public function compare_sql_currency($sqlfieldname, $currency, $compareOperator='<=') {
            return "CAST(REPLACE(`{$sqlfieldname}`, ',', '') AS decimal(10,2)){$compareOperator}CAST(REPLACE('{$currency}', ',', '') AS decimal(10,2))";
      }

When I use '500,000.00' for currency, it returns everything in table 2 ...

I need a catchall solution that has mysql returning the correct results...

Please advise
MarkProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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
0
MarkProgrammerAuthor Commented:
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?
0
Ray PaseurCommented:
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!
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

MarkProgrammerAuthor Commented:
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?
0
Ray PaseurCommented:
This looks right to me.  PHP will give you a decimal value in the form of a string, and all of this information in the database is stored and returned in string format.  The database is smart enough to round the decimal values correctly as they are inserted.
http://iconoun.com/demo/temp_mark.php

<?php // demo/temp_mark.php

/**
 * See http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28631502.html#a40652144
 */
error_reporting(E_ALL);
echo '<pre>';

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

, '$3'
, '3.14159'
)
;
// 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
;

foreach ($things as $original)
{
    $converted = preg_replace($regex, NULL, $original);
    $final = (string) trim($converted);
    echo PHP_EOL . "Original: $original; Final: $final";
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MarkProgrammerAuthor Commented:
Thanks, I will test it Monday when I get back.
0
Ray PaseurCommented:
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.
0
MarkProgrammerAuthor Commented:
Ok, thanks
0
MarkProgrammerAuthor Commented:
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;
	
}

Open in new window

0
MarkProgrammerAuthor Commented:
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;
		
	}

Open in new window


And

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

Open in new window

0
Ray PaseurCommented:
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.
0
MarkProgrammerAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.