Help with SQL/PHP Error.

[jhartman]

I know that I'm new to the forum, but I'm stuck. Recently I had to switch web hosts, and my DB worked fine before, but now it's broke. The version of MySQL that this was for was 5.0.51a ; my new web host uses version 4.1. I've been trying to find the syntax error, but I cannot locate it. Here is the error message I get

Unable to execute the query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'decimal(6,2)) as MilesFrom from store_locations order by MilesFrom asc LIMIT 0, ' at line 1

Here is the code-

$query = "select lon, lat from zipData where zipcode = $zip";



$result = mysql_query($query,$con) or die ('Unable to execute the query: ' . mysql_error());

$row = mysql_fetch_array($result); 

extract($row);



$lonfrom = $row['lon'];

$latfrom = $row['lat'];



$query = "select storename, storechain, phone, address, city, st, zip, lat, lon, cast((degrees(acos(sin(radians($latfrom)) * sin(radians(lat)) + cos(radians($latfrom)) * cos(radians(lat)) * cos(radians($lonfrom - lon)))) * 69.172) as decimal(6,2)) as MilesFrom from store_locations order by MilesFrom asc LIMIT $startpos, 10";



$result = mysql_query($query,$con) or die ('Unable to execute the query: ' . mysql_error());



$num_results = mysql_num_rows($result); 

 

Any suggestions would be greatly appreciated!

 

Thanks.

[fenway]

DECIMAL CAST-ing is only from 5+.

[jhartman]

Thanks for the reply.

 

Ah, I see. So my best bet would be to see if I can upgrade to 5+? Because I really don't have the time to redo our "store locator"

[fenway]

You can format the string on your own.

[jhartman]

If 4.x doesn't allow casting, what would be the best way to do it?

Would I need to define the decimal as a different type, then just use that in place of "as decimal"?

[fenway]

I suppose you could use format and a replace to get rid of the comma... or just handle it in php for now.