Create MySQL Database Using PHP

Creating a Database

To create and delete a database you should have admin privilege. Its very easy to create a new MySQL database. PHP uses mysql_query function to create a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

bool mysql_query( sql, connection );  

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn ) {
die('Could not connect: ' . mysql_error());
}

echo 'Connected successfully,';

$sql = 'CREATE Database test_db';
$retval = mysql_query( $sql, $conn );

if(! $retval ) {
die('Could not create database: ' . mysql_error());
}

echo "Database test_db created successfully\n";
mysql_close($conn);
?>

Selecting a Database

Once you establish a connection with a database server then it is required to select a particular database where your all the tables are associated.

This is required because there may be multiple databases residing on a single server and you can do work with a single database at a time.

PHP provides function mysql_select_db to select a database.It returns TRUE on success or FALSE on failure.

Syntax

bool mysql_select_db( db_name, connection );  

Creating Database Tables

To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function.

In case you need to create many tables then its better to create a text file first and put all the SQL commands in that text file and then load that file into $sql variable and excute those commands.

Consider the following content in sql_query.txt file

CREATE TABLE employee(
emp_id INT NOT NULL AUTO_INCREMENT,
emp_name VARCHAR(20) NOT NULL,
emp_address VARCHAR(20) NOT NULL,
emp_salary INT NOT NULL,
join_date timestamp(14) NOT NULL,
primary key ( emp_id ));