Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>i've just finished making the PDO version of david walsh's original backup function.<br> i've also improved it to address the issues mentioned in <a href="https://stackoverflow.com/a/18281687/20860">Bill Karwin's answer</a>; handles NULL, writes indvidual lines so no memory issues, with backticks etc.<br> Gives out pretty much exactly what mysqldump does.<br> Could do with a little tidying but here it is, please advise on any improvements</p> <pre><code>require 'login.php'; $DBH = new PDO("mysql:host=$db_hostname;dbname=$db_database; charset=utf8", $db_username, $db_password); //put table names you want backed up in this array. //leave empty to do all $tables = array(); backup_tables($DBH, $tables); function backup_tables($DBH, $tables) { $DBH-&gt;setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_TO_STRING ); //Script Variables $compression = false; $BACKUP_PATH = ""; $nowtimename = time(); //create/open files if ($compression) { $zp = gzopen($BACKUP_PATH.$nowtimename.'.sql.gz', "w9"); } else { $handle = fopen($BACKUP_PATH.$nowtimename.'.sql','a+'); } //array of all database field types which just take numbers $numtypes=array('tinyint','smallint','mediumint','int','bigint','float','double','decimal','real'); //get all of the tables if(empty($tables)) { $pstm1 = $DBH-&gt;query('SHOW TABLES'); while ($row = $pstm1-&gt;fetch(PDO::FETCH_NUM)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(',',$tables); } //cycle through the table(s) foreach($tables as $table) { $result = $DBH-&gt;query('SELECT * FROM '.$table); $num_fields = $result-&gt;columnCount(); $num_rows = $result-&gt;rowCount(); $return=""; //uncomment below if you want 'DROP TABLE IF EXISTS' displayed //$return.= 'DROP TABLE IF EXISTS `'.$table.'`;'; //table structure $pstm2 = $DBH-&gt;query('SHOW CREATE TABLE '.$table); $row2 = $pstm2-&gt;fetch(PDO::FETCH_NUM); $ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]); $return.= "\n\n".$ifnotexists.";\n\n"; if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; //insert values if ($num_rows){ $return= 'INSERT INTO `'.$table."` ("; $pstm3 = $DBH-&gt;query('SHOW COLUMNS FROM '.$table); $count = 0; $type = array(); while ($rows = $pstm3-&gt;fetch(PDO::FETCH_NUM)) { if (stripos($rows[1], '(')) {$type[$table][] = stristr($rows[1], '(', true); } else $type[$table][] = $rows[1]; $return.= $rows[0]; $count++; if ($count &lt; ($pstm3-&gt;rowCount())) { $return.= ", "; } } $return.= ")".' VALUES'; if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; } while($row = $result-&gt;fetch(PDO::FETCH_NUM)) { $return= "\n\t("; for($j=0; $j&lt;$num_fields; $j++) { $row[$j] = addslashes($row[$j]); //$row[$j] = preg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { //if number, take away "". else leave as string if (in_array($type[$table][$j], $numtypes)) $return.= $row[$j] ; else $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; } if ($j&lt;($num_fields-1)) { $return.= ','; } } $count++; if ($count &lt; ($result-&gt;rowCount())) { $return.= "),"; } else { $return.= ");"; } if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; } $return="\n\n-- ------------------------------------------------ \n\n"; if ($compression) { gzwrite($zp, $return); } else { fwrite($handle,$return); } $return = ""; } $error1= $pstm2-&gt;errorInfo(); $error2= $pstm3-&gt;errorInfo(); $error3= $result-&gt;errorInfo(); echo $error1[2]; echo $error2[2]; echo $error3[2]; if ($compression) { gzclose($zp); } else { fclose($handle); } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POPDO MySQL backups function
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USLan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThis is getting into code review, which is not the purpose of StackOverflow. But briefly: no proper support for NULL (you convert them to `''`); not consistently delimiting table names; using non-ANSI double-quotes as string delimiters; using buffered queries on huge tables will break PHP max memory limit; appending all rows for a huge table will break PHP max memory limit; using addslashes() instead of PDO::quote(); checking for query errors only at the end of the function; not checking for failed file creation; gzip extension may not be loaded
      singulars
      1. PO
      1. USBill Karwin
    2. COAlso, probably still doesn't support UTF8 data.
      singulars
      1. PO
      1. USBill Karwin
    3. COouch. charset is stated in connection. whats wrong with NULLs to ''? table structure has to be very very big to max memory. each insert row is written to file individually so again, row has to be very very big to max memory. addslashes isnt THAT bad but will change to PDO quote. will add check for gzip. agree, error checking can be a bit prettier. but it is getting there, no?
      singulars
      1. PO
      1. USLan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload