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 );
|
| Parameter |
Description |
| sql | Required - SQL query to create a database |
| connection | Optional - 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 );
|
| Parameter |
Description |
| db_name | Required - Database name to be selected |
| connection | Optional - 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);
?>
Comments
Post a Comment