TalkPHP - insert cast

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - insert cast (http://www.talkphp.com/general/5429-insert-cast.html)

nefus

05-27-2010 10:03 PM

insert cast

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.

All times are GMT. The time now is 09:26 AM.