Oj Obasi
Oj Obasi

I am also and will continue to be a student of web design and development. My tutorials may not be for you. If you feel that way, then search online for better tutorials.

Create a MySQL Table Using PDO

In this tutorial, we want to create a table call 'products' with five columns to store our product information for our eCommerce website. The columns are: id, product_name, category, price, date_added.

First, we need to establish a connection to our MySQL database http://www.simplecss3.com/tutorial/66/php-pdo-connect-to-mysql/.
I have a script for my database connect called 'database.php'. What I need to do is include it with a require 'database.php' statement.

To create a MySQL table in php, we have to use the special statement 'CREATE TABLE' followed by the name of the table we are creating.

<?php
require 'database.php';
try{
// sql to create table
$sql = "CREATE TABLE products (
id INT(11) NOT NULL AUTO_INCREMENT,
product_name VARCHAR(200) NOT NULL,
category VARCHAR(50) NOT NULL,
price VARCHAR(50) NOT NULL,
date_added DATETIME NOT NULL,
PRIMARY KEY (id)
)";
$db->exec($sql);
echo "Table products created successfully";
}
catch (Exception $e) {
echo "Table products not created.";
exit;
}
?>

I wrap my CREATE TABLE code in a pdo try {} and catch {} block. With this, if the code fails, I will get a message saying "Table products not created" otherwise my "Table products created successfully".

SQL DATETYPES:

You must have noticed INT, VARCHAR, DATETIME. These are known as datatypes. It specifies what kind of data a column can hold.

After the datatypes we have NOT NULL, which basically says that column must have a value.

AUTO INCREMENT - For each product we will add to our table , there will be a new record that increases by 1.
PRIMARY KEY - Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT. This is the unique number that we use to identify each product.