Friday, July 9, 2010

Get MySQL table data structure with PHP

Hi, today I'll show how to retrieve informations about a MySQL table through php in order for example to create a php class making editable a table in an automatic way (that is: stupid class, i give you a name of a table and YOU have to create its backend for me). This work is done very well by the python framework Django for example.
Now, there are several ways to do so, I'll use the interrogation of the information_schema db.
Well, let's see the code. I post here a simple function, clearly the informations that may be retrieved are more than these.
function getTableStructure($dbname, $table) {
    $structure = array();
    $fields = array();

    $res = mysql_query($query);

    while($row = mysql_fetch_array($res)) {
        $fields[$row['COLUMN_NAME']] = array(
        if($row['COLUMN_KEY']=='PRI') $structure['primary_key'] = $row['COLUMN_NAME'];
    $structure['fields'] = $fields;

    return $structure;


The important informations to do an auto-generation-form class are:
  • default: the default value (we may insert it as a default value in the input field of the form)
  • null: we may use it to decide whether a field must be compulsory or not
  • type: the must important: which form element we'll use? It depends on data type and ...
  • max_length: the maximum number of characters acceopted for the field
  • key: we may want to check for uniques keys etc...
  • extra: i think it's useful to know if a field is auto_increment because we may not make it editable
That's all falks, hasta la proxima!

No comments: