Inserting PHP float/decimal values into MySQL

Question

It's a pretty simple question to be honest. I've been looking for a while now on Google for a solution but nothing seems to work. I have the following field in my database:

decimal(2,1)

I have two variables in PHP (which come from values inserted into a form via POST) I want to add together and then insert into this field.

$sql2 = $link->prepare("INSERT INTO league_stats (match_id, rating)
VALUES (?, ?)");
$sql->bind_param("ii", $match_id, $rating);

$match_id = $_SESSION["match_id"];
$rtg1 = $_POST[$rating_1"];
$rtg2 = $_POST[$rating_2"] / 10;
$rating = $rtg1 + $rtg2;

For example, rtg1 would be 7 and rtg2 would be 3 divided by 10 so it comes out as 0.3. I then add these two numbers together to make 7.3. When I go to insert it into the database, it always displays the second digit as 0. So instead of 7.3 it would come out as 7.0. I've tried many different methods but I always get the exact same result.

I even assigned $rating to a raw value just to test if there was something wrong with my variables:

$rating = 7.5

Still comes out as 7.0.

Could somebody please provide an example of how to correctly insert a float type PHP variable into MySQL? And also maybe explain how to correctly add two float values together? Thanks!

you're telling your DB library you're inserting an int. So if you pass in 7.5, the db library has been TOLD it should get truncated down to 7, because it's supposed to be an int. — Marc B
– Marc B, Commented Dec 3, 2013 at 18:09
I don't understand, you are binding before set values: $sql->bind_param("ii", $match_id, $rating);, $match_id = $_SESSION["match_id"]; $rtg1 = $_POST[$rating_1"]; $rtg2 = $_POST[$rating_2"] / 10; $rating = $rtg1 + $rtg2; — jacouh
– jacouh, Commented Dec 3, 2013 at 18:14
@jacouh, that's normal. Mysqli takes the values of those variables when the code calls execute(). This allows you to bind variables once, then execute the SQL multiple times, changing the variables each time. — Bill Karwin
– Bill Karwin, Commented Dec 3, 2013 at 18:15
@Bill Karwin, Thank you. I used always PDO::bindValue(). I would read the manual. — jacouh
– jacouh, Commented Dec 3, 2013 at 18:50

kero · Accepted Answer · 2013-12-03 18:19:44Z

6

You are telling php to cast $match_id and $rating to integer. You should use:

$sql->bind_param("id", $match_id, $rating);

instead of

$sql->bind_param("ii", ...

edited Dec 3, 2013 at 18:19

kero

10.7k5 gold badges44 silver badges52 bronze badges

answered Dec 3, 2013 at 18:18

l-x

1,5699 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

samer.j92 Over a year ago

Ahh thank you! I actually did try changing it to 'd' instead of 'i' earlier but I had made other edits to the code which wasn't making it work. I reverted these changes to the code I had above and changed it to 'd' and it works perfectly now. :)

Collectives™ on Stack Overflow

Inserting PHP float/decimal values into MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related