Posted By

the_coder on 12/19/08


Tagged

mysql backup


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

igor822
baqc
Nix


MySQL backup


 / Published in: PHP
 

  1. function _backup($params = array())
  2. {
  3. if (count($params) == 0)
  4. {
  5. return FALSE;
  6. }
  7.  
  8. // Extract the prefs for simplicity
  9. extract($params);
  10.  
  11. // Build the output
  12. $output = '';
  13. foreach ((array)$tables as $table)
  14. {
  15. // Is the table in the "ignore" list?
  16. if (in_array($table, (array)$ignore, TRUE))
  17. {
  18. continue;
  19. }
  20.  
  21. // Get the table schema
  22. $query = $this->db->query("SHOW CREATE TABLE `".$this->db->database.'`.'.$table);
  23.  
  24. // No result means the table name was invalid
  25. if ($query === FALSE)
  26. {
  27. continue;
  28. }
  29.  
  30. // Write out the table schema
  31. $output .= '#'.$newline.'# TABLE STRUCTURE FOR: '.$table.$newline.'#'.$newline.$newline;
  32.  
  33. if ($add_drop == TRUE)
  34. {
  35. $output .= 'DROP TABLE IF EXISTS '.$table.';'.$newline.$newline;
  36. }
  37.  
  38. $i = 0;
  39. $result = $query->result_array();
  40. foreach ($result[0] as $val)
  41. {
  42. if ($i++ % 2)
  43. {
  44. $output .= $val.';'.$newline.$newline;
  45. }
  46. }
  47.  
  48. // If inserts are not needed we're done...
  49. if ($add_insert == FALSE)
  50. {
  51. continue;
  52. }
  53.  
  54. // Grab all the data from the current table
  55. $query = $this->db->query("SELECT * FROM $table");
  56.  
  57. if ($query->num_rows() == 0)
  58. {
  59. continue;
  60. }
  61.  
  62. // Fetch the field names and determine if the field is an
  63. // integer type. We use this info to decide whether to
  64. // surround the data with quotes or not
  65.  
  66. $i = 0;
  67. $field_str = '';
  68. $is_int = array();
  69. while ($field = mysql_fetch_field($query->result_id))
  70. {
  71. // Most versions of MySQL store timestamp as a string
  72. $is_int[$i] = (in_array(
  73. strtolower(mysql_field_type($query->result_id, $i)),
  74. array('tinyint', 'smallint', 'mediumint', 'int', 'bigint'), //, 'timestamp'),
  75. TRUE)
  76. ) ? TRUE : FALSE;
  77.  
  78. // Create a string of field names
  79. $field_str .= $field->name.', ';
  80. $i++;
  81. }
  82.  
  83. // Trim off the end comma
  84. $field_str = preg_replace( "/, $/" , "" , $field_str);
  85.  
  86.  
  87. // Build the insert string
  88. foreach ($query->result_array() as $row)
  89. {
  90. $val_str = '';
  91.  
  92. $i = 0;
  93. foreach ($row as $v)
  94. {
  95. // Is the value NULL?
  96. if ($v === NULL)
  97. {
  98. $val_str .= 'NULL';
  99. }
  100. else
  101. {
  102. // Do a little formatting...
  103. $v = str_replace(array("\x00", "\x0a", "\x0d", "\x1a"), array('\0', '\n', '\r', '\Z'), $v);
  104. $v = str_replace(array("\n", "\r", "\t"), array('\n', '\r', '\t'), $v);
  105. $v = str_replace('\\', '\\\\', $v);
  106. $v = str_replace('\'', '\\\'', $v);
  107. $v = str_replace('\\\n', '\n', $v);
  108. $v = str_replace('\\\r', '\r', $v);
  109. $v = str_replace('\\\t', '\t', $v);
  110.  
  111. // Escape the data if it's not an integer
  112. if ($is_int[$i] == FALSE)
  113. {
  114. $val_str .= $this->db->escape($v);
  115. }
  116. else
  117. {
  118. $val_str .= $v;
  119. }
  120. }
  121.  
  122. // Append a comma
  123. $val_str .= ', ';
  124. $i++;
  125. }
  126.  
  127. // Remove the comma at the end of the string
  128. $val_str = preg_replace( "/, $/" , "" , $val_str);
  129.  
  130. // Build the INSERT string
  131. $output .= 'INSERT INTO '.$table.' ('.$field_str.') VALUES ('.$val_str.');'.$newline;
  132. }
  133.  
  134. $output .= $newline.$newline;
  135. }
  136.  
  137. return $output;
  138. }

Report this snippet  

You need to login to post a comment.