How to change export to mysql to file in php?

Question

Now i have such method:

 function exportFromTransbase($table_name) {
 $odbc_query = "SELECT * FROM " . $table_name;

 $data = odbc_exec($this->odbc_id, $odbc_query);
 odbc_longreadlen($data, 10485760);

 while($row = odbc_fetch_array($data))
 {

 foreach($row as $key => $value) {
 $keys[] = "`" . $key . "`";
 $values[] = "'" . mysql_real_escape_string($value) . "'";
 }


 $mysql_query = "INSERT INTO `" . strtolower(substr($table_name, 4)) . "` (" . implode(",", $keys) . ") VALUES (" . implode(",", $values) . ")";

 mysql_query($mysql_query);
 set_time_limit(3600);
 unset($keys);
 unset($values);
 unset($row);
 }
    if ($mysql_query){
        print "Ýêñïîðò äàííûõ èç òàáëèöû " . $table_name . " çàâåðøåí!";
        //strtolower(substr($table_name, 4))
    }
 }

But it's very slow when importing to mysql. I decide to change this to export to file .sql, so that in future i can via terminal or phpmyadmin import that table. How to change to export to sql file my data?

Note! i'm converting from transbase to mysql

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

see this post:

Easy way to export a SQL table without access to the server or phpMyADMIN

it uses the select into outfile syntax. The docs for this syntax are here: http://dev.mysql.com/doc/refman/5.1/en/select-into.html

you would do something like this:

mysql_query('SELECT * INTO OUTFILE "/path/to/my_file/my_file.sql" from '.$table_name)

Then this .sql file will be on your server.

If you don't have permissions to run the select into outfile synatx. You can use the mysqldump utility, like this:

<?php
    exec('mysqldump db_name '.$table_name.' > my_file.sql');
?>

This will create an .sql file with the name indicated.

edited May 23 '17 at 12:20

Community

1
1

answered Jul 16 '12 at 13:45

jeffery_the_wind

13,565
31
87
146

1

`OUTFILE` needs some privileges and server configuration (just saying, it is as good tool) – hakre Jul 16 '12 at 13:52
! my mysql db must be empty, forget mysql, php must direct export via odbc to sql, not via mysql query – byCoder Jul 16 '12 at 13:55
In addition to Hakre's comment - most times, if you dont have remote SQL client access to the DB you are not going to have the perms to do `OUTFILE`. – prodigitalson Jul 16 '12 at 13:57
thanks for the updates everyone, I usually admin PHP/MySQL on servers where I am root, so permissions is not an issue. I will see what I can come up with... – jeffery_the_wind Jul 16 '12 at 14:12
you don't unerstand. My mysql must be empty! from ts db directly to file sql! – byCoder Jul 16 '12 at 16:10

score 0 · Accepted Answer · answered Jul 16 '12 at 14:33

Instead of...

while($row = odbc_fetch_array($data))
{
   foreach($row as $key => $value) {
      $keys[] = "`" . $key . "`";
      $values[] = "'" . mysql_real_escape_string($value) . "'";
   }


   $mysql_query = "INSERT INTO `" . strtolower(substr($table_name, 4)) . "` (" . implode(",", $keys) . ") VALUES (" . implode(",", $values) . ")";

    mysql_query($mysql_query);
    set_time_limit(3600);  // this should not be here
    unset($keys);          // this is redundant
    unset($values);        // and this
    unset($row);           // and this too
 }

Try:

$oufile=fopen("export.sql", 'w') || die("error writing file");

while($row = odbc_fetch_array($data))
{
   foreach($row as $key => $value) {
      $keys[] = "`" . $key . "`";
      $values[] = "'" . mysql_real_escape_string($value) . "'";
   }


   $mysql_query = "INSERT INTO `" . strtolower(substr($table_name, 4)) . "` (" . implode(",", $keys) . ") VALUES (" . implode(",", $values) . ")";

   fputs($outfile, $mysql_query . ";\n";        
 }

However it'll be much faster if you....

$oufile=fopen("export.sql", 'w') || die("error writing file");
fputs($outfile, "ALTER TABLE `" . . strtolower(substr($table_name, 4)) . "` DISABLE KEYS;\n";

while($row = odbc_fetch_array($data))
{
   foreach($row as $key => $value) {
      $keys[] = "`" . $key . "`";
      $values[] = "'" . mysql_real_escape_string($value) . "'";
   }

   $head="INSERT DELAYED INTO `" . strtolower(substr($table_name, 4)) . "` (" . implode(",", $keys) . ") VALUES ";
   $row[]="(" . implode(",", $values) . ")";

   if (count($row)>100) {
      flush_ins($outfile, $head, $row);
      $row=array();
   }       
 }
 if (count($row)) flush_ins($outfile, $head, $row);
 fputs($outfile, "ALTER TABLE `" . . strtolower(substr($table_name, 4)) . "` ENABLE KEYS;\n";
 fclose($outfile);
...
function flush($rows, $head, $fh) 
 {
    fputs($fh, $head . implode("\n,", $rows) . ";\n");
 }

set_time_limit(3600); // this should not be here unset($keys); // this is redundant unset($values); // and this unset($row); // and this too when i delete this, it's inert only one row — byCoder, Jul 16 '12 at 16:06
also Warning: fputs() expects parameter 1 to be resource, null given i — byCoder, Jul 16 '12 at 16:09
the reason for the error message should be BLINDINGLY obvious. Yes, you do need to reset the arrays if you keep re-using them. If you want me to write bug free tested, tested code then I'd need to start charging you for it. — symcbean, Jul 16 '12 at 21:40

How to change export to mysql to file in php?

2 Answers2