Monday 27 May 2013
Facebook StumbleUpon Twitter Google+ Pin It

Create MySQL Database Using PHP


Creating a Database:

To create and delete a database you should have admin priviledge. 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 );



ParameterDescription
sqlRequired - SQL query to create a database
connectionOptional - if not specified then last opend connection by mysql_connect will be used.

Example:

Try out following example to create a database:
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$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 estblish 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 );



ParameterDescription
db_nameRequired - Database name to be selected
connectionOptional - if not specified then last opend connection by mysql_connect will be used.

Example:

Here is the example showing you how to select a database.
<?php
$dbhost = 'localhost:3036';
$dbuser = 'guest';
$dbpass = 'guest123';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db( 'test_db' );
mysql_close($conn);
?>

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.

Example:

Try out following example to create a table:
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
$sql = '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 ))';

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create table: ' . mysql_error());
}
echo "Table employee created successfully\n";
mysql_close($conn);
?>
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 ));



<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$query_file = 'sql_query.txt';

$fp    = fopen($query_file, 'r');
$sql = fread($fp, filesize($query_file));
fclose($fp); 

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create table: ' . mysql_error());
}
echo "Table employee created successfully\n";
mysql_close($conn);
?>

No comments: