Import a list of CSV files into their appropriate tables and email the results when finished.

By : Keegan

<?php
# Author: Keegan
# Email: keegan@sifizm.com
# Web Site: www.sifizm.com

# 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";

# 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'");

?>

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More Database Code Articles
More By Codewalkers

blog comments powered by Disqus