Floats and LOAD DATA (PHP & MySQL) 
Author Message
 Floats and LOAD DATA (PHP & MySQL)

I have a text-file containing products and their prices for a small
shop. I upload and insert the text-file into a MySQL table using the
LOAD DATA LOCAL INFILE command, and it really works a treat.

My problem is that when inserting prices of more than one thousand,
the field it gets inserted into formats it wrong. Ex:

The field "price" is of type float(13,2), inserting via LOAD etc.
formats the price 1.139,00 to 1.14. I figured it had to do with
different delimiters of thousands and hundreds (the text-file uses
danish delimiters), so I cleaned up the file replacing the . with a ,
in prices above 1000, and vice versa.

This doesn't seem to fix the problem though. I can tell that the
replacing of ,'s and .'s is successfull (because I print the resulting
output to the screen), but MySQL still interprets the number as 1.14,
and not as 1.139,00 or 1,139.00 (yes, I tried both).

Actually, I tried a plethora of formats including the following:

1139
113900
1.139,00
1,139.00
1139,00
1139.00

But MySQL interprets them all as 1.14.

Inserting 1139 via CLI or phpMyAdmin works as expected, I get a
price of '1139.00'.

I this an issue anybody dealt with before? I'd be happy to hear your
findings on the matter.

The code I wrote for importing the text-file can be found here:

< http://www.*-*-*.com/ ~jcv/usenet/tusindfryd.phps>

The echo's are in danish, but you'll get the point of the script,
hopefully.

All thoughts on this appreciated.

Thanks.

--

 "When I eat a biscuit," said Arthur, "it stays eaten."



Tue, 14 Jun 2005 02:24:09 GMT  
 Floats and LOAD DATA (PHP & MySQL)

Quote:

>I have a text-file containing products and their prices for a small
>shop. I upload and insert the text-file into a MySQL table using the
>LOAD DATA LOCAL INFILE command, and it really works a treat.

>My problem is that when inserting prices of more than one thousand,
>the field it gets inserted into formats it wrong. Ex:

>The field "price" is of type float(13,2), inserting via LOAD etc.
>formats the price 1.139,00 to 1.14. I figured it had to do with
>different delimiters of thousands and hundreds (the text-file uses
>danish delimiters), so I cleaned up the file replacing the . with a ,
>in prices above 1000, and vice versa.

Try decimal data type.  You can use number_format () function on both
ends, while saving and displaying.


Tue, 14 Jun 2005 04:28:39 GMT  
 Floats and LOAD DATA (PHP & MySQL)

Quote:

>>The field "price" is of type float(13,2), inserting via LOAD etc.
>>formats the price 1.139,00 to 1.14. I figured it had to do with
>>different delimiters of thousands and hundreds (the text-file uses
>>danish delimiters), so I cleaned up the file replacing the . with a ,
>>in prices above 1000, and vice versa.

> Try decimal data type.  You can use number_format () function on both
> ends, while saving and displaying.

With decimal instead of float, 1139 becomes 1.13 in the table.

As for using number_format(), how would I do that in a LOAD DATA LOCAL
INFILE command? It seems like you can't really do much about the SQL
that inserts the file, and I've only been able to figure out how to
swap , and . before actually inserting the file.

Am I right in thinking, that when a figure has been inserted, and
formated to the field it has been inserted into, the original figure
(1139) doesn't exist in the table, only the formated (1.13) number
does?

Thanks for your suggestion.

--

 It's just a job. Grass grows, birds fly,
 waves pound the sand. I beat people up. - Muhammed Ali, 1977.



Tue, 14 Jun 2005 05:02:54 GMT  
 Floats and LOAD DATA (PHP & MySQL)

Quote:

> As for using number_format(), how would I do that in a LOAD DATA LOCAL
> INFILE command? It seems like you can't really do much about the SQL
> that inserts the file, and I've only been able to figure out how to
> swap , and . before actually inserting the file.

I think it may be more pertinant to post on the MySQL mailing lists
regarding what you could do ... it's certainly a more appropriate
place anyway.

My suggestion: try getting rid of the ',' (without quotes) from the
file. use a decimal point '.' as the delimiter.

--
Registered Linux User #273744
<www.volutin.net -- everything irrelevant>
now playing :: [winamp not running]



