Did you know?

if you register at Hamid School you can save up to 45% on hosting packages offered by Surreyhost.com .

Don't miss out, every minute counts!

Register now!

Are you Confused? Click here to learn how to use hamid school

PHP MySQL Create Database and Tables

A database holds one or multiple tables.

Create a Database

The CREATE DATABASE statement is used to create a database in MySQL.

Syntax

CREATE DATABASE database_name

Example

In the following example we create a database called "my_db":

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

mysql_close($con);
?>

Create a Table

The CREATE TABLE statement is used to create a database table in MySQL.

Syntax

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
.......
)

We must add the CREATE TABLE statement to the mysql_query() function to execute the command.

Example

The following example shows how you can create a table named "person", with three columns. The column names will be "FirstName", "LastName" and "Age":

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

// Create database
if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

// Create table in my_db database
mysql_select_db("my_db", $con);
$sql = "CREATE TABLE person 
(
FirstName varchar(15),
LastName varchar(15),
Age int
)";
mysql_query($sql,$con);

mysql_close($con);
?>

Important: A database must be selected before a table can be created. The database is selected with the mysql_select_db() function.

Note: When you create a database field of type varchar, you must specify the maximum length of the field, e.g. varchar(15).

MySQL Data Types

Below are the different MySQL data types that can be used:

Primary Keys and Auto Increment Fields

Each table should have a primary key field.

A primary key is used to uniquely identify the rows in a table. Each primary key value must be unique within the table. Furthermore, the primary key field cannot be null because the database engine requires a value to locate the record.

The primary key field is always indexed. There is no exception to this rule! You must index the primary key field so the database engine can quickly locate rows based on the key's value.

The following example sets the personID field as the primary key field. The primary key field is often an ID number, and is often used with the AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field by 1 each time a new record is added. To ensure that the primary key field cannot be null, we must add the NOT NULL setting to the field.

Example

$sql = "CREATE TABLE person 
(
personID int NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(personID),
FirstName varchar(15),
LastName varchar(15),
Age int
)";

mysql_query($sql,$con);

• Introduction to PHP
• PHP Installation
• PHP Syntax
• PHP Variables
• PHP String
• PHP Operators
• PHP If...Else Statements
• PHP Switch Statement
• PHP Arrays
• PHP Looping
• PHP Functions
• PHP Forms and User Input
• PHP $_GET
• PHP $_POST
• PHP Date
• PHP Include File
• PHP File Handling
• PHP File Upload
• PHP Cookies
• PHP Sessions
• PHP Sending E-mails
• PHP Secure E-mails
• PHP Error Handling
• PHP Exception Handling
• PHP Filter
• PHP MySQL Introduction
• PHP MySQL Connect to a Database
• PHP MySQL Insert Into
• PHP MySQL Select
• PHP MySQL The Where Clause
• PHP MySQL Order By Keyword
• PHP MySQL Update
• PHP MySQL Delete From
• PHP Database ODBC

Did you know?

if you register at Hamid School you can learn web programming and designing for free and also you can save up to 45% on hosting packages offered by Surreyhost.com .

Don't miss out, every minute counts!

Register now!