PHP Tutorials from Scratch - Part 3 - PHP database table creation, column creation and table deletion

Yepkoo

Yepkoo
Staff member
First, we create our tables that we will use in our database.
For example, let's create a table called our members.

While creating our table, we need to add a primary key, this is the ID number of the automatically rising table, and we will need this ID number a lot.

PHP:
/*
While creating a table for our members below, we also added an ID number that automatically increases each time we add data to this table.

Now we will create a registration table for our members to register and a topics table so that our members can post a blog after registration.

*/

$connect_db->query("CREATE TABLE members (ID BIGINT(20) not null auto_increment primary key) character set UTF8 " );
$connect_db->query("CREATE TABLE blogs (ID BIGINT(20) not null auto_increment primary key) character set UTF8 " );

Secondly, we need to add our columns to these tables we created.
We can add our columns individually or in bulk

PHP:
/*
We add the necessary columns to us in the "members" table
We added 6 columns as username, userpass, username, usersurname, usermail, usertel.
*/

$connect_db->query("ALTER TABLE members ADD username VarChar(30), ADD userpass MEDIUMTEXT, ADD username VarChar(30), ADD uyesoyadi VarChar(30), ADD usermail VarChar(50), ADD usertel VarChar(20) ");

/*
We add the necessary columns to the "blogs" table
We added 5 columns named authorid, topictitle, topicdetail, topicdate, topicview.
*/

$connect_db->query("ALTER TABLE blogs ADD authorid INT(11), ADD topictitle VarChar(250), ADD topicdetail MEDIUMTEXT, ADD topicdate INT(11), ADD topicview INT(11) ");

// We can add our columns one by one instead of adding them in bulk as follows.

$connect_db->query("ALTER TABLE members ADD username VarChar(30) ");


Well, when creating the column, this is the varchar and so on. What do words do?


1- VarChar
It means any data field entry. We specify the maximum number of characters to be saved in this column by entering a number next to it.
For example, a maximum of 30 characters can be saved in the column I created by typing "username VarChar(30)".
That is, the member name cannot exceed 30 characters.
Of course, as I will show in these next lessons, we will also need to limit the character in the form boxes.
Otherwise, the page will fail when the member writes more than 30 characters. To avoid this error, we need to limit the maximum number of characters that the member can enter in the form box.

2- INT
Used for integer entries, {..., -2, -1, 0, 1, 2, ...}.
Maximum number to be recorded in this field as negative or positive (-2147483647 , ... , 2147483647)
So for numbers larger than 2 billion you should use Bigint.

3- BIGINT
It is used for integer inputs, but larger numbers than INT can be entered. {..., -2, -1, 0, 1, 2, ...}.
Maximum number to be recorded in this field as negative or positive (-9223372036854776000, ... , 9223372036854776000)

4- MEDIUMTEXT
Any data such as VarChar can be recorded. If very long sentences are to be recorded, we should create our column with MEDIUMTEXT.
For example, in the blogs table, we created our column with this value because long articles will be written in the topicdetail column.

5- LONGTEXT
Any data such as VarChar, MEDIUMTEXT can be recorded. The feature of this is that if much longer sentences are to be recorded, we should create our column with LONGTEXT.

6- TIME
If we are going to record a clock entry, we may need to use it.
Example recording format ( 08:25 )

7- DATE
Date input field
Example registration type Entered as Year, Month, day ( 2021-12-01 )

8- DOUBLE
Negative or positive numbers such as INT and BIGINT can be entered.
The difference is that you can use decimals. (eg 2.85)

9- DATETIME
It is used to create a column to record the Date and Time at the same time.
eg. (2021-12-21 13:46:22) - year-month-day hour: minute:second

The values of this column are actually more, but these are the most important and necessary ones.

I transferred all of these values to a variable, because I was going to create a lot of tables and columns, so I expedited my process of creating tables and columns with variables.

You can see the variables I assigned below.


PHP:
$lang = 'character set UTF8';

$az1 = 'BIGINT(20) not null auto_increment primary key';
$az2 = 'VarChar';
$az3 = 'LONGTEXT';
$az4 = 'INT';
$az5 = 'DATE';
$az6 = 'TIME';
$az7 = 'DOUBLE';
$az8 = 'DATETIME';
$az9 = 'BIGINT(20)';
$az10 = 'MEDIUMTEXT';

