Ave,
I¹m sure there would be better ways of doing this, and mine may not be the
most decent, but I did manage to solve this.
Here¹s how I did it:
- Create An Array
- Run the loop for all the records in the table
- Fill the array with the Field Names
- Display the Fields names with the ³Comma² being inserted on an ³If²
condition pertaining to the count() for the Array. So we place the ³Comma²
only ³If² there is a Value remaining in the Array.
- Reinitialize (empty) the Array upon restart of the loop (So that the next
set of values can go in)
I did this once for the Field Names and then again for the Values, and it
works perfect! The commas & apostrophes are just the way they should be, not
excessive. The entire INSERT INTO SQL statement is generated from the data
in the DBF file.
Here¹s the code:
<?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);
$fArray = array();
$vArray = array();
#Run the loop for all the records in the Table
for ($i = 1; $i <= $record_numbers; $i++) {
$row = dbase_get_record_with_names($dbh, $i);
$fArray = array();
$vArray = array();
echo "INSERT INTO ".substr($DATABASEFILE_name,0,-4)." (";
#Run the loop for all the fields & Assign Field Names to an
Array
foreach ($column_info as $v1) { $fArray[]=$v1[name]; };
#Run the loop for all values in the Array & Display Field Names
$c=count($fArray);
for($j=0;$j<=$c;$j++) { echo $fArray[$j]; if($j<$c-1) { echo
","; } }
echo ") VALUES (";
#Run the loop for all the values corresponding to fields in the
Table
foreach ($column_info as $v1) {
$vArray[]=trim($row[$v1[name]]); }
#Run the loop for all values in the Array & Display Values
$c2=count($vArray);
for($j2=0;$j2<=$c2;$j2++) {
if($j2<$c2) { echo "'"; }
echo "$vArray[$j2]";
if($j2<$c2) { echo "'"; }
if($j2<$c2-1) { echo ", "; }
}
echo "); <br>";
}
}
dbase_close($dbh);
?>
Rahul S. Johari
Coordinator, Internet & Administration
Informed Marketing Services Inc.
251 River Street
Troy, NY 12180
Tel: (518) 266-0909 x154
Fax: (518) 266-0909
Email: [EMAIL PROTECTED]
http://www.informed-sources.com
On 4/4/05 4:33 PM, "Rahul S. Johari" <[EMAIL PROTECTED]> wrote:
>
> Ave,
>
> I¹ve written a code that is able to extract the Column names and Records
> from a simple dbf (foxpro) file and create an INSERT INTO sql statement
> which can be used to insert all those records with their corresponding field
> names in an existing mySQL table. (A CREATE TABLE code I wrote is able to
> create the table from the dbf file information).
>
> Following is the code I wrote for creating the INSERT INTO sql:
>
> <?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);
>
> #Run the loop for all the records in the Table
> for ($i = 1; $i <= $record_numbers; $i++) {
> $row = dbase_get_record_with_names($dbh, $i);
>
> echo "INSERT INTO ".substr($DATABASEFILE_name,0,-4)." (";
>
> #Run the loop for all the fields in the Table
> foreach ($column_info as $v1) { echo "$v1[name],"; }
>
> echo ") VALUES (";
>
> #Run the loop for all the values corresponding to fields in the
> Table
> foreach ($column_info as $v1) { echo
> "'".trim($row[$v1[name]])."',"; }
>
> echo "'); <br>";
>
> }
> }
> dbase_close($dbh);
> ?>
>
> It works fine, except for one problem. It¹s able to create the INSERT INTO
> sql statement, with all the fields and corresponding values, but as I¹m
> running a loop for both the fields names, and the values corresponding to
> fields names, it leaves a comma after the records are over.
>
> So instead of having this : INSERT INTO tblname (c1,c2,c3) VALUES
> (Œv1¹,¹v2¹,¹v3¹);
> I achieve this : INSERT INTO tblname (c1,c2,c3,) VALUES (Œv1¹,¹v2¹,¹v3¹,¹);
>
> Notice an additional Comma after column names, and an additional ,¹ after
> the values. I¹m not quite sure what to do to get rid of those. I¹ve tried
> some different combinations using different kind of logic with the echo
> statements, but it¹s not working out. Would love some help.
>
> Thanks,
>
> Rahul S. Johari
> Coordinator, Internet & Administration
> Informed Marketing Services Inc.
> 251 River Street
> Troy, NY 12180
>
> Tel: (518) 266-0909 x154
> Fax: (518) 266-0909
> Email: [EMAIL PROTECTED]
> http://www.informed-sources.com
>
