Re: Creating INSERT INTO statement from dbf file - SOLVED!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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: rahul@xxxxxxxxxxxxxxxxxxxx
http://www.informed-sources.com


On 4/4/05 4:33 PM, "Rahul S. Johari" <rjohari@xxxxxxxxxxxx> 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: rahul@xxxxxxxxxxxxxxxxxxxx
> http://www.informed-sources.com
> 


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux