Post By: Hanan Mannan
Contact Number: Pak (+92)-321-59-95-634
-------------------------------------------------------

Obtaining and Using MySQL Metadata

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

• Information about the result of queries: This includes number of records affected 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.

It's very easy to get all these information at mysql prompt, but while using PERL or PHP APIs, we need to call various APIs explicitly to obtain all these information. Following section will show you how to obtain this 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 at mysql prompt or using any script like PHP to get various important informations about database server.

Command	Description
SELECT VERSION( )	Server version string
SELECT DATABASE( )	Current database name (empty if none)
SELECT USER( )	Current username
SHOW STATUS	Server status indicators
SHOW VARIABLES	Server configuration variables