Work With Tables in PHP

Introduction

This article shows you how to code Data Definition Language (DDL) statements that work with tables of a database.

Create a table through PHP

You can use a simple CREATE TABLE statement to create a table.

You can write a database name after the CREATE TABLE statement and the new table will be stored in the specifird database. You can qualify the table name with the database name after it.

For example:

CREATE TABLE databasename.tablename

The following syntax shows how to create a table in a specific database. The create table statements consists of the name of  the new table followed by the names and data types of its columns.

Syntax of the CREATE TABLE statement

CREATE TABLE [database_name] table_name ( Column_name1 data_type [column_attributes], Column_name2 data_type [column_attributes]...........................,table level constraints

Creates a table without columns attributes

The following example creates a table with the name "emp_data", that has the two columns "emp_id" (int type) and "emp_address" (varchar type).

CREATE TABLE emp_data
(
emp_id INT,
emp_address VARCHAR(50)
)

Creating a table without columns attributes

The following is PHP code for creating a table without columns attributes:

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
$qry = "CREATE TABLE emp_data
(
emp_id INT,
emp_address VARCHAR(50)
)";
mysql_query($qry,$con);
mysql_close($con);
?>

Description of table emp_data

The following image shows specification of a description of the table "emp_data":

 

Creates a table with columns attributes

The following example specifies how to create a table with columns attributes. The code snippet creates a table with the name vendor_data. This table's columns do not accept null values; to do that you can use the NOT NULL attribute. The AUTO_INCREMNET attribute can only be specified for one column in a table, and that column must be defined as either the "primary" key or "unique" key. When you define a column with the AUTO_INCREMENT attribute, MySQL automatically generates the next number in the sequence for the column if you do not specify a value; by default MySQL starts the number with one but you can start with a value other than one (1).

CREATE TABLE vendor_data
(
vendor_id INT NOT NULL UNIQUE  AUTO_INCREMENT,
vendor_address VARCHAR(50)
)

Creating a table with columns attributes

The following is PHP code for creating a table with columns attributes:

<?php

$con = mysql_connect("localhost","root","");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("mysql", $con);

$qry = "CREATE TABLE vendor_data

(

vendor_id INT NOT NULL UNIQUE  AUTO_INCREMENT,

vendor_address VARCHAR(50)

)";

mysql_query($qry,$con);

mysql_close($con);

?>

Description of table vendor_data

The following image shows specification of a description of the table "vendor_data":

 

Create a table with primary key constraint

The purpose of constraints with tables is to restrict the type of data that can be stored in a column. For example, the NOT NULL keywords prevents null values from being stored in a column. The UNIQUE keyword allows a unique value for the column. Now we are concerned with primary key constraints, the easiest way to define a primary key is to code the PRIMARY KEY keywords, after the data type of the column name. The Primary key constraint is the same as a unique key constraint, but it additionally does not allow a null value.

Syntax Column Level Primary key syntax

column_name data_type PRIMARY KEY column_attribute

Example

This example shows a column level constraint. The first column is "login_dtl" with a primary key with the auto_increment  attribute. The second column is forced to contain a unique value for each record.

CREATE TABLE login_dtl
(
u_id INT PRIMARY KEY AUTO_INCREMENT,
pwd VARCHAR(12) NOT NULL UNIQUE
)

Description of table login_dtl

The following image shows specification of a description of the table "login_dtl":



Syntax Table Level Primary key syntax

[CONTRAINT [constraint_name]] PRIMARY KEY [column_name 1, column_name2,.................]

Example

When you define a constraint at the table level, use the CONSTRAINT keyword. The table level is better than a column level constraint, it can refer to multiple columns in the table. As a result, if you need multiple columns, you must use the table level constraint. The following example shows how to use a table level constraint. For example to create the primary key for the registration_dtl table, I wrote the code "CONSTRAINT mul_pk PRIMARY KEY(u_id ,email_id )". With that code the "u_id" and "email_id" columns are treated as a primary key. See:

CREATE TABLE  registration_dtl
(
u_id  INT  AUTO_INCREMENT,
pwd VARCHAR(20)  NOT NULL,
email_id VARCHAR(50) NOT NULL,
dob date,
address varchar(100),
CONSTRAINT mul_pk PRIMARY KEY(u_id ,email_id )
)

Description of the table "registration_dtl"

The following image shows specification of a description of the table "registration_dtl":



Summary

The CREATE TABLE statement creates a table based on column names, data types, and attributes that you specify. The primary key constraint requires that each row has a unique value for the column or columns for the primary key, and it does not allow null values.