insert cast

nefus · 05-27-2010, 10:03 PM

How in the word do you use CAST with mysql? I've tried google for a basic example with no luck. Could somebody help me out with a basic example?

I am trying to insert something like 555.555 into a float column but I either get 555 or 555.6 in the column after the insert. *shrug*

Code:

$station_insert = mysqli_prepare($link, "INSERT INTO stations (Company_Name, station_name, Address, City, State, Country, Zip, Phone, Lat, Lon ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");


mysqli_stmt_bind_param($station_insert, 'ssssssiiss', $company_name, $station_name, $address, $city, $state, $country, $zip_code, $phone, $lat, $lon);

I've tried ii, ss and dd on the when binding... is CAST what I need to use for the lat and lon floats?

sketchMedia · 05-28-2010, 10:50 AM

Can I see your table structure?

nefus · 05-28-2010, 01:59 PM

Sure...

CREATE TABLE IF NOT EXISTS `stations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Company_Name` varchar(30) COLLATE latin1_general_ci NOT NULL,
`station_name` varchar(30) COLLATE latin1_general_ci NOT NULL,
`Address` varchar(30) COLLATE latin1_general_ci NOT NULL,
`City` varchar(30) COLLATE latin1_general_ci DEFAULT NULL,
`State` varchar(30) COLLATE latin1_general_ci DEFAULT NULL,
`Country` varchar(20) COLLATE latin1_general_ci DEFAULT NULL,
`Zip` varchar(11) COLLATE latin1_general_ci DEFAULT NULL,
`Phone` varchar(12) COLLATE latin1_general_ci DEFAULT NULL,
`Lat` float NOT NULL,
`Lon` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=19 ;

sketchMedia · 05-28-2010, 02:41 PM

Can't replicate this unfortunately, I don't have mysqli installed (for whatever reason) on my works dev machine.

Something is rounding it somewhere, I'll bet its mysqli, just try dumping it as a string type I don't think mysql will mind if it has quotes around it or not. Failing that there must be some form of rounding error at mysql's end, try specifiying the mantissa/exponent in the float args:

Code:

ALTER TABLE `stations` MODIFY COLUMN `lat` FLOAT(6,3)  DEFAULT NULL;
ALTER TABLE `stations` MODIFY COLUMN `lon` FLOAT(6,3)  DEFAULT NULL;

If not I'll have to wait to get home and try mysqli on my home web server.

nefus · 05-28-2010, 03:51 PM

That didn't work at all.

The table altered but the saved results were not showing up correctly.

Do I have to format the variable as a float before it gets inserted?

nefus · 06-09-2010, 08:29 PM

Btw, for anyone who wants to know how I got around this... I changed it to a double instead of float and it worked. Don't use the above alter table or it messes things up too. Just make sure you use 'd' when binding.