When using each data type of mysqli_stmt_bind_param

Asked

Viewed 339 times

4

According to the documentação:

i - corresponding variable has type integer
d - corresponding variable has type double
s - corresponding variable has type string
b - corresponding variable is a blob and will be sent in packets

So I was wondering when exactly to use each of these types, what characters each of these types accepts.

The doubt arose because in Mysql database use for example VARCHAR to save currency values, and I’ve always associated the VARCHAR with string, then I was passing the currency values as s, but I saw that the right thing is with d, because in the bank was saving without .and without ,. The s accept only letters? Double accepts only numbers and . and ,? What’s the point of b? Anyway, a general and updated explanation would be nice. Thanks.

php mysqli typing

1 answer

4


As told by @rray b is used for binary data, for example photos, zipped files, etc.

The guys VARCHAR and TEXT are fields that use string that is accept from A to Z from 0 to 9, so the use should be the s, do not use to make calculations.

So the supported characters vary depending on the collection used. For example:

  • utf8_general_ci supports Unicode characters however it is simpler.
  • utf8mb4_unicode_ci supports most (if not all) Unicode characters.
  • latin1_general_ci does not use Unicode, but supports accents.

That is, the s you can pass a string, but when the process reaches the query the characters can be lost if they are not compatible with the collection used.

Working with Monetary Values

If you’re going to use monetary values, maybe you should use DECIMAL (note that you will not use comma).

Check out these answers (despite talking about sql-server): /a/5760/3635

The decimal type

Decimal type does not have the same type accuracy problems float and double. It is a fixed point type, not floating. In other words, if you define a field in a database as Mysql of type DECIMAL(10,2), means that it will be a 10-digit (base 10) number, of which 2 will be after the comma. Always. Some databases even store such data internally as strings.

This storage strategy eliminates the problem of data storage rounding, but on the other hand it decreases type flexibility. While it may not be a good choice for data where the number of decimals cannot be set, it is a good option for example for financial values (since these always have 2 decimal places).

It should also be taken into account that in some cases decimal values will be converted to floating point numbers implicitly when used in calculations. In this case if the expected result also of the decimal type, it will be rounded and converted from float to decimal. I mean, the accuracy is better, but it’s not limitless.

Source: http://douglascunha.com/blog/2010/08/tipos-sql-float-double-decimal/#sthash.VGlFZBL7.dpuf

  • 2

    I’m going to improve the answer, I just noticed that you have some subplots.

  • Thanks, I think you were at the heart of my problem, which was to be using the wrong guy in the bank... I think to be perfect you could say something about every kind of data. About the bit is already clear, but about the s for example, does it only accept the letters of the alphabet? When I save a snippet in HTML for example, which type is right? Thanks for now! + 1

  • 1

    @gustavox s is for string and string accepts any type of characters within the selected encoding type, for example latin1_general_ci, all ASCII characters would be accepted or utf8_general_ci accepts several Unicode characters.

  • So it’s the right kind to save HTML clips right... thanks man, you and some other users here have helped me a lot! Hugs.

  • 1

    @gustavox Yes can save html because the characters are ASCII and the mysql character collections support such characters, even the most basic - Note: in fact I prefer to save exactly what the user writes, even if it Injete html where it could not and only use htmlspecialchars read after using the SELECT .... But this is my opinion :) - Now for monetary value really the best is decimal - Hug

  • I got it, thanks! Hugs!

  • So, man, I came here to look at this topic again and I saw that there’s nothing on the d, and I have a little doubt, because I need to apply to several fields and I wanted to be sure even before I do, because there are many fields... money and percentage I must use the d same right? DECIMAL in Mysql and the din the bind_param right? What are the characters that the daccepted? I granted a small reward for this answer, and for the explanations, and for all that you have helped me... thanks!

  • 1

    @gustavox I didn’t even remember kk - Thanks!! : ) I’ll try to add some "tips" in the evening reply to you.

  • Calm man, I just have to thank you! Thanks!

  • 1

    Thanks man, it is an honor to know that you liked the answer of github! (to post here for you do not forget the tips kkkkk) Thanks anyway!

  • 1

    @gustavox Do you believe that some bastard stole the phone wires from the street above my house and affected the whole neighborhood, to no net, to using the 3g, tomorrow if the internet is cool I edit and add the tips there. See you around!

  • Wow, forcing someone to use 3G, now that should be a heinous crime! :)

Show 7 more comments

Browser other questions tagged php mysqli typing

You are not signed in. Login or sign up in order to post.