PHP – Poor Man’s Data Export/Combine Script

Programming,Snippets — Jeff Eske on February 5, 2013 at 4:50 pm

I ran into a situation a while back where I needed to merge some information from 2 separate tables into one table.   Basically, I was sort of breaking normalization rules in favor of simplicity.  I basically had a table full of helpdesk tickets with a field for customer numbers, but no customer names.  I also had a customer table that contained all of the customer information.  Since I was making a system that’s going to be “read-only” for looking up the old tickets, I wanted to be able to query a single database, when looking for the old tickets.

So, basically, I cobbled the basics of this script together to pull information from the two different tables and combine it into one single record.  I pulled the ticket record, then did a sub-search on the customer table to find the customer name in the customer table associated with the customer number from the ticket table.

Then, once that was done, I cleared the ticket table and turned around and imported my brand-spanking new csv file BACK into the ticket table, with the customer number field adjusted to be the customer name.

There’s probably an easier way to accomplish this, but when all you have is a hammer (PHP programming experience), everything looks like a nail.

Here’s the basic code.  It’s a little confusing since I’m just using generic field1, field2, etc.

[code]

<?php
#### Variables to Set
$db_host = “localhost”; // Database server name
$db_user = “db_user_username_here”; // Database Username
$db_pass = “db_user_password_here”; // Database User’s Password
$db_table1 = “your_first_table_here”; // Table name, written as database.table
$db_table1 = “your_second_table_here”; // Table name, written as database.table
$csv_filename = “csv_filename_here”; // name of the output file

#### Database Connection
$db_connect = mysql_connect($db_host, $db_user, $db_pass) or die(“couldn’t connect to DB server”);

#### Open the csv text file for writing
$csv_file_out = fopen($csv_filename,”w”); // name of the output file

#### Query the first database
$sql1 = “SELECT field1,field2,field3 FROM $db_table1”;
$result1 = mysql_query($sql1, $db_connect);

#### Pull each record from the first database then do a subquery for the related record from the second database.
while($data1 = mysql_fetch_object($result1)){ // cycle through first database’s records
// Find the record in database2 that is related to the record in database1 via field1 in both
$sql2 = “SELECT field1 FROM $db_table2 WHERE field1 = $data1->field1″;
$result2 = mysql_query($sql2, $db_connect);
// Write out field1, field2, and field3 from database1, plus field1 from database2
$write_line = $data1->field1.”,”.$data1->field2.”,”.$data1->field3.”,”.$data2->field1.”\n”;
fwrite($csv_file_out, $write_line);
}
#### Close the text file when finished
fclose($csv_file_out;
?>

[/code]

Jeff Eske

0 Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

You must be logged in to post a comment.

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.5 License. | Jeff's Blog