Skip to main content
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.
CommandDescription
SELECT VERSION( )Server version string
SELECT DATABASE( )Current database name (empty if none)
SELECT USER( )Current username
SHOW STATUSServer status indicators
SHOW VARIABLESServer configuration variables

Comments

Post a Comment

Popular posts from this blog

The Windows Firewall with Advanced Security is a firewall that runs on the Windows Server 2012 and is turned on by default. The Firewall settings within Windows Server 2012 are managed from within the  Windows Firewall Microsoft Management Console . To set Firewall settings perform the following steps − Step 1  − Click on the Server Manager from the task bar → Click the Tools menu and select Windows Firewall with Advanced Security. Step 2  − To see the current configuration settings by selecting  Windows Firewall Properties  from the MMC. This  allows access to modify the settings  for each of the three firewall profiles, which are –  Domain, Private and Public  and IPsec settings. Step 3  − Applying custom rules, which will include the following two steps − Select either  Inbound Rules  or  Outbound Rules  under  Windows Firewall with Advanced Security  on the left side of the management console...
Post a Comment
Read more
The table creation command requires: Name of the table Names of fields Definitions for each field Syntax: Here is generic SQL syntax to create a MySQL table: CREATE TABLE table_name ( column_name column_type ); Now, we will create following table in  TUTORIALS  database. tutorials_tbl ( tutorial_id INT NOT NULL AUTO_INCREMENT , tutorial_title VARCHAR ( 100 ) NOT NULL , tutorial_author VARCHAR ( 40 ) NOT NULL , submission_date DATE , PRIMARY KEY ( tutorial_id ) ); Here few items need explanation: Field Attribute  NOT NULL  is being used because we do not want this field to be NULL. So if user will try to create a record with NULL value, then MySQL will raise an error. Field Attribute  AUTO_INCREMENT  tells MySQL to go ahead and add the next available number to the id field. Keyword  PRIMARY KEY  is used to define a column as primary key. You can use multiple columns separated by comma to define...
Post a Comment
Read more
In this chapter, we will see how to Install Active Directory in Windows Server 2012 R2. Many of us who have worked with the previous version run  DCPROMO.EXE  to install it, but in the 2012 version, it is recommended by Microsoft not to use it anymore. To continue with Installation follow the steps given below. Step 1  − Go to “Server Manager” → Manage → Add Roles and Feature. Step 2  − Click the Next button. Step 3  − As we are installing AAD on this machine, we will select “Role-based or featurebased Installation” → Next. Step 4  − Click on “Select a server from the server pool”, this is the case when it will be installed locally. Step 5  − Check mark in the box next to  Active Directory Domain Services . A box will be explaining additional roles services or features which are also required to install domain services. Step 6  − Click Add Features. Step 7  − Check “Group Policy Management” → Next. Step 8  ...
Post a Comment
Read more