MySQL Tables

MySQL tables neatly store data in rows and columns

In the previous article, we covered how to create a database and establish a connection to it. It’s time to go a step deeper now. It’s time to create some tables. If you remember the previous analogy, the database can be thought of as an Excel Workbook and the tables can be thought of as sheets within the Workbook.

https://blog.devgenius.io/php-p83-mysql-db-connection-8a34c4056863

Tables are part art part science. Architects must decide how far they want to normalize a database without losing readability. We’ll cover the basics of tables in this article and will cover normalizing data in the next one.

Creating Your First Table

We’ll start off with the phpMyAdmin approach. Visit phpMyAdmin. For me it’s http://0.0.0.0/phpmyadmin. Select your database and create your table. The first table that we’ll create is going to store book author information. We’ll name the table authors. It’s plural since it will hold multiple authors. We’re going to want to store the author’s first name, last name, and email address. We’re also going to store a unique identifier, called id. Why do we need this? Imagine if people didn’t have Social Security Numbers. The IRS would not know which John Smith just submitted their taxes. On a side note, that might not be a bad idea. The id will be unique for each person. So, if there are 100 John Smith authors in the authors table, we’ll know which one is which.

Something that’s standard across most tables are the updated_at and created_at fields. These are timestamps that show when the author is created and when the last time the entry was updated. If we change the last name of a particular author, the created_at field will stay the same but the updated_at field will be modified.

We’ll need 6 fields to store:

  • id
  • first_name
  • last_name
  • email
  • updated_at
  • created_at

You’ll be presented with a ton of information to fill out. There is some standardization to this, but it’s really up to you how you choose to fill it out. You will need to understand data types that are present. For example, if you choose INT as the data type for the first_name field, you won’t be able to store anything other than integer values. So that’s not a good data type for it. A VARCHAR would be a lot better for something like first_name.

For the time being, let’s just fill these fields in like this.

The id field is:

  • an INT datatype meaning that it will be an integer, which is what we want.
  • UNSIGNED since we don’t want negative integer values in this instance.
  • PRIMARY since we want this to be our primary key. We’ll cover primary and foreign keys later, but a primary key must be unique. We can’t have two of the same integer values.
  • A.I. or Auto Increment. We want to automatically add 1 to an ID when we insert a new record. If the first record was 1 then the next record will be 2.

The first_namelast_name, and email fields are:

  • VARCHAR datatypes
  • Have a max character length of 255 characters

The updated_at field is:

  • TIMESTAMP which has the following format: YYYY-MM-DD HH:MM:SS. An example timestamp looks like this: 2022–01–01 00:00:01
  • Its default is set to CURRENT_TIMESTAMP, which means that when you add the a record to the database, it will automatically insert the timestamp.
  • It also has the on update CURRENT_TIMESTAMP attribute. That just states that each time this record is updated, like the email changed, the timestamp will be updated.

The created_at field is exactly the same as the updated_at field with the exception of the on update CURRENT_TIMESTAMP. It doesn’t make sense to update the timestamp for this field since we just want to know when this record was actually created.

Click the Save button at the bottom to create your new table.

You can insert data from phpMyAdmin automatically by clicking on the Insert button. Type in the first_name, last_name, and email to add the data. It’s that simple. But, we want to do this programmatically and we will in the next article.

Creating a table programmatically

You know we were going to get here. Let’s create a table through PHP. In the previous article we created a database programmatically. We can easily achieve the same thing with tables.

Let’s create a new table called users, that has the same fields as our authors table, first_namelast_nameemail.

CREATE TABLE users (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(50),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
               ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
<?php
$server   = "localhost";
$username = "admin";
$password = "E9bIyRDwzidT";
$database = "dino_test_db";

$connection = new mysqli( $server, $username, $password, $database );

if ( $connection->connect_error ) {
    die( $connection->connect_error );
}

$sql = "CREATE TABLE users (
            id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            first_name VARCHAR(255),
            last_name VARCHAR(255),
            email VARCHAR(255),
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                       ON UPDATE CURRENT_TIMESTAMP,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($connection->query($sql) === TRUE) {
    echo "Table users created successfully";
} else {
    echo $connection->error;
}

echo "Connection established successfully";

We create our SQL script and pass it to our query method within our instantiated mysqli object. If everything worked, and it did, it creates the table. You can verify whether it was created by revisiting phpMyAdmin and taking a look.

And it’s there. That’s it for now. See you next time when we actually start adding data to these table. You’ll want to get acquainted with the various data types in MySQL. You can get the full list here:

https://dev.mysql.com/doc/refman/8.0/en/data-types.html?source=post_page—–83b203d28ff7

https://github.com/dinocajic/php-youtube-tutorials?source=post_page—–83b203d28ff7

MySql DB Connection

MYSQL CONNECTION: BUILDING BRIDGES TO DATA DREAMS

PHP – P83: MYSQL DB CONNECTION

The first step to reading or inserting data is to establish a connection to your server/database. It’s a fairly straightforward process and it’s something that doesn’t change.

MySQL Tables

MySQL tables neatly store data in rows and columns

PHP – P84: mysql tables

It’s time to create some tables. If you remember the previous analogy, the database can be thought of as an Excel Workbook and the tables can be thought of as sheets within the Workbook.

MySQL Insert

INFUSING TABLES WITH FRESH DATA ENERGY

PHP – P85: MYSQL INSERT

When you have a database connection setup, the next logical step is to insert data into the tables. You handle that with the MySQL insert command.

Leave a Reply