Import csv into mysql database - error

Question

I created a php script wihic i will put into cron jobs and should be executed at midnight, but this is not a issue. Issue is when cron runs my code it deletes all records from database but DOES NOT insert new recordes from CSV file. Any hint?

PHP code is below:

<?php 

# I run this script from a cron job every night to update 
# the mysql database I use with my employee web site 
# so it matches my local database every day.  Feel free to 
# modify it to meet your specific needs.  If you find it 
# usefull, drop me an email and let me know. 

# edit the follow six items to use the script 

# first connect to your mysql database 
# i have my connection settings in a diferent file 
# so i just include that file in all my scripts 
include("db.php"); 

# assign the tables that you want to import to to the table array 
$table = array( 
                'table1', 
                'table2', 
                'table3', 
                'table4', 
                'table5', 
        ); 

# if the first row of your csv file contains column headings: 
# $columnheadings=1 
# if the first row does not contain column headings and should be imported: 
# $columnheadings=0 
$columnheadings = 0; 

# contains the email address you want the results sent to 
$emailaddress = "user@domain.com"; 

# contains the subject you want the message to have 
$subject = "Enter Subject Here"; 

# contains the email address that will show in the from line 
$emailfrom = "user@domain.com"; 

# perform the required operations for every table listed in the table array 
foreach ($table as $tablename) { 

    # empty the table of its current records 
    $deleterecords = "TRUNCATE TABLE `$tablename`"; 
    mysql_query($deleterecords); 

    # intialize your counters for successful and failed record imports 
    $pass = 0; 
    $fail = 0; 

    # the csv file needs to be the same name as the table, 
    # comma seperated with the columns in the same order as the table, 
    # and in the same dir as this script 
    $filecontents = file ("$tablename.csv"); # .csv is added to the table name to get the name of the csv file 

    # every record in the csv file will be inserted into the table unless an error occurs with that record 
    for($i=$columnheadings; $i<sizeof($filecontents); $i++) { 
        $insertrecord = "Insert Into `$tablename` Values ($filecontents[$i])"; 
        mysql_query($insertrecord); 
        if(mysql_error()) { 
            $fail += 1;     # increments if there was an error importing the record 
        } 
        else 
        { 
            $pass += 1;        # increments if the record was successfully imported 
        } 
    } 

    # adds a line to the email message we will send stating how many records were imported 
    # and how many records failed for each table 
    $message .= "Table $tablename: Success=$pass  Failure=$fail \n"; 
} 

# set to the date and time the script was run 
$runtime = (date("d M Y H:i")); 

# add the run time to the body of the email message 
$message .= "\nTime of the message: $runtime (server time zone)\n\n"; 

# Send the email message 
mail($emailaddress, $subject, $message, "From: '$emailfrom'"); 

?>

Answer 1

Whats the schema of table, and what you are getting in $message at the end? are you getting all rows as failed?

Added:

As all rows are failing, means there is some problem in insert statement or probably schema mismatch between csv and target table. try this

    # and in the same dir as this script 
    $filecontents = file ("tablename.csv"); # .csv is added to the table name to get the name of the csv file 

    # every record in the csv file will be inserted into the table unless an error occurs with that record 
    for($i=$columnheadings; $i<sizeof($filecontents); $i++) { 
        $insertrecord = "Insert Into `$tablename` Values ($filecontents[$i])"; 
        mysql_query($insertrecord); 
        if(mysql_error()) { 
            $fail += 1;     # increments if there was an error importing the record
             //this is include exact error in email, and will help you to identify the
             //problem very easily.
             $message .= mysql_errno() . ": " . mysql_error() . "\n"; 
        } 
        else 
        { 
            $pass += 1;        # increments if the record was successfully imported 
        } 
    }

try this and let me know with your finding?

Answer 2

Your code does not check for the existance of the file - so, what if its not where you're thinking its looking? Depending on how you run this from cron, it may run from a different directory than if you ran it from the web.