exporting data to text mysql/php

I was using the code below to export data to text and import it on another server. The problem is "Select * into outfile* exports the data in a different order on different servers. so if I export some data on one server and import it to a database on another some data is put in the wrong fields. The tables have the same structure. The text file is a tab delimited plain text file but there is no field info in it, just raw data.

How should I be doing this?

- I can't dump the whole table... I need to be able to dump all data fields base on a query
- must be done within php. no shell commands because one server is Linux, one is windows
- I would like to have something that will work without being rewritten if the data table changes... So I would like it to work as long as both servers have the same table structure.
- It doen't matter what format the exported data is in as long as it works between servers.

The application is a data entry application.  The server is run on a LAN for speed during data entry. Jobs are sent to data entry folks. they create the tables, export the jobs one at a time (a job is a BATCHNUMBER in the code) then email their data dumps to me. I import it into the same application running on a different machine here

 
foreach($tables as $table){
        $file = $batch . "_". $table . "_export.sql";
        if ($action == "export"){
                echo "<h1>Exporting Entered $table data for batch $batch</h1>\n";
                $qry = "SELECT * INTO OUTFILE \"${datadir}/export/${datasubdir}/${file}\" FROM $table where BATCHNUMBER=\"$batch\"";
        } elseif ($action == "import") {
                echo "<h1>Importing Entered $table data for batch $batch</h1>\n";
                $qry = "LOAD DATA INFILE  \"${datadir}/import/${datasubdir}/${file}\" INTO TABLE $table";
        }
        echo "$qry <br />\n";
        $result=$db->query($qry);
        if (!$result) {
                echo 'Could not run query: ' . mysql_error();
                exit;
        }
 
        $numresults=$result->num_rows;
}

Open in new window

blotsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

blotsAuthor Commented:
I've tried a few things here. I thought I would can my requirement of doing this in php only and use mysqldump... and just deal with the differences between windows and linux. well that won't work as far as I know.

mysqldump by default recreates the table. I can't do that without losing the other records. I put togeather command options that will not recreate the table:

mysqldump  -uroot -pxxxxxx --skip-opt --no-create-info --lock-tables --quick "--where=BATCHNUMBER='20090112chisago'" crevdata entryx entryy entrycomp  > testtom3.sql

but I'm back to the same problem with the order of the files. since the table is not recreated in a particular order the INSERT queries that mysqldump outputs are in the wrong order. the fields are not spelled out in the INSERT command...
example:
INSERT INTO `entryx` VALUES ('20090112CHISAGO',1,'0','30420',NULL,'WALLMARK LAKE DR',NULL,NULL,'CHISAGO CITY','MN','55013','CHISAGO',NULL,NULL,'30420 WALLMARK LAKE DR',NULL,'CHISAGO CITY','MN','55013','7255 BAYMEADOWS WAY',NULL,'JACKSONVILLE','FL','32256','200809804','2008-12-29','2008-12-17',NULL,'174900.00',NULL,NULL,NULL,NULL,2,1,NULL,1,0,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,'LISA','LEWIS','2008-12-17',NULL,'W',NULL,'MATTHEW','L','WEHRENBERG',NULL,NULL,'HEATHER','R','WEHRENBERG',NULL,NULL,NULL,NULL,'MORTGAGE ELECTRONIC REGISTRATION SYSTEMS, INC',NULL,NULL,NULL,NULL,NULL,NULL,'651','260','5939',NULL,NULL,NULL,'020179037',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,'004589',NULL,'TOMP','2009-03-24 10:13:48',NULL,'20090112CHISAGO_0.PNG','7202','651','257');

So I'm left with two options that I can see.
please suggest others if you can.

1. write a utility to reorder the table fields on the server. AFAIK to do this I need to copy all records to a different table, drop the original, recreate the fields in the order I like then copy the data back. This again requires me to define each of the ever changing 150 fields in the table.

2. query the fields with a SHOW COLUMNS query, sort them with php, build a query out of those.

I like #2. I'll let you know if it works. maybe I can earn my own 500points back.
0
blotsAuthor Commented:
This is how I solved this problem:

I queried a list of fields in the table. Put the fields names in a php array and sorted it. then built my ordered field list from that.

I would have like to do this in an sql query but couldn't, apparently you can not use ORDER BY in a SHOW query. so I had to sort the fields with php.


// querry for list of field in table
$colqry = 'SHOW COLUMNS FROM entrycomp';
$colresult=$db->query($colqry);
 
//Put only the fields noames in an array and sort it
$num = $colresult->num_rows;
$fields = array();
echo "rows $num<br/>";
for($i; $i<$num; $i++ ){
    $row= $colresult->fetch_array();
    array_push($fields, $row[0]);
}
sort($fields);
 
// build a list string to use in the OUTFILE querry
$i=0;
$qryfields = '';
foreach($fields as $field){
    if($i != 0 ) $qryfields .= ",";    
    $qryfields .= $field;
    $i++;
}
echo $qryfields . "<br/><br/>";
 
// now the fields will be exported in a constant order between the servers
// as long as they have the same fields, the order of the fields in the database
// does not matter
 
echo "<h1>Exporting Entered $table data for batch $batch</h1>\n";
 $qry = "SELECT $qryfields INTO OUTFILE \"${datadir}/export/${datasubdir}/${file}\" FROM $table where BATCHNUMBER=\"$batch\"";

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.