Here in part 2 we focus on reading from the DBF file (addresses.dbf) and then inserting the data into the MySQL table (in this case a table called "addresses") created from the CREATE TABLE statement generated in part 1.
Before continuing note that you will need the DBase extensions compiled/loaded. In Windows you simply uncomment out the line:
;extension=php_dbase.dll
In Linux and other *nix you will have to compile PHP with the "--enable-dbase" option.
The code will look for matching field names as it loops through the entire file and generate & execute INSERT statements in MySQL. Note that I start with record 1 as record 0 doesn't seem to contain actual data when using the dbase_get_record_with_names() function. Here it goes:
<?php
//by David @ PHP4IT.com 1/20/2006
//support for importing multiple DBF files at a time
//the key is the location of the DBF file & the value is the table to import
$dbf_files = array('/home/dbfs/addresses.dbf' => 'addresses');
$db_uname = '';
$db_passwd = '';
$db = 'test';
$conn = mysql_p_connect($db_uname, $db_passwd, $db);
foreach ($dbf_files as $key => $val){
import_dbf2($db, $val, $key);
}
function import_dbf2($db, $table, $dbf_file)
{
global $conn;
if (!$dbf = dbase_open ($dbf_file, 0)){ die("Could not open $dbf_file for import."); }
$num_rec = dbase_numrecords($dbf);
$num_fields = dbase_numfields($dbf);
$fields = array();
for ($i=1; $i<=$num_rec; $i++){
$row = @dbase_get_record_with_names($dbf,$i);
$q = "insert into $db.$table values (";
foreach ($row as $key => $val){
if ($key == 'deleted'){ continue; }
$q .= "'" . addslashes($val) . "',";
}
if (isset($extra_col_val)){ $q .= "'$extra_col_val',"; }
$q = substr($q, 0, -1);
$q .= ')';
//if the query failed - go ahead and print a bunch of debug info
if (!$result = mysql_query($q, $conn)){
print (mysql_error() . " SQL: $q<br>\n");
print (substr_count($q, ',') + 1) . " Fields total.<p>";
$problem_q = explode(',', $q);
$q1 = "desc $db.$table";
$result1 = mysql_query($q1, $conn);
$columns = array();
$i = 1;
while ($row1 = mysql_fetch_assoc($result1)){
$columns[$i] = $row1['Field'];
$i++;
}
$i = 1;
foreach ($problem_q as $pq){
print "$i column: {$columns[$i]} data: $pq<br>\n";
$i++;
}
die();
}
show_status($i, $num_rec);
}
}
//this function outputs the current progress of the import
function show_status($i, $max, $precise_mode=FALSE){
if ($i == floor($max/4)){ print "...25%"; flush(); }
if ($i == floor($max/2)){ print "...50%"; flush(); }
if ($i == floor($max*0.75)) { print "...75%"; flush(); }
if ($i >= $max) { print "...100%<br>\n"; flush(); return; }
if ($precise_mode){
print '...' . (round($i/$max * 100)) . "%";
flush();
}
}
?>
