Advanced PHP Creating Databases & Tables

Hello again, and welcome back to Educator.com's Advanced PHP with MySQL course.0000

In today's lesson, we are going to be learning how to create databases and tables.0005

We are going to learn about specifically creating a database, using what is called the CREATE DATABASE command.0011

And as we learned in a previous lesson, the MySQL server, the way it is set up, contains a number of different databases,0017

each of which can contain a number of different tables.0023

We are going to learn how to create a database for our store web application.0025

We are going to learn about the CREATE TABLE command, which, as the name implies,0030

allows you to create a table within a particular database on the MySQL server.0032

We are going to learn in a little more detail about SQL data types, which are data types for the columns in a table that we will be creating.0038

As we learned before, in relational databases, data is organized in rows and columns.0046

And for each particular column, you can have data values, and they must correspond to a particular data type.0051

We are going to talk about something called column attributes, which in addition to providing a data type0057

for a particular column in a table that we are creating...we can also specify some particular features of that column.0062

We are going to learn how to create the command used to create the Items table in our database,0069

which we are going to be using for our web store application, to hold all of the information about the items in our store.0074

We are going to go over a couple useful commands that can help you to find out0079

about the structure of your database and the structure of tables that you have created.0083

And then, we are going to go over another required homework that you are going to need to do to set up this Items table,0087

so that we can connect to it from our web application.0097

A database is created in SQL using the CREATE DATABASE SQL command.0103

It simply is CREATE DATABASE, followed by the name of the database that you want to create, as a parameter; it is a very simple command.0110

And then, MySQL also provides a couple of non-SQL commands that allow you to provide some information about the MySQL server.0119

When you log into the MySQL server, you can type, for example, SHOW DATABASES.0128

And that is going to list all of the databases that currently exist on that MySQL server that your user has access to.0132

And that is a sort of a privilege thing.0138

Now, we are going to be connecting, in this lesson and all of the other lessons, as the phpuser, which is the account we created in the last lesson.0140

And because they have all privileges, we are going to be able to see all of the databases within the particular MySQL server we are connecting to.0153

There is also the USE command that you can issue that you type in at the MySQL Command Prompt that says,0161

"Use" and you specify the name of a particular database that you want to use.0169

What that does is tells the MySQL Monitor that any future commands you issue are going to be issued on that particular database.0172

For example, if you are inserting data into a database, you won't have to specify (as we will learn,0179

when we learn about inserted statements) both the name of the database and the table you want to insert data to.0186

You will just be able to specify the name of the table, because USE is already going to let MySQL know you are using a particular database.0192

What we are going to do is create our database that we are going to use for our web application, or web store.0199

We are going to call the database advanced_php.0205

And so, what I am going to do is bring up the MySQL Monitor program, which we learned about.0214

And the way you do that, again, is by opening the Windows Command Prompt, and then using this CD command0219

to change to the directory where that mysql.exe file is located (which is the MySQL Monitor client program).0227

And then, we are going to connect as this phpuser account we have created.0246

We type mysql; we don't need to specify the hostname, because we are going to default to the localhost;0251

we are going to be connecting to the MySQL server on the localhost.0256

We are going to be logging in as user phpuser, and we are going to ask it to prompt us for a password.0260

I'm going to go ahead and enter the password that we had set up last time; for this example, we had set up Educator as the password.0269

So now, we can see that we have our MySQL prompt; it shows that we have been able to successfully log in.0276

The first thing we do is run that SHOW DATABASES command that I told you about.0281

And what that is going to do is list all of the different databases that are available on this current MySQL server.0286

We can see that there a number of different databases.0293

Even though we haven't actually created anything, the MySQL database itself comes with its own databases already built--0295

for example, the mysql database; and then, XAMPP does some other things to set up some other databases0307

(for example, some example databases; there is a test database, and so forth).0315

Those are the databases that are currently on this server.0319

Now, we are going to create our advanced_php database: we type CREATE DATABASE0322

and the name that we want to give to it (which is advanced_php), followed by a semicolon, and hit Enter.0328

It is going to say 'query OK' to let us know that the command was successfully run.0335

And then, one thing you can do is use the up and down arrow keys when you are on the MySQL prompt.0340

And it is going to let you scroll through previously entered commands, which can save you some typing.0347

So, because we typed SHOW DATABASES as our last command, if I hit the up arrow once,0350

it is going to go ahead and populate the command prompt with SHOW DATABASES.0354

So now, when I enter it (oops, it looks like I entered a typo; I typed a 3 by accident), SHOW DATABASES,0357

we can see all of the databases that we had before; but now, we also can see our advanced_php database.0370

So now, any of the future commands we are going to be using, we want to operate on this advanced_php database.0377

So, we are going to type USE advanced_php, and that is going to tell this Monitor client that we are going to be using this database,0384

because in a second, we are going to be creating a table in this particular database.0393

It is going to let you know that the database has changed; you are currently using advanced_php.0397

SQL has a command called the CREATE TABLE command, which is what allows you to create database tables.0405

What it does is takes a comma-delimited list of column definitions.0411

You define the columns that you want in your particular table.0417

The command is CREATE TABLE--those are the keywords.0420

And then, you follow it by the name that you want to give the table--for example, you might call it Users.0424

Or for what we are going to be doing in this lesson, we are going to be creating a table called Items to store items' information.0429

And then, you have this comma-delimited list of however many columns you want to create.0435

And what you do is list the name of the column you want to create;0440

you provide the data type of the column; and then you can provide some additional attributes that we are going to talk about0443

to just further define how that column is going to work in the database.0450

And you separate all of the different column definitions by commas.0454

Data types--we talked about that a little in our introduction to databases lesson:0461

SQL provides some data types for data that you store in relational database tables.0463

We are going to specifically talk about some of the specific data types now.0469

There are a couple of numeric data types that we are going to be using.0474

First of all, INT is a data type that represents an integer, just like the int data type in PHP.0478

And what you can do is specify INT, which will allow you to do positive and negative integers.0487

You can also specify as a data type INT UNSIGNED, and that is saying you want an unsigned integer,0491

which means an integer that only holds positive values.0498

We are going to be using that for our item ID's and all of our ID columns in all of our tables.0501

You can also specify a floating-point number using this decimal data type.0507

And what you do is specify the keyword DECIMAL, followed by parentheses, and then followed by two digits.0513

And one we are going to call m, comma, and then the other one, d.0518

What m does is specifies the maximum number of digits that this floating-point number can represent.0523

And then, d is going to represent the number of digits after the decimal point.0528

For example, if we create a column--let's say we create a column (which is what we are going to be doing) called price;0532

and so, we have the name of the column and then data type, and then we specify that we want it to be0541

a maximum of three digits, with two after the decimal place,0548

which means it can only hold numbers of the form x.yz, where it has three total digits, two of which come after the decimal point.0551

We are going to be using that to hold the price of our items in our store.0560

Then, there are a couple of string data types that we are going to be using.0564

The first one is called a CHAR, and it is followed by parentheses with an M parameter.0567

And M is a number that allows you to specify...a CHAR data type says you are creating a fixed-length string;0574

so if you give this data type to a particular column, and you say CHAR(3), what you are saying0585

is that this column that you are using this data type for is going to be a string that is of a fixed length, and it is going to be 3 characters long.0594

An alternative to that is VARCHAR, and what that is: it is a variable-length string that can be up to M characters in length (that you specify).0604

So, you can say VARCHAR(10), which means that this column in this particular table that has this data type0614

can hold a string of variable length, up to 10 characters.0623

There is also the TEXT data type, which is a data type that allows you to store large amounts of string data.0628

And we are going to be using this, for example, for our product description.0637

You might have a product description, maybe, that is a couple of paragraphs long.0639

Well, instead of specifying a VARCHAR to say that this is going to be a variable-length string up to so many digits,0643

you can use something like TEXT, which has a predefined limit to it, but it is very large.0652

And that is, again, for storing large amounts of string data.0656

As mentioned in our CREATE TABLE SQL command, we specify the name of the column, the data type0662

(we just went over the data type), and--there are also some additional attributes you can specify for a particular column.0669

For example, you can specify the attribute called PRIMARY KEY.0675

What that does is: when you specify that in a column's definition, it denotes that that column is the primary key of the table.0679

And when you do this, one thing to note is that if you are defining multiple columns in your table, only one column can have this PRIMARY KEY attribute specified.0685

Another attribute that you can use, which we are going to be using, is called AUTO_INCREMENT.0695

And what that does is: it can be used to apply to integer and floating-point columns--numeric-type columns.0700

