Php Programming Code Examples

Import CSV files into MySQL <?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 = "[email protected]"; # 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 = "[email protected]"; # you should not have to edit anything below this line # 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'"); ?>