Hi David
Using the code below how can I make the script import the data but when i re run the script only update the ones that have been added or deleted. It currently changes the fgpartsID from starting at say 1 - 80 from 80-160 and so on each time its gets run.
Many thanks
Roy
<?php
require("MagicParser.php");
mysql_connect("localhost","root","");
mysql_select_db("foxs");
function myRecordHandler($fgpart)
{
/*Database Tables is the first item entered and the excel sheet data goes in at the end*/
$sql = "
INSERT INTO fgparts SET
Partno = '".mysql_real_escape_string( $fgpart["PARTNO_XRF"] )."',
PartnoPref = '".mysql_real_escape_string( $fgpart["PARTNO_PREF"] )."',
Description = '".mysql_real_escape_string( $fgpart["DESCRIPTION"] )."',
Shelflife = '".mysql_real_escape_string( $fgpart["SHELFLIFE"] )."',
QtyPerPallet = '".mysql_real_escape_string( $fgpart["PACKPERPAL"] )."' ";
mysql_query($sql);
}
mysql_query("DELETE FROM fgparts");
MagicParser_parse("xref.txt","myRecordHandler","csv|9|1|0");
?>
Hi David
What I am trying to do is automate the import of records which are then maintained in a databse with comments. When I re import for example quantities I want the comments to remain and just update the fields that are in the feed.
Thanks
Roy
Hi Roy,
When you run an import, do you want to delete any records that are no longer in the feed?
What this boils down to is making a decision within your record handler function as to whether you need to INSERT or UPDATE. This means that you have to do a preliminary query to find out whether the current record already exists.
In your example, I think the code would look something like this:
<?php
function myRecordHandler($fgpart)
{
/*Database Tables is the first item entered and the excel sheet data goes in at the end*/
$sql = "
SELECT * FROM fgparts WHERE Partno = '".mysql_real_escape_string( $fgpart["PARTNO_XRF"] )."'";
$result = mysql_query($sql);
if (mysql_num_rows($result)) // if already exists update description, shelflife and quantity
{
$sql = "
UPDATE fgparts SET
Description = '".mysql_real_escape_string( $fgpart["DESCRIPTION"] )."',
Shelflife = '".mysql_real_escape_string( $fgpart["SHELFLIFE"] )."',
QtyPerPallet = '".mysql_real_escape_string( $fgpart["PACKPERPAL"] )."'
WHERE
Partno = '".mysql_real_escape_string( $fgpart["PARTNO_XRF"] )."' ";
}
else // otherwise create a new record
{
$sql = "
INSERT INTO fgparts SET
Partno = '".mysql_real_escape_string( $fgpart["PARTNO_XRF"] )."',
PartnoPref = '".mysql_real_escape_string( $fgpart["PARTNO_PREF"] )."',
Description = '".mysql_real_escape_string( $fgpart["DESCRIPTION"] )."',
Shelflife = '".mysql_real_escape_string( $fgpart["SHELFLIFE"] )."',
QtyPerPallet = '".mysql_real_escape_string( $fgpart["PACKPERPAL"] )."' ";
}
mysql_query($sql);
}
?>
If you also want to delete any records that are no longer in the feed, the easiest way to do this is to create a new field in the database called "DeleteMe", and make it an INT(11). Then, before you start parsing the feed, set DeleteMe to 1 in every record, and make the modifications to the queries used in the record handler function to set DeleteMe to 0 for current records. Then, when you have finished importing; delete every record where DeleteMe is still 1....
<?php
function myRecordHandler($fgpart)
{
/*Database Tables is the first item entered and the excel sheet data goes in at the end*/
$sql = "
SELECT * FROM fgparts WHERE Partno = '".mysql_real_escape_string( $fgpart["PARTNO_XRF"] )."'";
$result = mysql_query($sql);
if (mysql_num_rows($result)) // if already exists update description, shelflife and quantity
{
$sql = "
UPDATE fgparts SET
Description = '".mysql_real_escape_string( $fgpart["DESCRIPTION"] )."',
Shelflife = '".mysql_real_escape_string( $fgpart["SHELFLIFE"] )."',
QtyPerPallet = '".mysql_real_escape_string( $fgpart["PACKPERPAL"] )."',
DeleteMe = '0'
WHERE
Partno = '".mysql_real_escape_string( $fgpart["PARTNO_XRF"] )."' ";
}
else // otherwise create a new record
{
$sql = "
INSERT INTO fgparts SET
Partno = '".mysql_real_escape_string( $fgpart["PARTNO_XRF"] )."',
PartnoPref = '".mysql_real_escape_string( $fgpart["PARTNO_PREF"] )."',
Description = '".mysql_real_escape_string( $fgpart["DESCRIPTION"] )."',
Shelflife = '".mysql_real_escape_string( $fgpart["SHELFLIFE"] )."',
QtyPerPallet = '".mysql_real_escape_string( $fgpart["PACKPERPAL"] )."',
DeleteMe = '0'";
}
mysql_query($sql);
}
// mark all records for deletion
mysql_query("UPDATE fgparts SET DeleteMe = '1'");
// records that are still in the feed have DeleteMe set to 0 by the record handler
MagicParser_parse("xref.txt","myRecordHandler","csv|9|1|0");
// finally delete any record that still has DeleteMe set to 1 as it is no longer in the feed
mysql_query("DELETE FROM fgparts WHERE DeleteMe = '1'");
?>
Hope this helps!
David
Hi Roy,
Your code as it stands should do the job as efficiently as any other mechansim, unless I have mis-understood what you are trying to do.
You are currently DELETE'ing every record from fgparts, and then importing the feed; so on completion the table should have no more records than are in the file; even if the ID field happens to be starting at a higher number. This is because a DELETE FROM does not necessarily reset the autoincrement ID value to 1; however an alternative is to use:
mysql_query("TRUNCATE TABLE fgparts");...which is guaranteed to reset the ID field. If you just make this change the IDs should start from 1 each import.
Cheers,
David.