How to Create Table in MySQL In PHP with MySQLi and PDO

In this tutorial about How to Create Table in MySQL database. We’re going to focus on the application of MySQL create table in the database. This is Part two of our MySQL tutorial for Beginners in 7 days.

All programming language is using a different syntax. In this section, we are going to introduce what is the Syntax for creating a table in MySQL.

Tips: A good programming habit should use the proper naming convention. For Example, if we’re going to create a table for people, we will add a prefix ‘tbl‘ to make it ‘tblpeople.’

MySQL Create Table Syntax

CREATE TABLE table_name (column_name column_type);

A table is composed of columns and rows. Before we proceed, let’s put in mind that creating a table should require the following:

• Name of the Table
• Fields
• and Definition for each field

Creating MySQL Table using Command Prompt

In this section, we will perform the MySQL Create Table Command using Command Prompt. I assume you have already the installed XAMPP.

To perform the Command to create a table in MySQL, Follow the steps below:

Step 1: Open XAMPP Control Panel.

Step 2: Make sure that MySQL is running like, as shown in the image below.

Step 3: Press “window + r” to open run program.

Step 4: Type “cmd”  and hit “enter” or click “OK” Button. (see image below)

Step 4: The Command Prompt will open. Look like, as shown below.

Step 5: We need to get inside MySQL Server. To this, follow the given steps.

1. Type CD.. to move back from your previous directory. In my case, xampp is in C:\ drive. So I have to do it twice. (like as shown in the image below)
2. Then TYPE CD XAMPP.
3. And Type CD MYSQL
4. Then Type CD Bin

5. after this step, you need to login to MySQL Server.

6. Just Type:  MySQL -u root -p and enter.

7. Enter your MariaDB password. In my case, I don’t set a password. So just simply press enter.

8. Then type “use mysqltutorial;” this is our database. (See the image below)

Step 6: Type the MySQL Create table code. And this should look like as shown in the image below.

Note: What we have done in the above image is comprise the following steps;

1. 1. We input a MySQL Query  to Create Table,
   2. Type “Show Tables” this is to ensure if our query is executed successfully.
   3. Type “describe tblpeople”, we do this to show what the structure of tblpeople table.

Congratulations you have now successfully created your first table. In this lesson, you learn how to MySQL create table using Command Prompt.

MySQL create table if not exists

Using “[IF NOT EXISTS]” for creating the table is optional, this is used to check if there’s no matching table name is found.

Here’ the Sample Syntax of Creating a table using “[IF NOT EXISTS]”

CREATE TABLE IF NOT EXISTS `tblpeople` (
  `ID` int(11) NOT NULL,
  `LNAME` varchar(30) NOT NULL,
  `FNAME` varchar(30) NOT NULL,
  `ADDRESS` varchar(50) NOT NULL
);

Creating a Table With Primary key

This lesson is on how to create a table with Primary Key. According to a source, a primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records ().

Here’ the Sample Syntax of Creating a table with Primary Key

CREATE TABLE `tblpeople` (
  `ID` int(11) NOT NULL,
  `LNAME` varchar(30) NOT NULL,
  `FNAME` varchar(30) NOT NULL,
  `ADDRESS` varchar(50) NOT NULL,
   PRIMARY KEY (ID)
)

Note: For instance, you have created already the table but forgot to ADD or DROP a PRIMARY KEY Constraint on the “ID”.

MySQL example to ADD PRIMARY KEY Constraint:

ALTER TABLE `tblpeople`
ADD PRIMARY KEY (`ID`);

MySQL example to DROP PRIMARY KEY Constraint:

ALTER TABLE `tblpeople`
DROP PRIMARY KEY (`ID`);

Creating a Table with Foreign Key Constraint

The table with a foreign key is considered as the child table. This table containing the candidate key which is called the parent table.

A Foreign key is used to connects tables together.

A Foreign key is referred to as the Primary Key in another table.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

See the two tables below:

“tblpeople”

“tblcontact”

You will observe that the “PEOPLID” column in “tblpeople” is Primary key in Table “tblpeople”.

The table “tblcontact” has a “CONTACTID” column which is the Primary key of Contact table.

Now, the “PEOPLEID” column in “tblcontact” will the Foreign Key of Contact table.

This foreign key constraint will prevent actions that would destroy the connections between the two tables.

MySQL Foreign Key on Create table

