Dear Monks, I am totally perplexed about a rather bizzare little bug I cannot seem to fix; 

  my $price = '23,356.00';
  my $itemref = 'S10099';   

  print "price = $price\n";

  $price =~ s/,//;

  print "new price = $price\n";

  my $query = "UPDATE items SET price='$price' WHERE itemref='$itemref
+';";

   print $query;

   $sth = $dbh->prepare ( $query );
   $sth->execute ();
   $sth->finish ();

[download]
The price column in the database is a decimal(15,2)

Ok so the first print of price gives the expected '23,356.00'

the strip commas regex runs

second print out of the price var shows it has removed the commas:

'23356.00'

the print out of the query reads :

UPDATE items SET price='23501.35' WHERE itemref='S10099';

however when I check the value saved in the database what I find is :

23.00

which is the price with everything after where the comma WAS... removed for no explicable reason.

I tried entering the number raw with commas into the db, same effect.

I tried putting the s/,//; into a sub called removecommas and setting a new var to its return... same effect.

can someone help me please?


-------- UPDATE -----------


I have implemented the changes in structure suggested by the kind chaps below, however it still didn't work.

I then changed the SQL column type to char(15), and had a look at what I got, for some reason the s/,//; is not working, even tho it prints out the var as if it has worked, the SQL was still receiving the same var with the commas in. Thankfully my price sorting routines still work even on a char column, so for now Im going to have to put up with it.

Thanks All.

Si

In reply to Bizzare comma bug by simonodell

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.