I need those decimals!! Please help..

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

I need those decimals!! Please help..

Post by pookie62 »

Hi,
I'm importing score values into a Score table.
Data is inserted, but the decimal values are all zero.

This is the data from the csv file:

Code: Select all

Id	         InschrijvingId	SerieNr	Tijd	Punten
-1342470134	-2066231603	1	10,53	100
1642315376	-2036312949	1	14,63	100
1752376856	-2000188461	1	9,61	100
This is how i't's shown in the table:

Code: Select all

Id               |InschrijvingId  SerieNr  Tijd  Punten  
-932280388  |529280601        1      14.00 100 
-1847839509 | -2128794105   1       19.00 100 
1783236609  |-2105805680     1      19.00 100
This is the script I use:

Code: Select all

<?php
mysql_select_db($database_ASN, $ASN);
if (!file_exists('Score.csv'))
{
die ("Er is geen Score bestand aangetroffen, voer eerst de Upload uit vanaf het Website menu !");
}
$fp = fopen('Score.csv', 'r');
 
// first line has column names 
$data = fgetcsv($fp, 2048, ';'); 
$columns = array(); 
foreach($data as $column) 
$columns[] = trim($column, '"'); 

$sql = 'INSERT INTO testscore ('; 
$sql .= implode($columns, ', '); 
$sql .= ') VALUES ('; 

// next lines have values 
while (($data = fgetcsv($fp, 2048, ';')) !== FALSE) 
{ 
  $checksql = "SELECT Id FROM testscore WHERE Id='".$data[0]."'"; 
  $result = mysql_query($checksql) or die(mysql_error()); 
  $row = mysql_fetch_row ($result); 
  if ($row[0]) 
  { 
     $sql2 = "UPDATE testscore SET "; 
     $sql_clause=array(); 
     foreach ($data as $key=>$column) 
     {    
        if ($key != 0) 
        { 
             $sql2 .= $columns[$key]."='".mysql_real_escape_string($column)."',"; 
        } 
     } 
     $sql2 = rtrim($sql2, ","); 
     $sql2 .= " WHERE ".$columns[0]." = '".mysql_real_escape_string($data[0])."'"; 

  } else 
  { 
     $sql2 = $sql; 
     foreach($data as $column) 
     { 
       $column = mysql_real_escape_string($column); 
       $sql2 .= "'{$column}', "; 
     } 
     $sql2 = rtrim($sql2, ', '); 
     $sql2 .= ')'; 
    // echo 'Executing: ' . $sql2 . '</br>'; 
  } 
  mysql_query($sql2) or print(mysql_error() . '<br>'); 
  } 
fclose($fp);


Can anyone help me please to correct this ???
Thanks a lot !
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

The first problem I found is that your decimal point is a comma. That's going to cause lots of problems when you've got a comma delimited file. Either make it tab (or some other character) delimited, or change your decimal point. I'd bet that'll fix your problem.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

I believe his file is semicolon delimited

Code: Select all

while (($data = fgetcsv($fp, 2048, ';')) !== FALSE)
To add my two cents in, having those queries inside of the while loop, along with foreach()'s inside of the while loop is making your code way slower than it should be.

You could always store all of those values into arrays, and run your queries after the loops.

I believe that would make your code more readable too, making debugging that much easier.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

1) DOH!!!

2) Moving on... output $sql2 before it's executed to see what the query is.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Hi there..
Thanks for your replies, first let me correct something..
The example shows no seperator, which is the semicolon. You don't see it in the sample because I copy/pasted this after opening the csv file in Excel (sorry...)

scrotaye > how should the code look in your opinion ?

In another script this code did the trick:

Code: Select all

foreach($data as $column) 
  { 
    $column= preg_replace('#^((?:(?:(?:[0-9]{1,3}\.(?:[0-9]{3}\.)*)(?:[0-9]{3}))|0|[0-9]*))(,[0-9]+)?$#e','str_replace(\'.\',\'\',\'\\1\').str_replace(\',\',\'.\',\'\\2\')',trim($column, '"')); 
    $sql2 .= "'{$column}', "; 
  }
I don't know how to insert this in the script.
Someone of you guys ??


Thanks again !
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Anyone can help me ??
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

What is the type of the column that needs to have the floating numbers?
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Hi Timvw,

Id bigint(20)
InschrijvingId bigint(20)
SerieNr smallint(6)
Tijd decimal(10,2)
Punten smallint(6)
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

So what's next ??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I would bet MySQL isn't returning those fields as strings. They are too large for PHP to handle them in a different form (it appears..)
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

Hi Feyd,

Could be, I don't have a great knowledge of php.. :oops:
What is the solution then for importing these values ?

When I can import a value with two decimals, I have what I want..
Can it be done ? And if so, how ??

Thanks for your time and support, I depend on it...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

the decimals aren't the problem simply converting the comma to a dot should get them working. I was talking about the two ID fields getting mangled.
pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

The conversion can only take place with the PHP script, just before inserting I think.
How should I do that in the script I use

Can you please show me.. ?
Thanks
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

pookie62
Forum Commoner
Posts: 92
Joined: Tue Dec 07, 2004 2:44 pm

Post by pookie62 »

This is how I did it, seems to work..
Does it look oke ??

Thanks !
Post Reply