Obtaining & Using MySQL Database Metadata

There are three information which you would like to have from MySQL.

• Information about the result of queries: This includes number of records effected by any SELECT, UPDATE or DELETE statement.

• Information about tables and databases: This includes information pertaining to the structure of tables and databases.

• Information about the MySQL server: This includes current status of database server, version number etc.

Its very easy to get all these information at mysql prompt. BUt while using PERL or PHP APIs then we need to call various APIs explicitely to obtain all these information. Following section will show you how to obtain these information.

Obtaining the Number of Rows Affected by a Query:

PERL Example:

In DBI scripts, the affected-rows count is returned by do( ) or by execute( ), depending on how you execute the query:

# Method 1
# execute $query using do( )
my $count = $dbh->do ($query);
# report 0 rows if an error occurred
printf "%d rows were affected\n", (defined ($count) ? $count : 0);
# Method 2
# execute query using prepare( ) plus execute( )
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

PHP Example:

In PHP, invoke the mysql_affected_rows( ) function to find out how many rows a query changed:

$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n");

Listing Tables and Databases:

This is very easy to list down all the databases and tables available with database server. Your result may be null if you don't have sufficient privilege.

Apart from the method I have mentioned below you can use SHOW TABLES or SHOW DATABASES queries to get list of tables or databases either in PHP or in PERL.

PERL Example:

# Get all the tables available in current database.
my @tables = $dbh->tables ( );
foreach $table (@tables ){
   print "Table Name $table\n";
}

PHP Example:

<?php
$con = mysql_connect("localhost", "userid", "password");
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}
$db_list = mysql_list_dbs($con);
while ($db = mysql_fetch_object($db_list))
{
  echo $db->Database . "<br />";
}
mysql_close($con);
?>

Getting Server Metadata:

There are following commands in MySQL which can be executed either are mysql prompt or using any script like PHP to get various important information about database server.