PHP & MySQL Tutorial MySQL with PHP: Part II - Creating and Deleting Database

bogotobogo.com site search:

MySQL with PHP II

Creating a Database

The function mysql_create_db() is not supported by PHP 5. The function mysql_create_db() is deprecated.

It is preferable to use mysql_query() to issue an sql CREATE DATABASE statement instead. So, we're going to use sql command, mysql_query() to create a database in PHP 5. Here is the code to create database in PHP 5.

# creating_database_simple.php

<?php
$servername = "localhost";
$username = "bogo";
$password = "bogopass";
$dbname = "new_db";

$link=mysql_connect($servername, $username, $password)
	or die( " Unable to connect to server ");

$query="CREATE DATABASE IF NOT EXISTS $dbname";
if (mysql_query($query)) {
	echo "Database <b>$dbname</b> created successfully <br />";
} 
else {
	echo "Error in creating database: <br /><br />". mysql_error ();
}
?>

Note that inside the query to create database we used IF NOT EXISTS, so there will not be any error message if database is already exist and we are trying to create with same name. Without the clause IF NOT EXISTS we will get error message if we try to create a database which is already exists.

Let's check if it has been really created.

Creating a Database with a Form Input

The following example displays all databases and the provide a form where a new database name is its input. When the form is submitted, the script will create a new database with the supplied name.

<?php
$servername = "localhost";
$username="bogo"; 	
$password="bogopass";

$connect = mysql_connect($servername, $username, $password)
	or die ( 'Unable to connect to server');

if(isset($_POST['create_button'])) {		
	$dbname = $_REQUEST["db"];
	$query="CREATE DATABASE IF NOT EXISTS $dbname";
	if (mysql_query($query)) {
		echo "Database <b>$dbname</b> created successfully <br>";
	} 
	else {
		echo "Error in creating database: <br><br>". mysql_error ();
	}
}

$list = null;
$result_set = mysql_list_dbs($connect);
for ($row = 0; $row < mysql_num_rows($result_set); $row++)
	$list .= mysql_tablename($result_set, $row) . " | ";
?>

<html>
<head> <title>Creating Databases</title> </head>
<body>
	<form action = "<?php echo $_SERVER['PHP_SELF'] ; ?>" 
		method="post">Current databases: <?php echo $list; ?> <hr>
	Name:<input type="text" name="db">
	<input type = "submit" name="create_button" value="Create database">
	</form>
</body>
</html>

After we select Create database, we get:

Deleting a Database with a Form Input

The mysql_drop_db() attempts to drop (remove) an entire database from the server associated with the specified link identifier. This function is deprecated, it is preferable to use mysql_query() to issue an sql DROP DATABASE statement instead.

The example below mirrors the example for creating a new database of the previous section. It tries to delete the specified database when the form is submitted.

<?php
$servername = "localhost";
$username="bogo"; 	
$password="bogopass";

$connect = mysql_connect($servername, $username, $password)
	or die ( 'Unable to connect to server');

if(isset($_POST['delete_button'])) {		
	$dbname = $_REQUEST["db"];


	$query="DROP DATABASE $dbname";
	if (mysql_query($query)) {
		echo "Database <b>$dbname</b> deleted successfully <br>";
	} 
	else {
		echo "Error in deleting database: <br><br>". mysql_error ();
	}
}

$list = null;
$result_set = mysql_list_dbs($connect);
for ($row = 0; $row < mysql_num_rows($result_set); $row++)
	$list .= mysql_tablename($result_set, $row) . " | ";
?>

<html>
<head> <title>Deleting Databases</title> </head>
<body>
	<form action = "<?php echo $_SERVER['PHP_SELF'] ; ?>" 
		method="post">Current databases: <?php echo $list; ?> <hr>
	Name:<input type="text" name="db">
	<input type = "submit" name="delete_button" value="Delete database">
	</form>
</body>
</html>