Forum Moderators: coopster
The site I am working on needs to be updated everyday. I receive a nightly ftp feed containing 1 txt file.
I found that setting a cron job is the most suitable way to update my database and I wrote a small script to achieve this. I realize the script is very rudimentary but I am an absolute beginner in web development. Please note that I am not asking you to write the code for me, just to point me in the right direction and help me see potential problems with the process i might be overlooking due to my limited experience.
The process I am using is the following:
Connect to MySql and select database
$link = @mysql_connect('localhost' , 'user' , 'password') or die ('no se puede conectar a mysql');
mysql_select_db ('db') or die ('no existe la bd');
Then I load the contents of the .txt file into a table named ORIGINAL I created for this purpose only (this is not the table the site is accessing)
$fcontents = file ('./text.txt');
for($i=0; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode("¦", $line);
$sql = "insert into original values ('".
implode("','", $arr) ."')";
mysql_query($sql);
}
The script then creates a table named TRANS and inserts only the fields and records I am interested in. The original file contains more than 50k records but once I filter them, my table has only about 2500.
$sql2= "CREATE TABLE trans SELECT ship_cd , depart , cabins, itinerary_cd, itinerary_desc, category_cd, fare_amt, cruise_id, cruise_days, promotion FROM original WHERE aircity = 'C/O'";
mysql_query($sql2);
The next step is to delete the contents of the table ORIGINAL so the table is empty for the next time the script is run
$sql3 = "DELETE FROM original";
mysql_query($sql3);
Now here comes the tricky part. So far, I have not touched the table that is accessed by users. This table is called "live". Now that I have filtered the records and have them ready in another table (this is the process that takes the longest) I DELETE all records from my table "live" and then copy the new ones. I thought this was the best way to do it because records from the ftp feed i receive can be updtaed or deleted and some others might be added.
$sql7 = "DELETE FROM live";
mysql_query($sql7);$sql4 = "INSERT INTO live (ship_cd, cabins, itinerary_cd, itinerary_desc, category_cd, fare_amt, cruise_id, cruise_days, promotion) SELECT ship_cd, cabins, itinerary_cd, itinerary_desc, category_cd, fare_amt, cruise_id, cruise_days, promotion FROM trans";
mysql_query($sql4);
/*in the previous query I left one field out because I need to further modify its contents. this piece of code extracts a field containing dates, transform them into timestamps and then insetrs them into table "live"*/
$sql5 = "SELECT depart, cruise_id FROM trans";
$result = mysql_query($sql5);
while ($renglones = mysql_fetch_assoc($result)) {
$fechaUNIX = strtotime ($renglones['depart']);
$sql6 = "UPDATE live SET depart = '".$fechaUNIX."' WHERE cruise_id='".$renglones['cruise_id']."'";
mysql_query($sql6);
//the last step is to drop the table I am no longer using
$sql7 = "DROP TABLE trans";
mysql_query($sql7);
My main concearn is to keep the integrity of the table "live". For what I have seen, the php script runs from begining to end even if something fails in the queries or if the queries have not been completed. Is that true? The behaviour I wanted from this script was the table live not to be touched until the new data is ready to be inserted, hence reducing the time the live table has no records.
The script will run when i expect the traffic to the site to be at its minimum bu I do not know if the process will be affected if a query from the site is performed. Now, if a user happens to perform a search at say, 4 AM while the database is getting updated and gets an error message, I can live with that. But if the table's integrity gets compromised that is going to be a problem.
Please let me know if I am awfully wrong and I need to write this script again from ground up.
For what I have seen, the php script runs from begining to end even if something fails in the queries or if the queries have not been completed. Is that true?
The script will run through the queries sequentially, waiting for each query to complete. If you think there will be errors, you should really check for these and take appropriate action.
Usually with a database you would wrap a series of queries into a transaction. That way the changes are not "visible" until all queries have been completed and have been committed. So even if you delete the "live" table, the old version will still be visible right up until the new version is available.
Unfortunately MySQL's transaction support is substandard. Theoretically it should work, but it's sort of an optional extra bolted on, and especially in shared hosting enviroments it's often not available, or only as an older (= buggier) version.
Google for "MySQL" and "InnoDB" for more information; the MySQL support site at dev.mysql.com is pretty comprehensive too.
