mySql : Select into file

[fomalhaut]

Hello

I want to make a copy of a table into a file in order to make a backup.

$db  = db_name;
$dbh = new PDO("mysql:host=localhost;dbname=$db", 'user', 'pw');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$TB = db_name.table_name;
$backupFile = '../Backup/' . $TB . '.sql';
$query = "SELECT * FROM ". $TB . " INTO OUTFILE '".$backupFile."' ";
$result = mysql_query($query) or die ("pb sur le ".$query);

and that does not work ! It says :

pb sur le SELECT * FROM db_name.table_name INTO OUTFILE '../Backup/db_name.table_name.sql'

I don't understand what happens ! And the file is not created.

Thank you for help

The directory ../Backup exists before.

 

[as4s1n]

$TB = db_name.table_name;
$backupFile = '../Backup/' . $TB . '.sql';
$query = "SELECT * FROM ". $TB . " INTO OUTFILE '".$backupFile."' ";
$result = mysql_query($query) or die ("pb sur le ".$query);

The directory ../Backup exists before.

It may be out of order:

$string = '';
$tableName  = 'tableName';
$backupFile = 'backupFile';
try
    $query =$dbh->query("SELECT * INTO OUTFILE '$backupFile' FROM $tableName");
catch(PDOException $e) 
    print("Error: ".$e->getMessage()."\n");

Also try reading Using PHP to Backup MySQL Databases. Although, I am not sure of the format in which the information is shown on the page.

Or you could use the fwrite() function to create a new file if it does not exist or empty and write to it if it does exist.

$errors = array();
$tableName  = 'tableName';
$backupFile = 'backupFile';
$query =$dbh->query("SELECT * FROM tableName");
if($query->columncount() === 0)
   $errors[] = "No rows in table";
if(!$query)
    $errors[] = "Query Error: ".mysql_error();
if(sizeof($errors) == 0) {
  while($row=$query->fetch()) {
   $column1 = $row['0'];
   $column2 = $row['1'];
   $column3 = $row['2'];

// Etc.

// Format any way you wish
$string .= "$column1 $column2 $column3"; // Etc.
}

$fp = fopen("$backupFile","w"); // the W parameter truncates the file, use A if you want to apply it to the end of the file which DOES NOT truncate the file.
fwrite($fp,$string);
fclose($fp);
} else {
 echo "<ul><li>";
  implode("</li><li>",$errors);
 echo $errors."</li>";
echo "</ul>";
}

Also if you use PDO, instead of the original:

$query = "SELECT fields FROM tableName";
$result = mysql_query($query);

You can use the PDO version:

$query = $dbh->prepare("SELECT fields FROM tableName");
$query->execute();

Refer to Writing PDO in PHP & MySQL

 

[zenadix]

Your error message is in french. Why?

 

[lemon-tree]

Dumping of SQL databases is not available on X10 hosting judging from the tests I have tried. I would suggest PHPMyBackupPro as a better alternative that does essentially the same thing.

 

[as4s1n]

Dumping really wasn't my intent, that was just a bonus for formalhaut.

@formalhaut: BTW is this like a scheduled backup thing or just an occasional backup you want just in case?

 

[fomalhaut]

Hello.

Your error message is in french. Why?

Of course, there are, I'm french !

BTW is this like a scheduled backup thing or just an occasional backup you want just in case?

You're right, that's just an occasional backup, because, the tables will not move a lot.
Please, what the exact meaning of "BTW" ?

In facts:
Having changed some things, I've now a more precise message:

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1086 File '../Backup/db_name.table_name.sql' already exists'

And alas, I don't find it in any directory !!!!

I'm searching !

 

[as4s1n]

'BTW' means 'By The Way'.

If it says it already exists, I'm not quite sure what to tell you, you looked through the folder? You could always try the fwrite method I suggested, which writes to a file whether or not it exists, which would probably mean the server thinks it has that but doesn't. I have noticed before that the cPanel file manager does not like certain names, although that may be just me. try 'db_tableName.sql', It's worth a shot.

One more question: Are you going to input the file directly into the database (import) or copy the data in it to the table?

 

[descalzo]

And alas, I don't find it in any directory !!!!

I'm searching !

Try using absolute paths. Not sure if the mySQL server treats '../Backup/db_name.table_name.sql' as relative to the server rather than to the script.

 

