php

Working with Database

Retrieving Database and Table list

Now that you've successfully used PHP to make a connection to MySQL, it's time to familiarize yourself with some of the built-in MySQL-related functions. In this section, you use the following functions:

. mysql_list_dbs()- Used to list the databases on a MySQL server.

Syntax: resource mysql_list_dbs ( [resource link_identifier ] )

mysql_list_dbs() will return a result pointer containing the databases available from the current mysql daemon.

. mysql_num_rows()- Returns the number of rows in a result set.

Syntax:
int mysql_num_rows ( resource result )
mysql_num_rows() returns the number of rows in a result set.

. mysql_tablename()- Despite its name, can extract the name of a table or a database from a result.

Syntax:
string mysql_tablename ( resource result, int i )

Getting database list

<?php
//PHP Code to retrieve the list of databases in MySQL.

class dbUtils{
	private $list_db;
	private $total_db;
	private	$list_of_dbs;

	public function  __construct(){

	}
	public function connect(){
		$con=mysql_connect("localhost","root","");
		if(!$con)
			return NULL;
		else
			return $con;

	}
	public function listDB()
	{
		$con=$this->connect();

		if(!$con)
			 $this->list_db="<b>Unable t
o Connect to MySQL ".mysql_error()."</b>";
		else
		{
			#echo "<b>Connection Created Successfully.</b>";

			$total_db=mysql_list_dbs($con)or die(mysql_error());
			$i=0;
			$list_of_dbs="<ul type=\"disc\">";
			if($total_db)
			{
				for($i;$i<mysql_num_rows($total_db);$i++)
				{
					$db_list[$i]=mysql_tablename($total_db,$i);
					$list_of_dbs.="<li>$db_list[$i]</li>";
				}

			}
			$list_of_dbs.="</ul>";
			mysql_close($con)or die(mysql_error());
			return $list_of_dbs;
		}
	}
}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<?php
$dbutil=new dbUtils();
?>
<HTML>
<HEAD>
<TITLE>PHP Tutorial: Working with MySQL</TITLE>
</HEAD>

<BODY>
<hr>
<h3>List  of Databases on localhost</h3>
<hr>
<FIELDSET>
	<LEGEND>Databases</LEGEND>
	<?php
	echo $dbutil->listDB();
	?>

</FIELDSET>
</BODY>
</HTML>

Description:
After connecting to database we have created a variable to hold the result of the mysql_list_dbs() function.

$total_db=mysql_list_dbs($con)or die(mysql_error());

After declaring all necessary variables begin a for loop. This loop will continue for as long as the value of $i is less than the number of rows in the $total_db result value:

for($i;$i<mysql_num_rows($total_db);$i++) {

Once you're within the for loop, get the name of the database reflected in the current row of the result:

$db_list[$i]=mysql_tablename($total_db,$i);
$list_of_dbs.="<li>$db_list[$i]</li>";

Close the for loop, the bulleted list, and your PHP block:

}
$db_list .= "</ul>";
?>

Save the above script and open the page in browser to view the output.

Output:

Output may vary from system to system depending upon how much you have played with your MySQL server, but it should look like the one below:

Getting Table list

To retrieve the list of tables we have just added 1 more for loop inside the main for loop. This for loop retrieve list of tables of each database and add it to the list.

<?php
//PHP Code to retrieve the list of databases in MySQL.


$con=mysql_connect("localhost","root","") ;
$list_db;
if(!$con)
	 die ("<b>Unable to Connect to MySQL ".mysql_error()."</b>");
else
{
	#echo "<b>Connection Created Successfully.</b>";

	$total_db=mysql_list_dbs($con)or die(mysql_error());
	$i=0;
	$list_of_dbs="<ul type=\"disc\">";
	if($total_db)
	{
		//Main loop to retireve the l
ist of databases on  the MySQL server
		for($i;$i<mysql_num_rows($total_db);$i++)
		{
			$db_list[$i]=mysql_tablename($total_db,$i);
			$list_of_dbs.="<li>$db_list[$i]";
			$tables=mysql_list_tables($db_list[$i]);
			$list_of_tables="<ul>";
			$i1=0;
			if($tables)
			{
	//For Loop to retireve list of tables in current database
	for ($i1;$i1<mysql_num_rows($tables);$i1++)
				{
			$table_list[$i1]=mysql_tablename($tables,$i1);
			$list_of_tables.="<li>$table_list[$i1]</li>";
				}
				$list_of_tables.="</ul>";
				$list_of_dbs.="$list_of_tables </li>";
			}

		}

	}
	$list_of_dbs.="</ul>";
	mysql_close($con)or die(mysql_error());
}

?>

<!DOCTYPE HTML PUBLIC 
"-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>PHP Tutorial: Working with MySQL</TITLE>
</HEAD>

<BODY>
<hr>
<h3>List  of Databases on localhost</h3>
<hr>
<FIELDSET>
	<LEGEND>Databases</LEGEND>
	<?php
	echo $list_of_dbs;
	?>

</FIELDSET>
</BODY>
</HTML>

Output: Try it yourself