Get mysql database schema

Question

Is it possible to get the full structure of a mySQL database in json or xml format?

what I'm looking for is a way to get the schema (stored data doesn't matter) to use it to create a backend/crud for the application.

Answer 1

My answer maybe also does not fit to 100% your question because it also doesn't return a json. But may it helps somebody

function makeTable($query, $array = null)
{
    $stmt = makeStatement($query, $array);

    if ($stmt instanceof Exception) {
        echo $stmt->getCode() . ': ' . $stmt->getMessage();
    } else {
        $meta = array();
        echo '<table class ="table"
     <tr>';

        for ($i = 0; $i < $stmt->columnCount(); $i++) {
            $meta[] = $stmt->getColumnMeta($i);
            echo '<th>' . $meta[$i]['name'] . '</th>';
        }
        echo '<th></th>';
        echo '</tr>';


        while ($databases = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo '<tr class="clickable-row">';
            foreach ($databases as $database) {
                echo '<td>' . $database . '</td>';
            }
            echo '<form method="post" action="index.php?site=tables">';
            echo '<input type="hidden" name="database" value="' . $database . '">';
            echo '<td>';
            echo '<button type="submit" class="btn btn-outline-secondary" name="choose">Auswählen</button>';
            echo '</td>';
            echo '</form>';
            echo '</tr>';
        }
        echo '</table>';
    }
}

here is the tables.php:

<?php
$schema;

if (isset($_POST['database'])) {
    $schema = $_POST['database'];
    echo "Schema: " . $schema . "<br>";
}

$query = "show tables from " . $schema;
$stmt = makeStatement($query);

if ($stmt instanceof Exception) {
    echo $stmt->getCode() . ': ' . $stmt->getMessage();
} else {
    $meta = array();
    echo '<table class ="table"
     <tr>';

    for ($i = 0; $i < $stmt->columnCount(); $i++) {
        $meta[] = $stmt->getColumnMeta($i);
        echo '<th>' . $meta[$i]['name'] . '</th>';
    }
    echo '<th></th>';
    echo '<th></th>';
    echo '</tr>';


    while ($tables = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo '<tr class="clickable-row">';
        foreach ($tables as $table) {
            echo '<td>' . $table . '</td>';
        }
        echo '<form method="post" action="index.php?site=columnsInhalt">';
        echo '<input type="hidden" name="schema" value="' . $schema . '">';
        echo '<input type="hidden" name="table" value="' . $table . '">';
        echo '<td>';
        echo '<button type="submit" class="btn btn-outline-secondary" name="inhalt">Inhalt</button>';
        echo '</td>';
        echo '</form>';

        echo '<form method="post" action="index.php?site=columnsStructure">';
        echo '<input type="hidden" name="schema" value="' . $schema . '">';
        echo '<input type="hidden" name="table" value="' . $table . '">';
        echo '<td>';
        echo '<button type="submit" class="btn btn-outline-secondary" name="structur">Struktur</button>';
        echo '</td>';
        echo '</form>';
        echo '</tr>';
    }
    echo '</table>';
}

and here are the columnsInhalt and columnsStructure

<?php
$schema;

if (isset($_POST['schema']) && isset($_POST['table'])) {
    $schema = $_POST['schema'];
    $table = $_POST['table'];
    echo 'Schema: ' . $schema . "<br>";
    echo 'Table: ' . $table . "<br>";
}

$query = "select * from " . $schema . "." . $table;
$query = "show columns from " . $schema . "." . $table; //for structure
$stmt = makeStatement($query);

if ($stmt instanceof Exception) {
    echo $stmt->getCode() . ': ' . $stmt->getMessage();
} else {
    $meta = array();
    echo '<table class ="table"
        <tr>';
            for ($i = 0; $i < $stmt->columnCount(); $i++) {
                $meta[] = $stmt->getColumnMeta($i);
                echo '<th>' . $meta[$i]['name'] . '</th>';
            }
        echo '</tr>';

        while ($columns = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo '<tr class="clickable-row">';
                foreach ($columns as $column) {
                    echo '<td>' . $column . '</td>';
                }
            echo '</tr>';
        }
    echo '</table>';
}

Answer 2

This is not a fully comprehensive answer to the question, as it doesn't return a json. But it's a simple class that gives all relevant info about a table as an object. This could easily be transformed to return a json.

The class relies on having passed a db object that implements a rawQuery method, as PHP-Mysqli-Database-Class does. But you could easily rewrite the method extractTableInfo if you need.

<?php
Class dbHelper {
    private $db;

    public function __construct($db) {
        $this->db = $db;
    }

    public function extractTableInfo($tableName) {
        $result = $this->db->rawQuery("DESCRIBE `".$tableName."`");

        $tableInfo = array();
        foreach ($result as $key => $value) {
            $info = new \stdClass();
            $info->name = $value['Field'];
            $info->type = $this->_getColumnType($value['Type']);
            $info->length = $this->_getLength($value['Type']);
            $info->hasNull = $this->_getNull($value['Null']);
            $info->default = $this->_getDefault($value['Default']);
            $tableInfo[] = $info;
        }
        return $tableInfo;
    }

    public function getIndexes($tableName) {
        $result = $this->db->rawQuery("SHOW INDEX FROM `".$tableName."`");
        return $result;
    }



    private function _getLength($info) {
        $pattern = '/\({1}([\d\W]*)\){1}/';
        preg_match($pattern, $info, $matches);
        return isset($matches[1]) ? $matches[1] : NULL;
    }

    private function _getColumnType($info) {
        $pattern = '/([\w]*)(\([\d\W]*\))*/';
        preg_match($pattern, $info, $matches);
        return isset($matches[1]) ? $matches[1] : NULL;
    }

    private function _getNull($info) {
        if($info==='NO') {
            return false;
        } else {
            return true;
        }
    }

    private function _getDefault($info) {
        if($info>='') {
            return $info;
        } else {
            return NULL;
        }
    }

}

Usage:

$db = YOUR DATABASE OBJECT/CLASS;
$dbHelper = new dbHelper($db);
$tableName = "test";
$tableInfo = $dbHelper->extractTableInfo($tableName);
echo json_encode($tableInfo);

To get a list of all tables in a database you'll need:

SHOW TABLES [in dbname]

or follow this question

Still, there are many alternatives with different benefits/outputs:

• using mysql workbench
• using phpmyadmin
• using command line mysqldump -u root -p --no-data dbname > schema.sql