Advanced Search

MySQL Schema XML Export

/ Published in: PHP
Save to your folder(s)
Export entire database schema to local xml file.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. private function execute( $sql )
  2. {
  3.   return $this->mysqli->query ( $sql );
  4. }
  5. /**
  6.  * I save the entire database schema to a local xml file.
  7.  *
  8.  * @param [string] $database the name of your database
  9.  * @return [result] true or false
  10.  */
  11. public function saveSchemaXML( $database )
  12. {
  13. 	$dom = new DOMDocument ( '1.0' );
  14.  
  15. 	/************************************
  16. 	 * Builds the root 
  17. 	 ************************************/
  18. 	//create a element
  19. 	$schema = $dom->createElement ( 'schema' );
  20. 	//set the element on itself
  21. 	$schema = $dom->appendChild ( $schema );
  22. 	//set a attribute for the schema node 
  23. 	$schema->setAttribute ( 'name', $database );
  24.  
  25. 	/***********************************
  26. 	 * Builds the table inside the root
  27. 	 **********************************/
  28. 	$tableQuery = $this->execute ( "SHOW TABLES FROM $database" );
  29.  
  30. 	while ( $tableRow = mysqli_fetch_row ( $tableQuery ) )
  31. 	{
  32. 		//create a element
  33. 		$table = $dom->createElement ( 'table' );
  34. 		//set the element on itself
  35. 		$table = $dom->appendChild ( $table );
  36. 		//set a attribute
  37. 		$table->setAttribute ( 'name', $tableRow [ 0 ] );
  38.  
  39. 		$fieldQuery = $this->execute ( "DESCRIBE $database.$tableRow[0]" );
  40.  
  41. 		while ( $fieldRow = mysqli_fetch_assoc ( $fieldQuery ) )
  42. 		{
  43. 			/***********************************
  44. 			 * Builds the attributes inside the table
  45. 			 **********************************/
  46. 			//create a element
  47. 			$field = $dom->createElement ( 'field' );
  48. 			//set the element on itself
  49. 			$field = $dom->appendChild ( $field );
  50. 			//set the name attribute
  51. 			$field->setAttribute ( 'name', $fieldRow [ 'Field' ] );
  52. 			//set the type attribute
  53. 			$field->setAttribute ( 'type', $this->replaceNumbers ( $fieldRow [ 'Type' ] ) );
  54. 			//set the null attribute
  55. 			$field->setAttribute ( 'null', strtolower ( $fieldRow [ 'Null' ] ) );
  56.  
  57. 			if ( $fieldRow [ 'Default' ] != '' )
  58. 			{
  59. 				//set the default
  60. 				$field->setAttribute ( 'default', strtolower ( $fieldRow [ 'Default' ] ) );
  61. 			}
  62. 			if ( $fieldRow [ 'Key' ] != '' )
  63. 			{
  64. 				//set the key
  65. 				$field->setAttribute ( 'key', strtolower ( $fieldRow [ 'Key' ] ) );
  66. 			}
  67. 			if ( $fieldRow [ 'Extra' ] != '' )
  68. 			{
  69. 				//set the value/length attribute
  70. 				$field->setAttribute ( 'extra', strtolower ( $fieldRow [ 'Extra' ] ) );
  71. 			}
  72.  
  73. 			//put the field inside of the table
  74. 			$table->appendChild ( $field );
  75. 		}
  76.  
  77. 		//put the table inside of the schema
  78. 		$schema->appendChild ( $table );
  79. 	}
  80.  
  81. 	$dom->formatOutput = true;
  82. 	$dom->saveXML ();
  83. 	$filename = 'output/' . ucfirst ( $database ) . 'Schema.xml';
  84.  
  85. 	//save the file
  86. 	$xml = $dom->save ( $filename );
  87.  
  88. 	//change the permissions
  89. 	chmod ( "$filename", 0777 );
  90.  
  91. 	return $xml;
  92. }

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.