[fomalhaut]

OK, I've found !! Select Into file writes in a directory relative to the mysql directory. And that doesn't arrange me because this is not the same place on my test server than on the x10 server.

So I've tried your "fwrite method", as4s1n. And it works, putting the file where I expect it.

although... I've an other pb that I'm searching for: each column is written twice in my file !

I've no time now, but if I don't found, I will explain you. Probably an idioty of my small mind !

Thanks again for having help me.

 

[as4s1n]

Is the duplicate columns result from the fwrite method or the OUTPUT clause in the query?

 

[fomalhaut]

Is the duplicate columns result from the fwrite method or the OUTPUT clause in the query?

I've suppressed the Output term. I only use fwrite :

if (isset($_GET['save']) && isset($saveTB[$_GET['save']])) {
  $saveCode = $_GET['save'];
  $errors = array();
  $lgn = '';
  $TB = $saveTB[$_GET['save']];
  $backupFile = '../Backup/' . $TB . '.sql';
  $how = "SELECT count(*) FROM ". $TB;
  $sql = "SELECT * FROM ". $TB;
  if ($res = $dbh->query($how)) {
    if ($res->fetchColumn() > 0) {
      $result = $dbh->query($sql);
      foreach($result as $row) {
        $str = implode("|",$row)."\n";
        $lgn .= $str;
      }
      $fp = fopen("$backupFile","w");
      fwrite($fp,$lgn);
      fclose($fp);
    }
    else {
      $errors[] = "table ".$TB." vide";
    }
  }
  else {
    echo "<ul><li>";
      implode("</li><li>",$errors);
    echo $errors."</li>";
    echo "</ul>";
  }
}
else {$TB = null;}

 

[fomalhaut]

OK, I've found. I do that:

if (isset($_GET['save']) && isset($saveTB[$_GET['save']])) {
  $saveCode = $_GET['save'];
  $errors = array();
  $lgn = '';
  $TB = $saveTB[$_GET['save']];
  $backupFile = '../Backup/' . $TB . '.sql';
  $how = "SELECT count(*) FROM ". $TB;
  $sql = "SELECT * FROM ". $TB;
  if ($res = $dbh->query($how)) {
    if ($res->fetchColumn() > 0) {
      $result = $dbh->query($sql);
      foreach($result as $row) {
        for ($c=0; $c<=count($row); $c++) {
          $lgn .= $row[$c];
          $lgn .= "|";
        }
        $lgn = rtrim($lgn,"|"); 
        $lgn .= "\n";
      }
      $fp = fopen("$backupFile","w");
      fwrite($fp,$lgn);
      fclose($fp);
    }
    else {
      $errors[] = "table ".$TB." vide";
    }
  }
  else {
    echo "<ul><li>";
      implode("</li><li>",$errors);
    echo $errors."</li>";
    echo "</ul>";
  }
}
else {$TB = null;}

Now, it works !!! mySql seems to serv the column twice, non depending on the "null" or "not null" condition ! The rtrim function resolves my problem.

Thank you again.

 

[misson]

There are two copies of each column because the default fetch mode for PDOStatement is to return an array indexed both by column number and name (mode PDO::FETCH_BOTH). Call PDOStatement::setFetchMode() to set the fetch mode to PDO::FETCH_NUMERIC, PDO::FETCH_NAMED or PDO::FETCH_ASSOC. More info on the fetch modes is available on the PDO constants doc page.

 

[fomalhaut]

Hello, Misson, I've used setFetchMode(PDO::FETCH_NUM) and it works fine, as I wanted, with a code easier to read.

Thanks again.

 

[misson]

You'll probably get better performance and reduce resource usage if you write directly to the file, rather than appending to a string as you iterate over result rows. There will be much less allocation and reallocation of memory.

      $result = $dbh->query($sql);
      $result->setFetchMode(PDO::FETCH_NUM);
      $fp = fopen("$backupFile","w");
      foreach($result as $row) {
        // urlencode values to escape any '|'
        fwrite($fp, implode("|",array_map('urlencode', $row))."\n");
      }
      fclose($fp);

 

[fomalhaut]

OK, thanks again Misson, I've done the modification. It works well.

Thanks for all.

 

[hernandez9039]

Thanks for your informative post.It is really helpful.
____________________________
Boob Job
Hair Straightener