And what it does is: when you specify that column as AUTO_INCREMENT, what PHP will do is:0709

when you insert a new row into a table, for whatever column you have specified AUTO_INCREMENT,0716

it is going to look at the largest-value integer or numeric value that is in the table for that particular column,0722

and it is just going to increment it by 1; and it is going to generate that0728

as the new number for that particular column, for that particular row.0730

For example, let's say we have an item in the store, 1001; these are in a kind of tabular format.0738

It is called item1; maybe it is 15 dollars; well, with AUTO_INCREMENT, when you add a new item to the table,0748

it is going to go ahead and look at the largest item...0756

assuming this was the AUTO_INCREMENT column, so it has this AUTO_INCREMENT specifier specified in the CREATE TABLE command,0759

so this is an AUTO_INCREMENT column, when we add a new item to this table,0767

it is going to look and see, "OK, 1001 was the last item added to the table."0770

When we insert this new item, maybe we will specify that it is called item2 that costs 25 dollars;0775

we are just going to increment this by 1, and now it is going to be given the item ID 1002.0781

Now, what we are going to be doing is going over the command that we are going to use to create our table.0790

We are going to put this CREATE TABLE command into action, and we are going to use it to create a table called Items in our database,0793

which we are going to use to hold the items in our particular store.0801

It is going to contain 5 columns in the table.0805

So you can see here, we have 5 column definitions in this CREATE TABLE statement.0808

And I forgot to mention that actually, after you write CREATE TABLE and the command, and then the name of the table,0813

you have a set of parentheses in which you enclose all of the different column definitions.0819

We have five different columns we are creating.0824

The first one we are going to call itemID, which is going to represent the item ID of our column.0827

We are going to have it be an unsigned integer; that is going to be the data type.0831

We are going to have it be auto-incremented; so that means, every time we add an item to our database, it is going to automatically increment that ID.0836

So, it is going to generate it automatically for us.0843

And then, we are going to note that that is going to be the primary key column of the table.0847

Because item ID's are going to be something that is unique (we can't have two items in our store with the same item ID,0851

because then you wouldn't have a way of identifying them), we are going to use this column as our primary key.0858

Again, a primary key column is a column in which you can only have one row in the table that has a particular value.0862

You can't have multiple rows with the same value.0869

In this case, we are not going to be able to have multiple rows in the table with the same item ID.0872

The next column we are going to create is going to be called name.0878

We are going to give it the data type VARCHAR, so it is going to be a variable-length string;0880

and we are going to allow it to be up to 25 characters in length.0884

We are going to create a price column, and we are going to let it be a floating-point number that can be 7 digits long;0888

and it is going to have two digits after the decimal place.0895

So, we can have a number that looks like this, with 5 digits before the decimal point and two digits after.0898

We are going to use that to hold our price.0906

We are going to have a description column, and we are going to set its data type equal to TEXT.0908

And that is going to allow us to store large amounts of text information about the description for our item.0913

And then, we are going to create an image file extension column.0920

And we are going to use the fixed-length string data type, called CHAR, and we are going to say, "Make it a fixed-length string of length 3."0924

So, that means that we can only enter image file extensions that have length 3,0932

which makes sense, because we will be entering things like jpg, gif, and png, for example,0937

which are file extensions that are three characters in length.0944

One thing to note is that, when you create this table using the CREATE TABLE command,0950

the order in which you list these column definitions within the CREATE TABLE command0956

is the order in which they are going to be created in the table.0965

For example, the structure of our table is going to look like this: we will have a table called Items,0967

and it is going to have a number of different columns.0976

And essentially, they are going to have an automatic order to them, and the order is going to be this--the way we specified them.0979

And this is going to become important when we start inserting data into our database,0992

because there is a shortcut that you can do for inserting data, where you can just specify the values1000

for all of the different columns, without saying, "I want this to be the item ID and this to be the name."1006

And so, the order that you put them in is going to be dependent on this order in which the columns are defined within the table.1010

Before we go ahead and create that Items table, what we are going to do is:1025

I am going to tell you about two other useful commands.1030

One is called SHOW TABLES, which--after you have selected a database, using the USE command in the MySQL Monitor,1032

you can type SHOW TABLES, and what that is going to do is allow you to list all of the tables that currently exist in that particular database.1039

If we go back to our MySQL Monitor, we are using the advanced_php database that we have just created, so it shouldn't have any tables in it.1047

