MySQL Create Tables In PHP with MySQLi and PDO

    3
    1785

    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.

    How to Create Table in MySQL 2019 Best Practices
    How to Create Table in MySQL 2019 Best Practices

    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.

    Xampp MySQL Create table
    This is the view of running MSQL

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

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

    open run program
    open run program

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

    Command Prompt window
    Command Prompt window

    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
    change directory
    change directory

    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)

    mariaDB monitor
    MariaDB monitor

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

    mysql create table query
    MySQL create table query

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

      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]”

    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

    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:

    MySQL example to DROP PRIMARY KEY Constraint:

    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”
    example mysql create table
    tblpeople table
    “tblcontact”
    sample mysql create table
    tblcontact table

    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

    MySQL Foreign Key on Alter Table

    Drop Foreign Key on MySQL

    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

    MySQL Query using DEFAULT VALUE

    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):

    Example Using PHP Script and MySQLi (Procedural):

    Creating a MySQL Table Using PHP SCRIPT with PDO:

    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.

    Looking for more source code? Type your keyword here here!

    3 COMMENTS

    1. In this article I learned how to create database Using PHP Script and MySQLi and I have now idea how to Create a MySQL Table Using PHP SCRIPT with PDO.

    2. This article help me to further understand the difference the three ways of creating database tables i also identify which one is the easiest for me.

    3. Great article which helps me understanding and learning MySQL. I want to proceed with part three but I don’t see the link to this page. Can you help me.

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here

    This site uses Akismet to reduce spam. Learn how your comment data is processed.