?
Solved

mysql_query not inserting negative values using PHP

Posted on 2007-10-07
31
Medium Priority
?
672 Views
Last Modified: 2013-12-13
I'm using mysql_query in php with an insert sql command and it is not updating a decimal field with negative values, they default to zero. Positive values are ok. The same sql command works fine in phpmyadmin. Any ideas please?
0
Comment
Question by:ncw
  • 15
  • 10
  • 4
  • +1
31 Comments
 
LVL 48

Expert Comment

by:hernst42
ID: 20029663
Can you show the code where you build the sql-query?
If a 0 is added to the table the nthe format of the inserted value was wrong (eg. thousand delimters)
0
 
LVL 27

Assisted Solution

by:Cornelia Yoder
Cornelia Yoder earned 80 total points
ID: 20029665
Is the decimal field SIGNED?  It would help if you showed

1.  The characteristics of the field
2.  The phpmyadmin query that works
3.  The php/mysql statement where the query fails
4.  The echoed value of the variable going into the decimal field prior to the php query

and any other possible factors.
0
 
LVL 1

Author Comment

by:ncw
ID: 20029666
This is the query as echo'd froom the code:
insert into geocodes (address,latitude,longitude,date_added) values ('SomeVillage,SomeTown','-1.571242','50.918902', now())
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 21

Expert Comment

by:nizsmo
ID: 20029668
Yes, an example query and example code would be useful here. Also have you tried quoting the negative number in php: "-0.12" in the query?

Also it is good debugging practice to echo out the query so that you can see exactly what has been submitted to mysql and if it was any different than expected.

Let me know how you go.
0
 
LVL 1

Author Comment

by:ncw
ID: 20029669
I've not specifically SIGNED the field, I'm not sure how I would do that. In PhpMyadmin there is an attributes dropdown but there's no option for 'SIGNED', there is one for 'UNSIGNED', so I was assuming default was SIGNED. Anyway the field takes a negative number because as I say the sql above works fine in PhpMyAdmin.

0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20029672
Ahh I see, have you tried using no single quotes?

insert into geocodes (address,latitude,longitude,date_added) values ('SomeVillage,SomeTown',-1.571242,50.918902, now())
0
 
LVL 48

Expert Comment

by:hernst42
ID: 20029673
you treat numbers as string try to use:

insert into geocodes (address,latitude,longitude,date_added) values ('SomeVillage,SomeTown',-1.571242,50.918902, now());

no quotes arounf numbers. mysql might convert the strings wrong.
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 20029675
Have you tried it without the quotes?  

insert into geocodes (address,latitude,longitude,date_added) values ('SomeVillage,SomeTown',-1.571242,'50.918902', now())

Does it work that way?

How about with arithmetic?  

insert into geocodes (address,latitude,longitude,date_added) values ('SomeVillage,SomeTown',0-1.571242,'50.918902', now())

Does it work that way?

What are the characteristics of the latitude field?
0
 
LVL 1

Author Comment

by:ncw
ID: 20029677
Getting somewhere, if I put '-1.23' in the sql instead of the variable then it works.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20029680
Have you tried without the quotes? normally numbers in the query do not require quotes. It may also be that you have too many decimal places?
0
 
LVL 1

Author Comment

by:ncw
ID: 20029686
The insert code is:
$sql = "insert into geocodes (address,latitude,longitude,date_added) values ('".$address."','".$lat."','".$long."', now())";
mysql_query($sql);

The value in $lat = -1.571242
Using the variable results in a zero value inserted, but directly entering the number in the sql without using a variable works fine. Does it have to be CAST?
0
 
LVL 1

Author Comment

by:ncw
ID: 20029696
I started without quotes but then had an error, maybe due to something else as I've tested again without quotes but, although there is no syntax error, the value in the db is still zero when using the variable.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20029699
Try this:

$lat = (decimal)-1.571242; // casting to decimal

then modify the query a little bit:
$sql = "insert into geocodes (address,latitude,longitude,date_added) values ('".$address."',".$lat.",'".$long."', now())";

Let me know how you go.
0
 
LVL 1

Author Comment

by:ncw
ID: 20029703
Sorry, too quick on my last comment, query does fail without the quotes around the values. Integers don't need quotes, do I need to define the value as a decimal, (DEC)-1.234 or something?  
0
 
LVL 1

Author Comment

by:ncw
ID: 20029706
Query fails using (decimal) or (dec) infront of the $lat.

insert into geocodes (address,latitude,longitude,date_added) values ('SomeVillage,SomeTown',-1.571242,'50.918902', now())

