Posted by Tom Rogers on 04/12/05 01:39

Hi,

Tuesday, April 5, 2005, 6:33:31 AM, you wrote:

RSJ> Ave,

RSJ> I¹ve written a code that is able to extract the Column names and Records
RSJ> from a simple dbf (foxpro) file and create an INSERT INTO sql statement
RSJ> which can be used to insert all those records with their corresponding field
RSJ> names in an existing mySQL table. (A CREATE TABLE code I wrote is able to
RSJ> create the table from the dbf file information).

RSJ> Following is the code I wrote for creating the INSERT INTO sql:

RSJ> <?php
RSJ> $db_path = "$DATABASEFILE";
RSJ> $dbh = dbase_open($db_path, 0) or die("Error! Could not open
RSJ> dbase database file '$db_path'.");
RSJ> if ($dbh) {

RSJ> #Get the Information
RSJ> $column_info = dbase_get_header_info($dbh);
RSJ> $record_numbers = dbase_numrecords($dbh);

RSJ> #Run the loop for all the records in the Table
RSJ> for ($i = 1; $i <= $record_numbers; $i++) {
RSJ> $row = dbase_get_record_with_names($dbh, $i);

RSJ> echo "INSERT INTO
RSJ> ".substr($DATABASEFILE_name,0,-4)." (";

RSJ> #Run the loop for all the fields in the Table
RSJ> foreach ($column_info as $v1) { echo "$v1[name],"; }

RSJ> echo ") VALUES (";

RSJ> #Run the loop for all the values corresponding to fields in the
RSJ> Table
RSJ> foreach ($column_info as $v1) { echo
RSJ> "'".trim($row[$v1[name]])."',"; }

RSJ> echo "'); <br>";

RSJ> }
RSJ> }
RSJ> dbase_close($dbh);
?>>

RSJ> It works fine, except for one problem. It¹s able to create the INSERT INTO
RSJ> sql statement, with all the fields and corresponding values, but as I¹m
RSJ> running a loop for both the fields names, and the values corresponding to
RSJ> fields names, it leaves a comma after the records are over.

RSJ> So instead of having this : INSERT INTO tblname (c1,c2,c3) VALUES
RSJ> (Œv1¹,¹v2¹,¹v3¹);
RSJ> I achieve this : INSERT INTO tblname (c1,c2,c3,) VALUES (Œv1¹,¹v2¹,¹v3¹,¹);

RSJ> Notice an additional Comma after column names, and an additional ,¹ after
RSJ> the values. I¹m not quite sure what to do to get rid of those. I¹ve tried
RSJ> some different combinations using different kind of logic with the echo
RSJ> statements, but it¹s not working out. Would love some help.

RSJ> Thanks,

RSJ> Rahul S. Johari
RSJ> Coordinator, Internet & Administration
RSJ> Informed Marketing Services Inc.
RSJ> 251 River Street
RSJ> Troy, NY 12180

RSJ> Tel: (518) 266-0909 x154
RSJ> Fax: (518) 266-0909
RSJ> Email: rahul@informed-sources.com
RSJ> http://www.informed-sources.com

I would do it this way:

<?php
$db_path = "$DATABASEFILE";
$dbh = dbase_open($db_path, 0)
or die("Error! Could not open dbase database file '$db_path'.");
if ($dbh) {
#Get the Information
$column_info = dbase_get_header_info($dbh);
$record_numbers = dbase_numrecords($dbh);
$table = substr($DATABASEFILE_name,0,-4);
$fields = ''; //we will fill this on the first pass
$first = true; //flag to generate field names
#Run the loop for all the records in the Table
for ($i = 1; $i <= $record_numbers; $i++) {
$values = ''; //start off null values
$row = dbase_get_record_with_names($dbh, $i);
#Run the loop for all the fields in the Table
foreach ($column_info as $v1) {
$field = $v1['name'];
if($first) $fields .= (empty($fields))? $field : ",$field";
$val = mysql_escape_string(trim($row[$field]));
$values .= (empty($values))? "'$val'" : ",'$val'";
}
$first = false; // we have a field list now
echo "INSERT INTO $table ($fields) VALUES ($values) <br>";
}
dbase_close($dbh);
}
?>

--
regards,
Tom

[Back to original message]