Ooer.com - Chris Neale

Database Structure Querys

One less well known feature of SQL, and MySQL, is the ability to look at the structure of a database using SQL queries. This is a handy feature to be able to make use of as it allows you to build dynamic forms that automatically adapt themselves to your database schema.

The first step to using a database structure query is to connect to the database in the usual manner.


<?php

$connection = mysql_connect("localhost","root","password");

?>

We haven't yet chosen a database that we want to connect to. SQL allows us to list the databases available to the user that is currently connected to MySQL.


<?php

$sql = "show databases";

$result = mysql_query($sql,$connection);

?>

This will generate a list of a few databases. With a little more PHP it would be relatively simple to build a page that allowed the user to select a database to use. However, we'll not bother with that here.

After you have chosen a database to use you can then list the tables available on it.


<?php

$sql = "show tables";

$result = mysql_query($sql,$connection);

?>

This SQL query produces a list of the tables that are contained within the database in a column called Tables_in_[database_name], but it doesn't give us any information about them. In order to find out about the tables we need to describe them. Describe is an SQL function that does precisely what it suggests, it describes things.


<?php

$sql = "describe items";

$result = mysql_query($sql,$connection);

?>

This produces a list of columns in the table with fields for other information. The extra columns are the data type, whether or not it can be a NULL value, the key type (set as PRI if the column is a primary key), the default value, and a field for any extra information. Extras are things such as whether the column is set to auto_increment, unique and so on.

If you also need to know what priviledges the current database user has available on the columns in the table you're examining the describe function will not return enough detail. However, you can use the alternative "show full columns" syntax instead.


<?php

$sql = "show full columns from items";

$result = mysql_query($sql,$connection);[php]

This returns the same information as describe, but with the addition of a priviledges field containing a comma seperated list of access priviledges for the current database user.



These functions are the basis for writing a database adminstration tool such as PHPMyAdmin. With a little more PHP code you can produce a tool to access a wide range of MySQL database features above and beyond the usual selects, inserts and updates.

?>

Comments

Comments are not currently being accepted for this article.

Sidebar

Published:

17/01/2007

Views:

2114

Author:

Chris Neale

Labels:

database, mysql, query, sql

Print:

View print version