Now let's create the same table and columns using these variables Of course, this part is something I made up for convenience, it's your choice whether to use it or not.

PHP:
//--  tables

$connect_db->query("CREATE TABLE members (ID $az1 not null auto_increment primary key) $lang " );
$connect_db->query("CREATE TABLE blogs (ID $az1 not null auto_increment primary key) $lang " );

//-- columns

$connect_db->query("ALTER TABLE members ADD username $az2(30), ADD userpass $az10, ADD username $az2(30), ADD uyesoyadi $az2(30), ADD usermail $az2(50), ADD usertel $az2(20) ");

$connect_db->query("ALTER TABLE blogs ADD authorid INT(11), ADD topictitle $az2(250), ADD topicdetail $az10, ADD topicdate $az4(11), ADD topicview $az4(11) ");

If you want to perform these operations not using a PHP file, but with a SQL query directly from PHPmyadmin, you need to get only the data in quotes from these codes.


Example;

SQL:
CREATE TABLE members (ID BIGINT(20) not null auto_increment primary key) character set UTF8

Well, if we want to delete a table we created;

PHP:
//delete with php code
$connect_db->query("DROP TABLE  members" );

PHP:
//delete with sql code
DROP TABLE  members

Sometimes we may need to check if we have created this table before.
Because if you try to recreate an existing table, it may throw an error.
We can control this with a simple function.

PHP:
    // Table existence check function
    function table_check($table_name)
    {
        global $connect_db;
        try
        {
            $result = $connect_db->query("SELECT 1 FROM $table_name LIMIT 1");
        }
        catch (Exception $e)
        {
            return 'no';
        }
        return 'ok';
    }

Now we can continue with our function if the table has not been created before.

PHP:
if (table_check('members') != 'ok') {
    $connect_db->query("CREATE TABLE members (ID BIGINT(20) not null auto_increment primary key) character set UTF8 " );
}
if (table_check('blogs') != 'ok') {
  $connect_db->query("CREATE TABLE blogs (ID BIGINT(20) not null auto_increment primary key) character set UTF8 " );
}

Well, we checked the existence of the table.
However, we may also need to check the existence of the columns we have created.

The function we will use for this

PHP:
    function row_check($table_name, $row_name)
    {
        global $connect_db;
        try
        {
            $result = $connect_db->query("SELECT $row_name FROM $table_name ");
        }
        catch(PDOException $e)
        {
           return 'no';
        }
        return 'ok';
    }

Now with this function, we create our columns by checking them as follows. Of course, since we will check each column one by one, we need to do the creation process one by one.

PHP:
if (row_check('members','username') != 'ok') {
    $connect_db->query("ALTER TABLE members ADD username $az2(30) ");
    }

if (row_check('members','userpass') != 'ok') {
    $connect_db->query("ALTER TABLE members ADD userpass $az10 ");
    }

if (row_check('members','uyesoyadi') != 'ok') {
    $connect_db->query("ALTER TABLE members ADD uyesoyadi $az2(30) ");
    }

if (row_check('members','usermail') != 'ok') {
    $connect_db->query("ALTER TABLE members ADD usermail $az2(50) ");
    }

if (row_check('members','usertel') != 'ok') {
    $connect_db->query("ALTER TABLE members ADD usertel $az2(20) ");
    }

if (row_check('blogs','authorid') != 'ok') {
    $connect_db->query("ALTER TABLE blogs ADD authorid INT(11) ");
    }

if (row_check('blogs','topictitle') != 'ok') {
    $connect_db->query("ALTER TABLE blogs ADD topictitle $az2(250) ");
    }

if (row_check('blogs','topicdetail') != 'ok') {
    $connect_db->query("ALTER TABLE blogs ADD topicdetail $az10 ");
    }

if (row_check('blogs','topicdate') != 'ok') {
    $connect_db->query("ALTER TABLE blogs ADD topicdate $az4(11) ");
    }

if (row_check('blogs','topicview') != 'ok') {
    $connect_db->query("ALTER TABLE topicview $az4(11) ");
}
 
Top