GIDForums  

Go Back   GIDForums > Computer Programming Forums > MySQL / PHP Forum
User Name
Password
Register FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
 
Thread Tools Search this Thread Rate Thread
  #11  
Old 23-Apr-2009, 11:54
MisterChucker's Avatar
MisterChucker MisterChucker is offline
Junior Member
 
Join Date: Mar 2009
Location: Cyberspace, Earth
Posts: 53
MisterChucker is a jewel in the roughMisterChucker is a jewel in the roughMisterChucker is a jewel in the rough

Re: MySQL error when uploading CSV file


1. The date needs to be entered as a string, so use single quotes around the value.

2. There are more variables in this script than necessary.

3. For splitting a string on a single character, explode() is faster than split().
PHP Code:

explode('/', $buffer[$i]) 



4. Omit the semicolon from the end of queries given to mysql_query().

Here is a simpler script:
PHP Code:

<?php
header('Content-Type: text/plain');

// Missing database connection

$fh = fopen('sample-data.txt', 'r');
while (!feof($fh))
{
    // Loads the next line into an array
    $csv = fgetcsv($fh, 8192);
    
    // Splits the date string into date parts
    $dp = explode('/', $csv[3]);
    
    // Rearranges the date parts and adds dashes
    $csv[3] = "{$dp[2]}-{$dp[0]}-{$dp[1]}";
    
    // Builds the query, adding a comma between each value and single-quotes around string values
    $query = "INSERT INTO `anii_cp1`.`student` (`student_ID`, `course_code`, `GPA`, `commencement_date`, `process_category`) "
           . "VALUES ({$csv[0]}, {$csv[1]}, {$csv[2]}, '{$csv[3]}', '{$csv[4]}')";
    
    if (false === mysql_query($query))
        echo 'Query failed: ';
    
    echo $query . "\n";
}
fclose($fh);
?>

Last edited by MisterChucker : 23-Apr-2009 at 12:52.
  #12  
Old 23-Apr-2009, 13:49
MisterChucker's Avatar
MisterChucker MisterChucker is offline
Junior Member
 
Join Date: Mar 2009
Location: Cyberspace, Earth
Posts: 53
MisterChucker is a jewel in the roughMisterChucker is a jewel in the roughMisterChucker is a jewel in the rough

Re: MySQL error when uploading CSV file


Oops, I noticed I have the date parts in the wrong order. The line should look like this:
PHP Code:

$csv[3] = "{$dp[2]}-{$dp[1]}-{$dp[0]}"; 


  #13  
Old 25-Apr-2009, 06:17
lanz lanz is offline
New Member
 
Join Date: Oct 2007
Posts: 21
lanz is on a distinguished road

Re: MySQL error when uploading CSV file


Hi! Thanks for your reply.

I tried your little script but it didn't work [still got 0000-00-00] and then i tried your suggestions just in case but I still got 0000-00-00.

Maybe it needs to be changed into a date format in PHP first?
  #14  
Old 25-Apr-2009, 11:11
MisterChucker's Avatar
MisterChucker MisterChucker is offline
Junior Member
 
Join Date: Mar 2009
Location: Cyberspace, Earth
Posts: 53
MisterChucker is a jewel in the roughMisterChucker is a jewel in the roughMisterChucker is a jewel in the rough

Re: MySQL error when uploading CSV file


Here are some things that could cause the date to appear to be inserted as 0000-00-00, in descending order of probability.

1. Insert queries will not insert a record if another record has the same primary key. Make sure your query is not trying to insert a record that already exists.

2. Make sure the date in your query has single-quotes around it.

3. When you tried my script, did you add the appropriate mysql_connect() and mysql_select_db() statements?

4. When you tried my script, did you make the change to the date order that I posted?
  #15  
Old 26-Apr-2009, 01:59
lanz lanz is offline
New Member
 
Join Date: Oct 2007
Posts: 21
lanz is on a distinguished road

Re: MySQL error when uploading CSV file


Thanks! I accidentally put it in a different column. [I've updated the code since] Sorry for all of the trouble!
 
 

Recent GIDBlogProblems with the Navy (Chiefs) by crystalattice

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Airport Log program using 3D linked List : problem reading from file batrsau C Programming Language 11 29-Feb-2008 08:44
After execution - Error cannot locate /Skin File? WSCH C++ Forum 1 05-Mar-2005 21:03
CD burner wont burn!! robertli55 Computer Hardware Forum 1 18-Jun-2004 11:53
Yet another CD burner problem: Lite-On LSC-24082K Erwin Computer Hardware Forum 1 22-May-2004 12:28
CD Buring Failed skanth2000 Computer Hardware Forum 1 15-Nov-2003 04:52

Network Sites: GIDNetwork · GIDWebHosts · GIDSearch · Learning Journal by J de Silva, The

All times are GMT -6. The time now is 04:33.


vBulletin, Copyright © 2000 - 2009, Jelsoft Enterprises Ltd.