Creating a table

The Table creation in MySQL involves:

• Table Name
• Field Names
• Definition of each field

Syntax:

The generic SQL syntax to create a MySQL table:

CREATE TABLE table_name (column_name column_type);

Here is an example table we are creating named book_tbl

Example

CREATE TABLE book_tbl(
   book_id INT NOT NULL AUTO_INCREMENT,
   book_title VARCHAR(100) NOT NULL,
   book_author VARCHAR(40) NOT NULL,
   book_submission_date DATE,
   PRIMARY KEY  (book_id)
);

Here few items need explanation:

Field Attribute NOT NULL means the field should not be NULL. So if the record is created with NULL value, the MySQL will raise error. Field Attribute AUTO_INCREMENT will allow increment the id field.Keyword PRIMARY KEY is used to define a column as primary key.

There are heaps of other options related to creating a table, which can be found browsing to this link 

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

Let's create book_tbl using a PHP script 

Create table using PHP PDO

Here is an example of creating book_tbl under database testDB

<?php
$server = "localhost";
$user = "username";
$pwd = "password";

try {
    $connection = new PDO("mysql:host=$server;dbname=testDB", $user, $pwd);
    // PDO can throw exceptions rather than Fatal errors, so let's change the error mode to exception
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE TABLE book_tbl(
              book_id INT NOT NULL AUTO_INCREMENT,
              book_title VARCHAR(100) NOT NULL,
              book_author VARCHAR(40) NOT NULL,
              book_submission_date DATE,
              PRIMARY KEY  (book_id)
              );";
    // We can execute the above statement using exec() method from the connection class
    $connection->exec($sql);
    echo "Table -> book_tbl under testDB was created successfully";
    }
    catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>

Remember that PDO will throw an exception if user you are using to connect and create a table does NOT have Create_priv

Dropping a table

Drop Table using mysql binary:

In MySQL table can be dropped by using below command.

Syntax:

DROP TABLE table_name ;

Let's now drop our previously created table book_tbl

Drop table using PHP PDO

Here is an example of dropping book_tbl under database testDB

<?php
$server = "localhost";
$user = "username";
$pwd = "password";

try {
    $connection = new PDO("mysql:host=$server;dbname=testDB", $user, $pwd);
    // PDO can throw exceptions rather than Fatal errors, so let's change the error mode to exception
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "DROP TABLE book_tbl;";
    // We can execute the above statement using exec() method from the connection class
    $connection->exec($sql);
    echo "Table -> book_tbl under testDB was dropped successfully";
    }
    catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
?>

Once again, you would need Drop_priv assigned to the user connecting to your database to perform this action without error.

In next part of this tutorial, we will explore inserting data to our book_tbl