Skip to main content

Viewing MySQL Metadata

The INFORMATION_SCHEMA is a virtual database that is automatically created and populated by
MySQL, there is no need to create an INFORMATION_SCHEMA database. There also is no associated
file structure due to the fact that the contents of the data are actual views, rather than logical data being
stored in any one location. Only SELECT statements are allowed against the tables in the
INFORMATION_SCHEMA database. The following SELECT example demonstrates some of the
metadata that can be viewed:

<?php
// Load variables used in mysql_connect and connect to server
include "connect_info.php";
$linkID1 = mysql_connect($dbhost,$dbuser,$dbpass) or
die("Could not connect to MySQL server");
// Execute Query to Collect Metadata
$query = "SELECT TABLE_NAME, ENGINE, TABLE_ROWS, UPDATE_TIME FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='world'";
$results = mysql_query($query, $linkID1);
// Build HTML Table of Results
print "<table width='700' border='1'>\n";
print "<tr>\n<th>TABLE_NAME</th>\n<th>ENGINE</th>\n";
print "<th>TABLE_ROWS</th>\n<th>UPDATE_TIME</th>\n</tr>\n";
while ($row = mysql_fetch_array($results, MYSQL_BOTH)) {
print "<tr>\n<th>".$row[0]."</th>\n<th>".$row[1]."</th>\n";
print "<th>".$row[2]."</th>\n<th>".$row[3]."</th>\n</tr>\n ";
}
print "</table>";
// Close the connection to the MySQL server
mysql_close($linkID1);
?>
Labels:

Comments

Post a Comment

Popular posts from this blog

PHP INTRODUCTION

                     PHP  (recursive acronym for  PHP: Hypertext Preprocessor ) is a widely-used open source general-purpose scripting language that is especially suited for web development and can be embedded into HTML. PHP stands for  P HP:  H ypertext  P reprocessor PHP is a server-side scripting language, like ASP PHP scripts are executed on the server PHP supports many databases (MySQL, Informix, Oracle, Sybase, Solid, PostgreSQL, Generic ODBC, etc.) PHP is an open source software PHP is free to download and use Why PHP? PHP runs on different platforms (Windows, Linux, Unix, etc.) PHP is compatible with almost all servers used today (Apache, IIS, etc.) PHP is FREE to download from the official PHP resource:  www.php.net PHP is easy to learn and runs efficiently on the server side What can PHP do? Anything. PHP is mainly focused on server-side scripting, so you can...
6 comments
Read more

Self Joins

A table name is always ambiguous when table is joined to itself using a self-join. For example, the Country table in the world database contains an IndepYear column indicating the year in which each country achieved independence. To find all countries that have the same year of independence as some given country, a self-join can be used. However, the query cannot be written like this: mysql> SELECT IndepYear, Name, Name -> FROM Country JOIN Country -> ON IndepYear = IndepYear AND Name = 'Qatar'; ERROR 1066 (42000): Not unique table/alias: 'Country' Furthermore, the ambiguity cannot be removed from column references by preceding them with table name qualifiers because the names remain identical: mysql> SELECT Country.IndepYear, Country.Name, Country.Name -> FROM Country JOIN Country -> ON Country.IndepYear = Country.IndepYear -> AND Country.Name = 'Qatar'; ERROR 1066 (42000): Not unique table/alias: 'Country' It ...
Post a Comment
Read more