Greater than/Less than on data with ","s

Anon

Here's my problem.

I have a column called "networth" in my MySQL table, which is of the type varchar usnsigned, that has data such as "7,000,000".

What I want to do is a query like:

$networth = 5000000;

mysql_query("SELECT * FROM table WHERE networth > $networth");

That sort of thing should normally work, but because of the stupid commas it doesn't do what it's supposed to.

The user submits the value of $networth, so I don't want to force them to use commas.

If I strip the commas out of the database, which I can do easily, I'll have to insert them back into the string before displaying them to the browser.

So is there a way I can do this greater than/less than type of query without altering my database?

If not, what's the best way to format a string with commas, such as making "1234" into "1,234"?

Thanks in advance for any help.

Anon

this may help:
http://www.php.net/manual/en/function.number-format.php
Jim

superslacker

I think your problem here is not how to convert the user's input to a formatted number. Instead, you should look at changing your database so that it stores the data properly.

Storing numeric data as strings in a database is almost never a good idea. First of all, strings require more storage space than numbers.

More importantly, it is good design practice to perform formatting of data in the presentation layer of a system, rather than in the data layer. In this case, it is preferred to store the numbers as numbers, and format them in PHP when required to display them.

Storing the networth as numbers allows mathematic functions to be performed on them in the database. If you leave them as strings, I'm doubtful that the database can even compare them properly.

My suggestion is to store the numbers as numbers. In your PHP code, convert a value to a properly comma-formatted string using the number_format PHP function when you need to display it. It'll make your life a lot simpler.

Hope this helps.

Anon

Thanks to both of you, as I now know exactly what I need to do now. Which is alot better than where I was earlier today 😉

Luckily the DB is still in testing with 1 record in it, so now I can just drop it and remake it.