Causes the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1.571242,'50.918902', now())' at line 1
0
 
LVL 1

Author Comment

by:ncw
ID: 20029710
MySql version is 4.1.22
Php version is 4.4.7
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20029713
I was testing this, and realized that somehow mysql won't accept negative decimals unless the field type is a float? What is your field type in the database which stores lat? Maybe you can try changing it to a float and see if it works.

Let me know how you get on.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20029714
And yes quotes ARE needed (surprised...)
0
 
LVL 48

Expert Comment

by:hernst42
ID: 20029720
I suggest building the sql like this:

$sql = sprintf("insert into geocodes (address,latitude,longitude,date_added) values ('%s', %F, %F, now())", mysql_real_escape_string($address), $lat, $long);

This way you should be safe, if a address contains a ' which might lead to sql-errors, alos latitude and lognitude should be formated valid.
0
 
LVL 1

Author Comment

by:ncw
ID: 20029726
I had tried DOUBLE and DECIMAL, and now tried FLOAT, but all give a zero value for a negative in the variable. I know the field will take a negative value as it works when a fixed value is in the sql, but not when contained in the variable. It's something to do with how Php or MySql is treating the variable.

Using (decimal), (dec), or (DEC) in front of the variable with or without quotes causes the following error:

Parse error: syntax error, unexpected T_VARIABLE in <dirPath>/get_xml.php on line 40
0
 
LVL 1

Author Comment

by:ncw
ID: 20029734
hernst42: using your method the query works without error, but when $sql is echo'd the latitude value is 0.000000 and obviously the value in the db is the same.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20029736
Weird, I have just replicated based on your example as below, and it works...
My field is a float by the way:

if(!mysql_connect("localhost","root",""))
            die(mysql_error());
            
      mysql_select_db("test");
      $lat = -1.571242;
      
      $query = "insert into te (dectest) values ('$lat')";
      
      $result = mysql_query($query);
      
      
      if($result)
            echo "Success";


The above code works assuming you have a table called te and a field dectest which is a float.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20029742
Changing the float field to the decimal field also works, provided your decimal field is set correctly:
decimal(10, x) where x is at least 6 (for 6 decimal places for your latitude value).

Not too sure what is different from my example to yours?
0
 
LVL 1

Author Comment

by:ncw
ID: 20029751
nizsmo: That works for me too. However (here's a bit more information) it is not working when $lat takes it's value from an xml file (it will be a string I assume).

          ob_start();
          $retval = @readfile($url);
          if ($retval) { // no readfile error
              $xml = ob_get_contents();
             // parse xml for <coordinates> and </coordinates>
                $b = strpos($xml, '<coordinates>');
      $e = strpos($xml, '</coordinates>');
      ob_end_clean();
      $coordinates = substr($xml,$b,$e);
      if($coordinates) {
        $coords = explode(',',$coordinates);
        if(sizeof($coords)>1) {
          //$lat = -1.571242; // this works
         $lat = $coords[0];   // this doesn't
         $long = $coords[1];
etc ...
0
 
LVL 1

Author Comment

by:ncw
ID: 20029759
$lat = (float)$coords[0];

This gives a 0 (no errors).
0
 
LVL 48

Accepted Solution

by:
hernst42 earned 980 total points
ID: 20029763
In this case your source is an invalid number.

instead using outputbuffering to get the xml try:
$xml = file_get_contents($url);
// parse xml for <coordinates> and </coordinates>
$b = strpos($xml, '<coordinates>');
$e = strpos($xml, '</coordinates>');
$coordinates = substr($xml,$b,$e-$b+1);

then check if you got the contents of the coordinates rights.
0
 
LVL 1

Author Comment

by:ncw
ID: 20029766
Someone had the same problem here: http://www.thescripts.com/forum/thread602839.html

There doesn't appear to be any extra characters in the string, but I'm wondering if there's something different (not accpted) about the minus sign.
0
 
LVL 21

Assisted Solution

by:nizsmo
nizsmo earned 940 total points
ID: 20029778
All I can think of now is the minus sign, since everything else seems to be the same. Casting to float should do the trick (in fact, PHP does that automatically) so the problem lies in the variable $coords[0] being not valid.
0
 
LVL 1

Author Comment

by:ncw
ID: 20029780
My silly mistake! I've got html tags in the value and of course they don't appear in the echo.
0
 
LVL 21

Expert Comment

by:nizsmo
ID: 20029787
Glad you found the problem! :)
0
 
LVL 1

Author Comment

by:ncw
ID: 20029810
Thanks everyone for your help.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question