MySQL Query problems

[irving]

here is an example of a query I am using:

$result = mysql_query ("SELECT * FROM Mlsfull1 WHERE LISTNUMBER = '$listnumber%' OR LISTPRICE >= '$minprice%'
AND LISTPRICE <= '$maxprice%'");

The users inputs the minprice and maxprice. the query is supposed to search for all records between those values.

The problem is the value for LISTPRICE is in the format 12,345 and the <= and >= doesn't work properly. I would like to change the value to 12345 so it will work. Any ideas.

[Anon]

Jeff,

As a temporary fix: use the string replace function in mysql (see section on String Functions, its about 6.3.ish).

A much better fix would be to convert your database field over to the appropriate numberic type and then use setlocale and printf in PHP to print your numbers out correctly.

Cheers

Justin

[Anon]

how do you recommend converting the database field? I download this data to a text file every night and this is the format for the listprice field.

I will take a look at the string replace function. thanks.

[Anon]

Jeff,

E-mail with detailed instructions on route.

One way in summary (have assumed table doesn't have more than a couple of thousand rows).

1. Backup table.
2. Create dummy column with correct defn (i.e. numeric with correct number of decimals)
3. Update table, filling new column with values from old_column using string replace fn.
4. Drop original column.
5. Rename dummy column to old column name.

Voila.

Justin