Tue, 14 Jun 2005 20:28:24 GMT  
 Floats and LOAD DATA (PHP & MySQL)

Quote:
> I think it may be more pertinant to post on the MySQL mailing lists
> regarding what you could do ... it's certainly a more appropriate
> place anyway.

Ok, will do. Thanks.

Quote:
> My suggestion: try getting rid of the ',' (without quotes) from the
> file. use a decimal point '.' as the delimiter.

No go, I tried with 1139.00 instead of 1,139.00, but it still formats
it to 1.13 with decimal(13,2). Thanks for the suggestion though.

--

    "Great things are afoot!"



Tue, 14 Jun 2005 21:16:26 GMT  
 Floats and LOAD DATA (PHP & MySQL)

Quote:

>>I think it may be more pertinant to post on the MySQL mailing lists
>>regarding what you could do ... it's certainly a more appropriate
>>place anyway.

> Ok, will do. Thanks.

no problem :)

Quote:
>>My suggestion: try getting rid of the ',' (without quotes) from the
>>file. use a decimal point '.' as the delimiter.

> No go, I tried with 1139.00 instead of 1,139.00, but it still formats
> it to 1.13 with decimal(13,2). Thanks for the suggestion though.

That's really really strange. really strange. unfortunately i have no
idea what's going on there ...

--
Registered Linux User #273744
<www.volutin.net -- everything irrelevant>
now playing :: [winamp not running]



Sat, 18 Jun 2005 09:48:55 GMT  
 Floats and LOAD DATA (PHP & MySQL)

Quote:

>> No go, I tried with 1139.00 instead of 1,139.00, but it still formats
>> it to 1.13 with decimal(13,2). Thanks for the suggestion though.

> That's really really strange. really strange. unfortunately i have no
> idea what's going on there ...

Well, it was I who mere not paying attention. I hadn't made the file
writeable in the first place, so the changes I had made to the contents
of it, and I thought were replaced with the original content were
written to /dev/null. The content of the actual file never changed,
hence the persistent error.

Thank you for your help.

--

    "Great things are afoot!"



Sun, 19 Jun 2005 22:06:27 GMT  
 Floats and LOAD DATA (PHP & MySQL)

Quote:

>>That's really really strange. really strange. unfortunately i have no
>>idea what's going on there ...

> Well, it was I who mere not paying attention. I hadn't made the file
> writeable in the first place, so the changes I had made to the contents
> of it, and I thought were replaced with the original content were
> written to /dev/null. The content of the actual file never changed,
> hence the persistent error.

lol ... sorry Jonas but that made my day :)

Quote:
> Thank you for your help.

not a problem
--
Registered Linux User #273744
<www.volutin.net -- everything irrelevant>
now playing :: [winamp not running]


Tue, 21 Jun 2005 20:52:09 GMT  
 Floats and LOAD DATA (PHP & MySQL)

Quote:

>> Well, it was I who mere not paying attention. I hadn't made the file
>> writeable in the first place, so the changes I had made to the contents
>> of it, and I thought were replaced with the original content were
>> written to /dev/null. The content of the actual file never changed,
>> hence the persistent error.

> lol ... sorry Jonas but that made my day :)

No need to be sorry, because in some sick convoluted way, it really
made my day too. And I laughed at it too, but only after banging my
head repetetively against a wall for around 48 minutes. (:

--

You moved your mouse. Windows needs to be restarted in order for the
changes to take effect. Do you want to restart your computer now?



Fri, 24 Jun 2005 23:34:23 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. loading javascript arrays (or listboxes) with mysql data after a page has loaded without a refresh

2. Inserting data from php form to MySQL

3. drawing a binary tree with php gd from mysql data

4. Using PHP to transfer data between 2 MySQL databases

5. formatting MySQL/PHP data into columns, rows, ecc.

6. PHP not inserting data correctly into MySQL

7. Data Load Tools for MySQL

8. Need a host with PHP & MySQL support

9. php & mysql linux web hosting

10. Solution - Apache, Redhat 8.0, PHP & MySQL

11. Apache, Redhat 8.0, PHP & MySQL

12. NewB PHP & mySQL Q

 

 
Powered by phpBB® Forum Software