logo
Apache Lounge
Webmasters

 

About Forum Index Downloads Search Register Log in RSS X


Keep Server Online

If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.

or

Bitcoin

A donation makes a contribution towards the costs, the time and effort that's going in this site and building.

Thank You! Steffen

Your donations will help to keep this site alive and well, and continuing building binaries. Apache Lounge is not sponsored.
Post new topic   Forum Index -> Coding & Scripting Corner View previous topic :: View next topic
Reply to topic   Topic: Data Type Convertion?
Author
kr33



Joined: 19 Sep 2006
Posts: 64
Location: South Africa

PostPosted: Thu 12 Oct '06 14:40    Post subject: Data Type Convertion? Reply with quote

Hi Guys!

I'm trying to import a cvs file into mysql, I have no problem importing it, but i'm trying to keep certain values in the format it supposed to be in.

For example:

I have the following value (as text in the cvs file)

246,388.50

I want to keep it in the above format, but as an actual numeric value, not as text using the VARCHAR data type, i've tried using the DECIMAL data type, setting its length to 8 and 2 as the amount of values of the 8 after the decimal point.

But instead, when I use DECIMAL it shows the follow:
246.00

Why? and how do I solve this problem? Please help, ANYONE Sad

Quote:
Every man has to go through Hell...to reach his paradise
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7288
Location: Germany, Next to Hamburg

PostPosted: Thu 12 Oct '06 17:52    Post subject: Reply with quote

How many inserts will that be? What did you tried to select your string?

Maybe you can use php preg_match_all function. But I'm not good of regex, so I can tell you how.
Back to top
kr33



Joined: 19 Sep 2006
Posts: 64
Location: South Africa

PostPosted: Fri 13 Oct '06 8:25    Post subject: Reply with quote

Im importing approximately 159 rows.

I have a csv file which is comma delimited. The values are between quotes eg: "246,488.35","345,366.49"

The above is just an example of a part of a line in the csv file. Now when I import this into my database using phpmyadmin and if i set the field types in phpmyadmin to DECIMAL (8, 2) then when i import it only takes every thing before the comma in the above examples, so instead of getting, 246,488.35 as a value I just get 246.00 as a result. the reason is because of the comma, i tried removing the comma in the src file making is "246488.35" and it works, but I would not alwalys be able to do that. Every other string field VARCHAR(50) works perfectly, only the fields that I need to be numeric are giving me problems.

I need the requires fields to be of a numeric type so the I can use them to plot graphs properly.

Hope you understand what I mean.

Do you have any Idea?
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7288
Location: Germany, Next to Hamburg

PostPosted: Fri 13 Oct '06 9:39    Post subject: Reply with quote

Why is there a need for numeric type and not VARCAHR? In VARCHAR you can also put numbers in and read it as numbers.
Back to top
kr33



Joined: 19 Sep 2006
Posts: 64
Location: South Africa

PostPosted: Fri 13 Oct '06 9:54    Post subject: Reply with quote

OK, I didnt realise that. So would the reading in of the value as a number be the same eg:

Code:

$sql = "SELECT field1, field2, field3 ".
          "FROM <table> ".
          "WHERE <something> = <something else>";

$result = mysql_query($sql)
   or die(mysql_error());

$row = mysql_fetch_array($result);
extract($row);

$data = array($field1, $field2, $field3);


code to plot graph
Code:

$c->dataset($data, 0x9999ff, "Numbers");


Inorder to draw the graphs, we need numbers...So even tough in the csv file, the numbers to be used to draw the graph are stored as text like so ("246, 488.35"), it will automatically be converted to a number? Right or Wrong?
Back to top
James Blond
Moderator


Joined: 19 Jan 2006
Posts: 7288
Location: Germany, Next to Hamburg

PostPosted: Fri 13 Oct '06 11:28    Post subject: Reply with quote

I never worked with extract, but if $data contains 246, 488.35 PHP will take that as a number. I don't know your object $c but normaly a number does not contain a , so I'd convert it to 246488.35
Not sure if your Class / function needs the ,

At least one tip: Trial and error Wink
Back to top
nrglift



Joined: 16 Nov 2006
Posts: 1

PostPosted: Thu 16 Nov '06 23:20    Post subject: Try Stripping the commas Reply with quote

Code:
$number = "246,488.35";

$number = str_replace(",", "", $number);

echo $number;
//  This will echo 246488.35


Try stripping the commas out.
Back to top


Reply to topic   Topic: Data Type Convertion? View previous topic :: View next topic
Post new topic   Forum Index -> Coding & Scripting Corner