MySQL Metadata

You might be interested in the following three types of information in MySQL:

• Query Result Information: The number of records affected by SELECT, UPDATE, or DELETE statements.
• Database and Table Information: Includes the structure information of databases and tables.
• MySQL Server Information: Includes the current status and version number of the database server.

In the MySQL command prompt, we can easily obtain the above server information. However, if you are using scripting languages like Perl or PHP, you need to call specific interface functions to retrieve them. We will discuss this in detail next.

Retrieving the Number of Affected Records by Query Statements

PERL Example

In DBI scripts, the number of affected records is returned by the functions do() or execute():

# Method 1
# Execute $query using do()
my $count = $dbh->do($query);
# Output 0 if an error occurs
printf "%d rows affected\n", (defined($count) ? $count : 0);

# Method 2
# Execute $query using prepare() and execute()
my $sth = $dbh->prepare($query);
my $count = $sth->execute();
printf "%d rows affected\n", (defined($count) ? $count : 0);

PHP Example

In PHP, you can use the mysqli_affected_rows() function to retrieve the number of affected records by query statements.

$result_id = mysqli_query($conn_id, $query);
# Return 0 if the query fails
$count = ($result_id ? mysqli_affected_rows($conn_id) : 0);
print("$count rows affected\n");

List of Databases and Tables

You can easily retrieve the list of databases and tables in the MySQL server. If you do not have sufficient permissions, the result will return null.

You can also use the SHOW TABLES or SHOW DATABASES statements to retrieve the list of databases and tables.

PERL Example

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

PHP Example

The following example outputs all databases on the MySQL server:

<?php
$dbhost = 'localhost';  // mysql server host address
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if (!$conn) {
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
$db_list = mysqli_query($conn, 'SHOW DATABASES');
while ($db = mysqli_fetch_object($db_list)) {
  echo $db->Database . "<br />";
}
mysqli_close($conn);
?>

Retrieving Server Metadata

The following command statements can be used in the MySQL command prompt or in scripts, such as PHP scripts.