How To Create MySql Database and Table Using PHP File

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

Read More

Oracle Create Table Statement

Using a CREATE TABLE statement

The syntax for creating a primary key using a CREATE TABLE statement is:

CREATE TABLE table_name

(column1 datatype null/not null,

column2 datatype null/not null,

...

CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n)

);

For Example:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.

We could also create a primary key with more than one field as in the example below:

CREATE TABLE supplier
(	supplier_id	numeric(10)	not null,
	supplier_name	varchar2(50)	not null,
	contact_name	varchar2(50),
	CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

Using an ALTER TABLE statement

The syntax for creating a primary key in an ALTER TABLE statement is:

ALTER TABLE table_name

add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

For Example:

ALTER TABLE supplier

add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE supplier

add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

Drop a Primary Key

The syntax for dropping a primary key is:

ALTER TABLE table_name

drop CONSTRAINT constraint_name;

For Example:

ALTER TABLE supplier

drop CONSTRAINT supplier_pk;

In this example, we're dropping a primary key on the supplier table called supplier_pk.

Disable a Primary Key

The syntax for disabling a primary key is:

ALTER TABLE table_name

disable CONSTRAINT constraint_name;

For Example:

ALTER TABLE supplier

disable CONSTRAINT supplier_pk;

In this example, we're disabling a primary key on the supplier table called supplier_pk.

Enable a Primary Key

The syntax for enabling a primary key is:

ALTER TABLE table_name

enable CONSTRAINT constraint_name;

For Example:

ALTER TABLE supplier

enable CONSTRAINT supplier_pk;

In this example, we're enabling a primary key on the supplier table called supplier_pk.

Read More