Restore table backup in sql and php

Question

Restore table backup in sql and php

Navigation

#1 by (1 votes)

1

<?php
   $dbhost = 'XXXXXXX';
   $dbuser = 'XXXXXXX';
   $dbpass = 'XXXXXXX';

   $conn = mysqli_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $table_name = "chofer";
   $backup_file  = "chofer.sql";
   $sql = "LOAD DATA INFILE '$backup_file' INTO TABLE $table_name";

   mysqli_select_db($conn,'test_db');
   $retval = mysqli_query($conn, $sql);

   if(! $retval ) {
      die('No se puede acceder al registro : ' . mysql_error());
   }

  echo "<script>";
  echo "alert ('Restauración exitosa.');";
  echo "window.location.replace('index.php');";
  echo "</script>";

   mysqli_close($conn);
?>

With this script you must restore the driver table with the information of the file driver.sql, but mark this error "You can not access the registry" and it does not restore the table, now the idea is to ask what file you want restore and likewise restore all the tables contained in the .sql file, I hope you can help me with this dilemma, thank you very much.

This is the file with which I backup the tables

<?php

backup_tables('XXXXXXX','XXXXXXX','XXXXXXX','XXXXXXX');

function backup_tables($host,$user,$pass,$name,$tables = '*')
{

   $link = mysqli_connect($host,$user,$pass);
   mysqli_select_db($link,$name);

   //get all of the tables
   if($tables == '*')
   {
      $tables = array();
      $result = mysqli_query($link,'SHOW TABLES');
      while($row = mysqli_fetch_row($result))
      {
         $tables[] = $row[0];
      }
   }
   else
   {
      $tables = is_array($tables) ? $tables : explode(',',$tables);
   }

   //cycle through
   foreach($tables as $table)
   {
      $result = mysqli_query($link,'SELECT * FROM '.$table);
      $num_fields = mysqli_num_fields($result);

      $return.= 'DROP TABLE '.$table.';';
      $row2 = mysqli_fetch_row(mysqli_query($link,'SHOW CREATE TABLE '.$table));
      $return.= "\n\n".$row2[1].";\n\n";

    for ($i = 0; $i < $num_fields; $i++)
      {
         while($row = mysqli_fetch_row($result))
         {
            $return.= 'INSERT INTO '.$table.' VALUES(';
            for($j=0; $j<$num_fields; $j++) 
            {
               $row[$j] = addslashes($row[$j]);
               $row[$j] = str_replace("\n","\n",$row[$j]);
               if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
               if ($j<($num_fields-1)) { $return.= ','; }
            }
            $return.= ");\n";
         }
      }
      $return.="\n\n\n";
   }

   //save file
   $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
   fwrite($handle,$return);
   fclose($handle);
}
?>

sql php mysqli

asked by Stravos77 29.08.2016 в 21:46

source

1 answer

collapse submenu when selecting another option from the menu can not import name commit_on_success, Mockups in django 1.9

score 1 · Accepted Answer

You are looking for a SQL script from php.

Before the solution code, it is my duty to recommend not allow a user to run arbitrary SQL on your server, because that opens the door to a Moderately capable user can destroy the data and its structures.

That said, and after having seen the routine that generates the backup , what you have is an SQL script that contains several sentences (one to delete the table, another to create it again, and multiple insert of rows of data).

Use the mysqli_multi_query function to run the entire content of the SQL script on a single call. Following the lines of your example, it would be something like:

<?php
  $dbhost = 'XXXXXXX';
  $dbuser = 'XXXXXXX';
  $dbpass = 'XXXXXXX';

  $conn = mysqli_connect($dbhost, $dbuser, $dbpass);

  if(! $conn ) {
    die('Could not connect: ' . mysql_error());
  }

  $backup_file  = "/path/to/chofer.sql";
  $sql = file_get_contents($backup_file);

  mysqli_select_db($conn,'test_db');

  if (mysqli_multi_query($conn, $sql)) {
    do {
      /* almacenar primer juego de resultados */
      if ($result = mysqli_store_result($link)) {
        while ($row = mysqli_fetch_row($result)) {
          printf("%s\n", $row[0]);
        }
        mysqli_free_result($result);
      }
      /* mostrar divisor */
      if (mysqli_more_results($link)) {
        printf("-----------------\n");
      }
    } while (mysqli_next_result($link));
  }


echo "<script>";
echo "alert ('Restauración exitosa.');";
echo "window.location.replace('index.php');";
echo "</script>";
mysqli_close($conn);
?>

It is likely that the code has some syntax error, the idea is that you get involved with it and produce your own solution.