Keep Server Online
If you find the Apache Lounge, the downloads and overall help useful, please express your satisfaction with a donation.
or
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.
| |
|
 Topic: Data Type Convertion? |
|
| Author |
|
kr33
Joined: 19 Sep 2006
Posts: 64
Location: South Africa
|
 Posted: Thu 12 Oct '06 14:40 Post subject: Data Type Convertion? |
 |
|
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 
| Quote: | | Every man has to go through Hell...to reach his paradise |
|
|
| Back to top |
|
James Blond
Moderator
Joined: 19 Jan 2006
Posts: 7371
Location: Germany, Next to Hamburg
|
| Posted: Thu 12 Oct '06 17:52 Post subject: |
|
|
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
|
| Posted: Fri 13 Oct '06 8:25 Post subject: |
|
|
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: 7371
Location: Germany, Next to Hamburg
|
| Posted: Fri 13 Oct '06 9:39 Post subject: |
|
|
| 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
|
| Posted: Fri 13 Oct '06 9:54 Post subject: |
|
|
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: 7371
Location: Germany, Next to Hamburg
|
| Posted: Fri 13 Oct '06 11:28 Post subject: |
|
|
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  |
|
| Back to top |
|
nrglift
Joined: 16 Nov 2006
Posts: 1
|
| Posted: Thu 16 Nov '06 23:20 Post subject: Try Stripping the commas |
|
|
| Code: | $number = "246,488.35";
$number = str_replace(",", "", $number);
echo $number;
// This will echo 246488.35 |
Try stripping the commas out. |
|
| Back to top |
|
|
|
|
|
|
