Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To anyone looking for the function which acts like the mysqldump, here is the latest draft, with the imperfections discussed in the comments above/below ironed out. Enjoy.</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_NATURAL ); //Script Variables $compression = false; $BACKUP_PATH = ""; $nowtimename = time(); //create/open files if ($compression) { $zp = gzopen($BACKUP_PATH.$nowtimename.'.sql.gz', "a9"); } 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 = ""; } $count =0; while($row = $result-&gt;fetch(PDO::FETCH_NUM)) { $return= "\n\t("; for($j=0; $j&lt;$num_fields; $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)) &amp;&amp; (!empty($row[$j]))) $return.= $row[$j] ; else $return.= $DBH-&gt;quote($row[$j]); } else { $return.= 'NULL'; } 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. USLan
    1. USLan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POPDO MySQL backups function
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CORunning the script returns the following error: Fatal error: Call to a member function columnCount() on a non-object ... Maybe would be better submit the code to Github so everyone can contribute to improve the code and fix bugs.
      singulars
      1. PO
      1. USeldblz
    2. COok, will do. but if you've got that error message, it sounds like you havent connected to mysql, ie somethings wrong with password, username, databasename or hostname
      singulars
      1. PO
      1. USLan
    3. CO@Lan, if he hadn't connected, he would have gotten an error much earlier than the call to columnCount(). You don't have good error-checking in this script to catch failed statements.
      singulars
      1. PO
      1. USBill Karwin
 

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