• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

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";
        if (!$result) {
                echo 'Could not run query: ' . mysql_error();

Open in new window

  • 2
1 Solution
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...

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.
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';
//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]);
// build a list string to use in the OUTFILE querry
$qryfields = '';
foreach($fields as $field){
    if($i != 0 ) $qryfields .= ",";    
    $qryfields .= $field;
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now