So, when we type SHOW TABLES, it is going to say 'empty set,' meaning that there are no tables.1053

There is also a DESCRIBE statement that we are going to go over after we create our Items table1062

that allows you to see the structure of a particular table.1067

You type DESCRIBE, and then the name of the table that you want to see the structure of.1069

And we will be running that on Items.1076

Let's go ahead and create that Items table using the CREATE TABLE statement.1080

We are going to say CREATE TABLE--create a table called Items; we are going to have an open parentheses.1087

Our first column is going to be called item ID; it is going to be an unsigned integer (that is going to be the data type).1097

It is going to have the attributes AUTO_INCREMENT and PRIMARY KEY.1106

One thing I forgot to mention is that, when you specify the attributes, you simply separate them by spaces.1111

We are going to say item ID is an unsigned integer that is going to be auto-incremented, and this is going to be the primary key column of the table.1118

We are going to enter a comma, which is going to say, "We are done with this column definition; let's create the next column,1126

which is going to be name," which we decided was going to be a variable-length string of 25 maximum characters.1131

Again, we enter a comma to start a new column definition.1139

We are going to have price, which is going to be a decimal number that can be 7 digits long, with 2 digits after the decimal place.1143

We are going to have a description column of the text data type, which is going to allow us to store a large amount of string data.1152

And then, we are going to create the image file extension--imageFileExt--column.1159

And we are going to have that be a fixed-length string of length 3.1166

And then, what we do is finish all this by having a closing parentheses, and then a semicolon to go ahead and issue the command.1170

And when we hit Enter, it is going to let us know (assuming it was typed correctly) that the query was OK,1179

which means that the command ran successfully and the table should be created.1185

So now, when we type SHOW TABLES, we should see that this table Items has been created.1188

And sure enough, in the advanced_php database, there is a table called Items.1193

Now, if we want to make sure that the structure of the table that we just created is how we want it to be,1199

we can write DESCRIBE Items, followed by a semicolon.1203

And we can see that it outputs a table that has information about that table Items.1207

We can see that it contains an item ID with an unsigned integer as a data type.1212

It is a primary key, and it has this extra attribute called AUTO_INCREMENT.1217

There is a bunch of other information here that we are not going to really get into,1222

but it does provide other information about the table.1225

It has a name column that can be 25 characters in length.1230

It has a price column, a description column, and an image file extension column.1235

And the thing to note is that the order: this table has 5 columns, and they are in the order itemID, name, price, description, imageFileExt.1238

We have another required homework for this lesson, and that is because it is going to help you to set up this Items database1254

that we are going to be using for our web application.1259

What I want you to do is log in to the localhost MySQL server as phpuser, and do that as the Windows Command Prompt, using the MySQL Monitor program.1262

I want you to type the SHOW DATABASES command to see a list of all the current databases that are on the server,1271

to see that we don't have this advanced_php database yet.1277

Then, go ahead and run the CREATE DATABASE command that is going to create our advanced_php database.1280

And then, when you re-run SHOW DATABASES, you should run that to confirm that the advanced_php database has been added to this particular MySQL server.1286

You are going to select that new database for use.1296

So, when we create a table, it is going to create a table in that advanced_php database.1298

So, we are going to do the USE command; we are going to say USE advanced_php.1303

Run the SHOW TABLES command to get a little practice with that, just showing you that there are no tables1309

in this brand-new database that we created (which makes sense, because we haven't added any yet).1312

And then, create an Items table using the CREATE TABLE command that we used in the lecture,1317

that specifies the item ID column, the name, the price, the description, and the image file extension.1323

And you can look at the slides that we have gone over to see the syntax to do that,1327

so that you create the table exactly as we are going to be using it in our web application.1331

After you do that, you should get a 'query OK' message from MySQL, letting you know that the command ran all right.1337

And then, you can go ahead and issue SHOW TABLES again, and verify that now you have an Items table in that advanced_php database.1344

And then, run the DESCRIBE command, specifying Items as the table you want to describe;1350

and verify that all that information you entered in our CREATE TABLE command...the table was successfully created with those attributes;1356

it has those particular columns, and those particular data types, and any particular column attributes we specified.1365

And assuming all those commands worked, it should see the same output that we saw in the lecture today.1371

That ends today's lesson; thank you for watching Educator.com, and I look forward to seeing you next time.1377