CREATE TABLE `tblcontact` (
  `CONTACTID` int(11) NOT NULL,
  `PEOPLEID` int(11) NOT NULL,
  `CONTACTTYPE` varchar(50) NOT NULL,
  `CONTACTINFO` varchar(50) NOT NULL,
  PRIMARY KEY (CONTACTID),
  FOREIGN KEY (PEOPLEID) REFERENCES TBLPEOPLE(PEOPLEID)
);

MySQL Foreign Key on Alter Table

ALTER TABLE tblcontact
ADD FOREIGN KEY (PEOPLEID) REFERENCES TBLPEOPLE(PEOPLEID);

Drop Foreign Key on MySQL

ALTER TABLE tblcontact
DROP FOREIGN KEY PEOPLEID;

MySQL Autoincrement, Default Value and NOT NULL

In this section, we will be using AUTOINCREMENT FEILD and Default Value to a column when creating a table in MySQL.

• AUTOINCREMENT FIELD – using autoincrement, it will allow you to automatically generate a unique number when a new record is inserted to a table.

Note: often time the primary key column is used for autoincrement.

• Default Constraint –  It is used to give a default value for a column. This will be added when a new record inserted has no specified value.
• NOT NULL – we use this because we do not want this field to be NULL. Meaning it will require the user to put some values to it as long as it will not be left as blank.

MySQL Query using AUTO_INCREMENT

CREATE TABLE `tblcontact` (
  `CONTACTID` int(11) NOT NULL AUTO_INCREMENT,
  `PEOPLEID` int(11) NOT NULL,
  `CONTACTTYPE` varchar(50) NOT NULL,
  `CONTACTINFO` varchar(50) NOT NULL
);

MySQL Query using DEFAULT VALUE

CREATE TABLE `tblcontact` (
  `CONTACTID` int(11) NOT NULL,
  `PEOPLEID` int(11) NOT NULL,
  `CONTACTTYPE` varchar(50) NOT NULL  DEFAULT 'EMAIL',
  `CONTACTINFO` varchar(50) NOT NULL DEFAULT 'NONE'
);

Creating a Table Using MySQLi(OOP), MySQLi and PDO

This time we will now apply the knowledge we acquire while ago. In this section, our learning will be put into actions to make it as our Best Practices.

NOTE: PHP script will use in this exercise. With this, you will be able to make your own Best PHP Projects for your clients.

Creating a MySQL Table Using PHP Script and MySQLi (Object-Oriented):

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqltutorial";

// Create connection
$conn = new mysqli($server, $username, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// sql to create table
$sql = "CREATE TABLE `tblpeople` (
`PEOPLEID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`LNAME` VARCHAR( 30 ) NOT NULL ,
`FNAME` VARCHAR( 30 ) NOT NULL ,
`ADDRESS` VARCHAR( 50 ) NOT NULL
) ENGINE = INNODB;";

if ($conn->query($sql) === TRUE) {
echo "Table tblpeople created successfully";
} else {
echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

Example Using PHP Script and MySQLi (Procedural):

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqltutorial";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// sql to create table
$sql = "CREATE TABLE `tblpeople` (
`PEOPLEID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`LNAME` VARCHAR( 30 ) NOT NULL ,
`FNAME` VARCHAR( 30 ) NOT NULL ,
`ADDRESS` VARCHAR( 50 ) NOT NULL
) ENGINE = INNODB;";

if (mysqli_query($conn, $sql)) {
echo "Table tblpeople created successfully";
} else {
echo "Error creating table: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

Creating a MySQL Table Using PHP SCRIPT with PDO:

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "mysqltutorial";

try {
$conn = new PDO("mysql:host=$server;dbname=$database", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// sql to create table
$sql = "CREATE TABLE `tblpeople` (
`PEOPLEID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`LNAME` VARCHAR( 30 ) NOT NULL ,
`FNAME` VARCHAR( 30 ) NOT NULL ,
`ADDRESS` VARCHAR( 50 ) NOT NULL
) ENGINE = INNODB;";

// use exec() because no results are returned
$conn->exec($sql);
echo "Table tblpeople created successfully";
}
catch(PDOException $e)
{
echo $sql . "
" . $e->getMessage();
}

$conn = null;
?>

Conclusion

Being a programmer is not easy, you have to invest more time to gain knowledge.

I believe, Programming is lifelong learning.

The knowledge from this material will still remain as knowledge unless you put this into Best practices.

*So, what do you think about the new learning?

Please leave a comment below.