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 );
|
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);
?>
|
