[RESOLVED] error >date & decimal variables appear as zeros

[vidz]

Iam a newbie to php. Iam builing ainventory control system. My problem is with date(type = date) and quantity(decimal). When i insert some values for these categories they show and store as zeros. But varchar types insert correctly. Hope u guys will help me to debug this. thanks in advance.

This is my code

<?php 
session_start(); 
      include("config.php"); 
$generalname     = mysql_real_escape_string($_POST['generalname']);  

$Scientificname  = mysql_real_escape_string($_POST['Scientificname']);  

$quantity        = mysql_real_escape_string($_POST['quantity']); 
$type            = mysql_real_escape_string($_POST['type']); 
$unit            = mysql_real_escape_string($_POST['unit']); 
$date            = mysql_real_escape_string($_POST['date']); 

$qry_add = "INSERT INTO chemical 
             (generalname, Scientificname, quantity, type, unit, date ) 
             VALUES ('$generalname', '$Scientificname', 'quantity', '$type', '$unit','$date') "; 

$count = mysql_query("SELECT COUNT(chemicalname) FROM chemical WHERE chemical name='$generalname'"); 
        if($count==1) 
            { 
            echo "<font color=red> Duplicate Entry. Please Verify Chemical name </font>"; 
            } 
            else 
            { 

           if($result=mysql_query($qry_add)) 
           { 
             echo  '<script language="javascript">'; 
             echo  'alert("you have successfully added one Chemical!" );'; 
             echo  '</script>';    
           } 

           else 
          { 
           echo "<br><font color=red size=+1 >Problem in Adding !</font>" ; 
           echo "ERROR - unable to add new chemical!<br>"; 
           $SQLError =  "SQL ERROR: ".mysql_errno().".  ".mysql_error()."<BR><BR>"; 
           echo "$SQLError"; 
           mysql_close(); 

          } 

        } 
?>

This is my table structure

CREATE TABLE IF NOT EXISTS chemical (
id int(50) NOT NULL AUTO_INCREMENT,
generalname varchar(100) NOT NULL,
Scientificname varchar(100) DEFAULT NULL,
quantity decimal(10,2) DEFAULT NULL,
type varchar(50) DEFAULT NULL,
unit varchar(50) DEFAULT NULL,
date date NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

This is my input
general name > HCl
Scientific name > dd
Quantity > 500.5
type > liquid
unit > l
date > 2012-02-05

This is what system shows in view.php page
general name > HCl
Scientific name > dd
Quantity > 0.00
type > liquid
unit > l
date > 0000-00-00

This is how table back up file show the entry

Dumping data for table chemical

INSERT INTO chemical (id, generalname, Scientificname, quantity, type, unit, date) VALUES
(2, 'HCl', 'dd', '0.00', 'liquid', 'l', '0000-00-00');

[cretaceous]

not sure why you have that problem but this line cannot be helping:

if($result=mysql_query($qry_add))

should be ==

and
a) how are you getting 'date' from your form??
b) 'date' is not a reserved name, but it is still a mysql keyword so shouldn't really be used for a column name as it could be confusing

[Derokorian]

No cretaceous that line is right. What its doing is assigning then result of the function to a variable than evaluating that variable by converting it to boolean if necessary. Its the same as doing:

$result = mysql_query($qry_add);
if( $result != FALSE ) 

[cretaceous]

ok - my bad ! (still looks an unintuitive construction..)

[Derokorian]

Ah well I use similar all the time:

$query = "SELECT columns FROM table";
if( !($result = $db->query($query)) ) {
   Error::db($query,$db->errno,$db->error);
} elseif( $result->num_rows == 0 ) {
   Error::noresults();
} else {
   while( $row = $result->fetch_object() ) {

   }
}

As for the problem, first thing I notice is you're trying to put a decimal point number through mysql_real_escape_string (which is designed for strings as the name suggests). Second thing I notice is the use of the mysql library, you should instead be using mysqli or PDO see API choosing for more info.

Next thing I notice is in your query you are using 'quantity' instead of '$quantity' so you are trying to insert a string into a numeric column, hence when it is type cast to decimal it turns into a 0.

Lastly, how is your date formatted from the form? If its in the wrong format then mysql will just insert zeros. See MySQL Date and Time Types for more information.

[vidz]

Thanku all
Sorry 4 da late reply.

The error was mysql_real_escape_string() as u mentioned. Now it is working perfectly. Thanks again 😃 😃