[PHP] Creating INSERT INTO statement from dbf file
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: "; } } 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
Re: [PHP] Creating INSERT INTO statement from dbf file
You can either put a flag indicating your first time around the field loop and add the comma before each field, when it is not the first time around, or build the fieldname and fieldvalue list on a string and then trim out the last character once the loop is finished. "Rahul S. Johari" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] 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: "; } } 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 (Ov1¹,¹v2¹,¹v3¹); I achieve this : INSERT INTO tblname (c1,c2,c3,) VALUES (Ov1¹,¹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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Creating INSERT INTO statement from dbf file
On Mon, April 4, 2005 1:33 pm, Rahul S. Johari said:
> 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.
If having a flag to determine when to add the , or ' feels too kludgy, you
could also do something like:
$values = array();
while ($value...){
$values[] = $value;
}
$values_sql = implode(", ", $values);
It's a bit more memory-intensive than just chopping up a string, but I
find it more understandable, personally. Others will differ, of course.
--
Like Music?
http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Creating INSERT INTO statement from dbf file
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> $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 "'); ";
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: [EMAIL PROTECTED]
RSJ> http://www.informed-sources.com
I would do it this way:
";
}
dbase_close($dbh);
}
?>
--
regards,
Tom
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Creating INSERT INTO statement from dbf file - SOLVED!
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:
";
}
}
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:
>
> $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 "'); ";
>
> }
> }
> 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
>
