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_name
, last_name
, and email
fields are:
VARCHAR
datatypes- Have a max character length of
255
characters
The updated_at
field is:
- a
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_name
, last_name
, email
.
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 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 neatly store data in rows and columns
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.
INFUSING TABLES WITH FRESH DATA ENERGY
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.