Decimal into MySQL?

Databases
1

Hi,

I have a DECIMAL (4,2) column in MySQL, but when I try to input 6.50 into this column, it shows as 6.00.

I’ve tried to send the value as text, integer, and currency, but can’t seem to get it to stick.

Anyone got any ideas?

2

Hi,

I have a DECIMAL (4,2) column in MySQL, but when I try to input 6.50 into this column, it shows as 6.00.

I’ve tried to send the value as text, integer, and currency, but can’t seem to get it to stick.

Anyone got any ideas?

3

What MYSQL version are you using? You are using the correct length DECIMAL (4,2) to save 6.50. I have also verified that in the mysql version 5.0.45.

4

Think I’m using 5.0.67

5

Sent how? PHP? MyPHPAdmin?
It shows where?

6

You double posted: http://www.sitepoint.com/forums/showthread.php?t=633249

7

sent from a php form (6.50), insert into SQL statement, in the database it shows as 6.00

8

Post the code

9

threads merged.

10

mysql changed the way decimal columns work in version 5. change your column definition to DECIMAL(4,2)

11

MYSQL: 5.0.67
COLUMN: DECIMAL(4,2)
FORM:
<tr valign=“baseline”>
<th>Price</th>
<td><span id=“sprytextfield1”>
<input type=“text” id=“count” name=“servprice” value=“” size=“32” /><br />
<span class=“textfieldRequiredMsg”>Please enter Price</span>
</span></td>
</tr>

SQL:
$insertSQL = sprintf(“INSERT INTO tbl_service (FK_freeid, servtitle, servlangs, servlangt, servcurrency, servrate, servprice) VALUES (%s, %s, %s, %s, %s, %s, %s)”,
GetSQLValueString($_POST[‘FK_freeid’], “int”),
GetSQLValueString($_POST[‘servtitle’], “text”),
GetSQLValueString($_POST[‘servlangs’], “text”),
GetSQLValueString($_POST[‘servlangt’], “text”),
GetSQLValueString($_POST[‘servcurrency’], “text”),
GetSQLValueString($_POST[‘servrate’], “text”),
GetSQLValueString($_POST[‘servprice’], “int”));

mysql_select_db($database_conndb2, $conndb2);
$Result1 = mysql_query($insertSQL, $conndb2) or die(mysql_error());

ENTERED VALUE: 6.50
DATABASE VALUE: 6.00

12

MYSQL: 5.0.67
COLUMN: DECIMAL(4,2)
FORM:
<tr valign=“baseline”>
<th>Price</th>
<td><span id=“sprytextfield1”>
<input type=“text” id=“count” name=“servprice” value=“” size=“32” /><br />
<span class=“textfieldRequiredMsg”>Please enter Price</span>
</span></td>
</tr>

SQL:
$insertSQL = sprintf(“INSERT INTO tbl_service (FK_freeid, servtitle, servlangs, servlangt, servcurrency, servrate, servprice) VALUES (%s, %s, %s, %s, %s, %s, %s)”,
GetSQLValueString($_POST[‘FK_freeid’], “int”),
GetSQLValueString($_POST[‘servtitle’], “text”),
GetSQLValueString($_POST[‘servlangs’], “text”),
GetSQLValueString($_POST[‘servlangt’], “text”),
GetSQLValueString($_POST[‘servcurrency’], “text”),
GetSQLValueString($_POST[‘servrate’], “text”),
GetSQLValueString($_POST[‘servprice’], “int”));

mysql_select_db($database_conndb2, $conndb2);
$Result1 = mysql_query($insertSQL, $conndb2) or die(mysql_error());

ENTERED VALUE: 6.50
DATABASE VALUE: 6.00

13

I don’t know what GetSQLValueString does, but I can guess. If you are casting that value as an int, you will lose your decimal position. Typecast it as a float if you need the decimal.

EDIT: And if you want to see exactly what I mean, echo $insertSQL to see the exact query you are passing to MySQL

14

Ah, ok that sounds good, I’ll try it out in the morning.
Cheers

15

Hi, I’ve changed the TYPE of ‘servprice’ to FLOAT, but it still won’t accept decimals…

16

Post the code of the function GetSQLValueString

17

if (!function_exists(“GetSQLValueString”)) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = “”, $theNotDefinedValue = “”)
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists(“mysql_real_escape_string”) ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case “text”:
$theValue = ($theValue != “”) ? “'” . $theValue . “'” : “NULL”;
break;
case “long”:
case “int”:
$theValue = ($theValue != “”) ? intval($theValue) : “NULL”;
break;
case “double”:
$theValue = ($theValue != “”) ? “'” . doubleval($theValue) . “'” : “NULL”;
break;
case “date”:
$theValue = ($theValue != “”) ? “'” . $theValue . “'” : “NULL”;
break;
case “defined”:
$theValue = ($theValue != “”) ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

$editFormAction = $_SERVER[‘PHP_SELF’];
if (isset($_SERVER[‘QUERY_STRING’])) {
$editFormAction .= “?” . htmlentities($_SERVER[‘QUERY_STRING’]);
}

if ((isset($_POST[“MM_insert”])) && ($_POST[“MM_insert”] == “form1”)) {
$insertSQL = sprintf(“INSERT INTO tbl_service (FK_freeid, servtitle, servlangs, servlangt, servcurrency, servrate, servprice) VALUES (%s, %s, %s, %s, %s, %s, %s)”,
GetSQLValueString($_POST[‘FK_freeid’], “int”),
GetSQLValueString($_POST[‘servtitle’], “text”),
GetSQLValueString($_POST[‘servlangs’], “text”),
GetSQLValueString($_POST[‘servlangt’], “text”),
GetSQLValueString($_POST[‘servcurrency’], “text”),
GetSQLValueString($_POST[‘servrate’], “text”),
GetSQLValueString($_POST[‘servprice’], “int”));

mysql_select_db($database_conndb2, $conndb2);
$Result1 = mysql_query($insertSQL, $conndb2) or die(mysql_error());

$insertGoTo = “freelancer_details.php?id=” . $row_rsFreeLancer[‘freeid’] . “”;
if (isset($_SERVER[‘QUERY_STRING’])) {
$insertGoTo .= (strpos($insertGoTo, ‘?’)) ? “&” : “?”;
$insertGoTo .= $_SERVER[‘QUERY_STRING’];
}
header(sprintf(“Location: %s”, $insertGoTo));
}

18

You should do this in your scipt:


GetSQLValueString($_POST['servprice'], "float"));

And then you’ll also need to add this new type to the GetSQLValueString function, and make it perform the necessary checks. Using floatval for example.


case "float":
$theValue = ($theValue != "") ? floatval($theValue) : "NULL";
break;
19

Top stuff! Thanks man, you’ve sorted it!

20

prices should be DECIMAL, not FLOAT