Advanced PHP SQL Command: INSERT

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 we can add data to our database, using the SQL INSERT command.0005

We are going to learn about the syntax of the SQL INSERT command, which is used to add data to a database.0013

We are going to learn about how to specify values that we want to insert into a database.0020

For example, when we insert strings into a database, we have to enclose them in quotation marks.0025

We are going to learn about some rules for doing that.0029

We are going to walk through the process of populating our Items table that we created in our last lesson with the default items we had in our store.0032

In our web application, we have had 6 items that we have had through all of our web application examples that we have had in the store.0044

And we are going to go ahead and add all of those, using INSERT statements.0051

We are going to talk about how you can have a bunch of SQL commands, or SQL statements, in text files.0054

And then, you can just sort of import that text file into the MySQL Monitor, and it will run all of the commands for you.0061

And then, we are going to go over another required homework that is going to have you set up the Items table0069

by populating it with those default six items that we have had in our web store application.0074

As mentioned, the INSERT command is used to insert rows into a table in the database.0082

It has the syntax defined here: it has the two keywords INSERT INTO, followed by the table name.0089

You would say "INSERT INTO Items," for example, and that is going to say that we are going to be inserting data into the Items table.0098

And then, what you do is specify the names of the columns that you are going to be providing data for.0108

You name the different columns: for example, you might say itemID, name, and price.0114

Then, you have the keyword values, and then, within parentheses, you specify the different values0119

that you want to correspond to the different columns you have listed.0125

So, the first value would correspond to whatever column name you could find here.0128

The second would go to this name here, and then so on.0132

And you can do that for as many columns as you need to.0135

There is also a shorter syntax where you can leave out the column names (this section here),0138

and have a command that would just be INSERT INTO Items, for example.0145

And then, instead of having this column definition, you would just say values.0155

And here, I am just putting them on separate lines (they don't need to be), just for readability.0160

And then, you would just specify the values.0163

Now, the thing to note about that is that (and this was mentioned in the last lesson),0169

when you create a table, and you use the CREATE TABLE command, you specify columns in a particular order.0173

Well, that sets up the structure of that table and how those columns are ordered.0179

So, if you are going to be using this form of the INSERT command without specifying the column names here,0182

and you are just specifying values, you have to specify the values according to how those columns are ordered in the table definition.0190

For columns with numeric data types (for example, our price column), you simply specify0198

the literal value that you wanted to insert into the database without quotation marks.0205

If we wanted to set a price of $15, we would just type 15.00.0210

For columns with string data types, however (for example, a CHAR data type, a VARCHAR, and a TEXT data type),0216

you have to surround them by quotation marks--either single quotes or double quotes.0222

And just as in our PHP strings, if your string that you are inserting into the database contains one of those quote characters itself,0228

you must explicitly escape it, using the escape sequence: backslash, single quote to add a single quote to the string,0237

or backslash, double quote to add a double quote to your string.0244

Another important thing is: for columns that have the AUTO_INCREMENT attribute set0249

(which, for our Items table, is the itemID column, because we want it to generate a new itemID automatically0253

every time we add an item to the table), what you do is: when you provide a value in the INSERT statement for that particular column,0259

if you provided a value of null or you provided a value of 0, then MySQL is going to know0269

to go ahead and auto-generate, in this case, a new itemID to whatever the next value in the table is.0275

You specify null or 0 to do that.0286

Alternatively, you can also exclusively set the value for an auto-increment column by actually giving it a particular value.0288

You can...for example, if this was our column definition: INSERT INTO, and we had set itemID with the column values...0294

let's say we are just inserting one column; we could explicitly put 1001.0306

And that way, it wouldn't do the AUTO_INCREMENT feature; it would set it to the value explicitly specified.0314

Now, we are going to talk about how to insert items into our database, taking that command that we just learned about abstractly0323

and putting it to work in a real-world example.0329

We are going to be inserting those six default items that we have had in our web store into our database.0333

This command right here is going to be used to insert the first item in the database.0339

We are saying, "Insert into the Items table values for these columns: itemID, name, price, description, and imageFileExt."0345

We are saying, "Give them the values..." in this case, we are explicitly setting this auto-increment column to 1001.0352

And we do that just for this first item.0359

And the reason we are doing that is: AUTO_INCREMENT--the first time you insert an item into a table with it, it automatically starts at 1.0360

If you explicitly set it (in this case, to 1001), then every time we add an additional item, and we specify a null value or a 0 value,0368

as mentioned, then it is going to auto-increment it from the highest item number there in the table.0375

In this case, it will have already been set to 1001; so the second item is going to be added as 1002.0380

We are going to specify the name of the item; notice that it is in quotation marks, because it is a string.0386

We are going to set the price (which is just a number, so there are no quotation marks; it is the string 15).0391

We are going to enter a description (again, because it is a string, it is enclosed in quotation marks),0398

and then the image file extension (in this case, jpg); because it is a string, it needs to be enclosed in single quotation marks.0403

Notice that there are parentheses around the names of the different columns.0411

And then, there are parentheses that surround the different values that we are going to be providing.0417

Let's go ahead and add this first item into our table.0424

We are going to connect to the MySQL database as our phpuser, just as we have done in the past.0430

We are going to use the advanced_php database.0439

And a command that I am going to do...I am going to show the tables that we currently have (we have only created one so far--it is going to be Items).0447

We have an Items table, and I am going to issue a command now that we are going to learn about in the next lesson,0457

called the SELECT command, that lets you see what data is in that particular table.0461

Don't worry about the syntax of this right now.0467

But it is saying, "Give me all of the rows in the Items table."0468

And when we enter it, we can see that it is the empty set; so we know that our Items table is currently empty.0470

Now, we are going to go ahead and insert that first item into the table.0476

We are going to insert into the Items table, and we specify the names of the columns.0479

And then, we specify the values we want to associate with those columns.0503

And after this one, I'll have to go back to the slides; I don't remember exactly what we had.0510

The second one, the name, is a string, so we are going to enclose it in quotation marks.0516

The price was $15; the description (which is another string) is going to be enclosed in quotation marks.0527

This 100% cotton T-shirt is built for comfort.0535

If I continue it on the next line..."for comfort"...I finish the quotation marks; and then, the image file, I believe, was jpg.0548

I'm going to enter jpg, again, in quotation marks.0559

I have the closing parentheses for our list of values, and a semicolon.0563

When we hit Enter, it is going to let us know that the query was OK.0568

If I run this SELECT command (which, again, we are going to learn about in the next lesson) again,0571

to see if we have any data in our Items database, we are going to see this row.0576

We are going to see (it is kind of hard to see here, because of the size of the screen, but basically) that0584

it has the itemID we entered (1001); it has the name (100% Cotton T-Shirt);0589

it has the price ($15); it has "This 100% cotton T-shirt is built for comfort," and then it also has the file extension jpg.0593

Now, the other way that we can insert items in tables, as mentioned, is not by specifying the columns,0604

but just specifying the values in the order that the columns are defined.0609

And remember, for our auto-increment columns, we can just specify null or 0,0614

and that is going to have the database automatically generate that for us.0618

We will get to this file in a second, but I have a file that contains INSERT statements for all of the 6 default items that we have.0625

So, I am going to reference it, so that I can type these in manually.0632

We are going to enter the second item into the table; this is a 42" LCD television.0636

We are going to say INSERT (let's see if we can clear a little space here) INTO Items values...0641

And now, I have to specify these values in the way that the table was defined.0661

And let me go ahead and throw an error there to get out of that command.0667

If we describe our Items table using the DESCRIBE command, we can see that the order they were defined in0671

is itemID, name, price, description, and imageFileExt, so that is the way0678

that I am going to have to specify them in parentheses in our INSERT statement.0682

Now, the first one is itemID; because we want it to auto-increment, we are going to set it to equal the value null.0692

The name is 42" LCD Television; notice that it is in quotation marks.0698

Also, notice, however, that even though this includes a double quote, I don't have to escape it with a backslash,0706

because it is included within single quotation marks; that is similar to how PHP works.0712

I need to make sure that I have the right name for that: 42" LCD Television, $599.99.0717

The next column is price, so we are going to enter, without quotation marks, 599.99.0722

The description: With 42 inches of viewing screen, you feel like you are a part of the movie.0729

And then, the image file extension; in this case, it is a GIF, so we enter gif in quotation marks.0755

We finish it with a closing parentheses, add a semicolon, and hit Enter.0765

And it is going to let us know that the query was OK.0771

It is going to say "one row is affected," which I didn't mention before, which shows that one row was inserted.0773

Now, if we run our SELECT query again to see what is in the Items table (let's see if there is some way I can get this so it looks better),0777

we can see that it has added two items to the table.0790

I am going to do a modified SELECT statement, which you will learn more about in the next lesson.0793

I am just going to get rid of the description; I am not going to have it pull up the description value0796

in the results that it returns, just so we can see it all in one screen.0803

And again, don't worry about the syntax of this right now.0808

You can see that it has added another row to the table.0818

It gave it the itemID value 1002, so you can see that, when we specified null for itemID, it went ahead and auto-incremented it.0821

It took the greatest value that was in that column in the table (which was 1001) and incremented it by 1.0828

And then, we can see that it went ahead and set all of the different columns for the table.0833

It set the name to 42" Television, price to 599.99, and the image file equal to gif.0836

One of the things that you can do is: the MySQL Monitor client can be used to run multiple commands at the same time.0845

And what it allows you to do is: you can create a text file that contains all of these commands that we have been typing into this interactive MySQL prompt.0851

You can go ahead and edit them offline in a text file, and then import this text file into the MySQL Monitor.0859

It will go ahead and run all of the commands in that particular file.0867

And the common thing that that is used for is INSERT statements.0872

For example, if we want to insert a bunch of data into our database, here I have this text file created.0875

And as you can see, it contains a bunch of INSERT statements, all with syntax exactly as you would use if we typed it in the command prompt.0881

And it just contains a bunch of INSERT statements that insert all of our 6 default items into the database.0888

You can contain any SQL statements you want in these text files.0895

For example, one of the statements that I have at the top is to use advanced_php,0899

because that is going to let MySQL know that we are inserting these items into the Items table that is in the advanced_php database.0903

That is something that you can include.0911

The way that you have the MySQL Monitor run these commands is: you use the MySQL command;0916

but then, what you do is use a less than sign, and then the name of the file that contains all the commands you want to run.0923

Now, the commands can be held in any text file with any extension, as long as it is a text file.0930

But the convention is to end files in this .sql extension, and that is what we are going to use in this course.0935

This is saying, "I want to connect to the MySQL database on the localhost (because no host is specified) as a phpuser."0943

"I want to be prompted for the password."0951

And then, one other thing to notice here is: let's say we hadn't included that USE advanced_php statement0955

in our SQL file that I was just talking about; we can specify what database we are going to use at this MySQL command prompt,0961

by...the last thing we enter before the less than sign is the name of the database we want to use.0969

This is going to say, "Connect to the localhost MySQL database as phpuser; prompt me for the password."0974

"When I log in, I want to be using the advanced_php database, so I don't have to type the USE command."0981

"And then, I want you to run these commands contained in commands.sql."0988

Now, one thing to note is that, when you specify, after this less than sign, the name of the file0994

that contains the SQL commands that you want to run, you have to make sure that the MySQL Monitor knows where to find that file.1000

If an absolute path is not specified, it interprets it as a relative path;1014

so in this case, commands.sql would have to be in the same directory where this MySQL command is being run from.1017

So, you might have to, for example, specify an absolute path.1023

Maybe you have an SQL file called items.sql stored in the user1 directory of the Users directory on the C: drive.1031

So, you would replace this commands.sql after the less than sign with an absolute path to the file.1042

That way, that is one problem that you might encounter.1047

You need to make sure that MySQL is able to find the SQL file that you are trying to import.1049

We already know that this file was set up to insert the 6 default items into the database.1057

But we have already inserted the first two, so I am going to go ahead and delete this from this file.1067

And then, I am going to have it insert into the file the remaining four items.1073

As you can see here, what I have done is: the next two items--I have just specified the values.1078

And so, I have to specify the values in the order that the table was set up in.1084

It's itemID, followed by the name, followed by the price, description, and image file extension.1090

Then, in this last part, in the fourth and fifth one, we are going to explicitly name the columns.1096

I'll put this on a separate line.1102

We are saying the first value we are providing is the itemID; we are providing it the name, price, description, and image file extension.1104

Here, this should be null.1112

And then, one thing to note is that, if you are explicitly specifying the columns of the values that you are supplying,1116

you can actually put them in any order you want.1126

You just have to switch the order of the way you are listed in the values parentheses.1128

For example, we want to specify price first and then name; we would switch the order here, and then subsequently switch the order in the list down here.1132

What this statement is going to do is say, "Insert into Items itemID, price, name, description, and imageFileExt."1143

So, this one is going to be interpreted as itemID; this will be interpreted as price; this will be interpreted as name.1148

So, it will still work; and it just allows you to switch the order around.1153

And that is just to give you an example of the different flavors you can use for the INSERT statement.1156

Let's see this file...I'll get the address of this file.1169

I'm going to exit altogether and get a fresh command prompt.1180

I'll change to our mysql/bin directory, so we get access to that MySQL program.1191

We are going to connect to the localhost; we are going to connect to phpuser.1200

We are going to be requested for the password; we are not going to specify that we want to use the advanced_php...1205

Actually, let's go ahead and do that; let's specify that we want to use the advanced_php database.1211

And then, in our items.sql file, I am going to eliminate this line.1216

When we import this file, it is going to know that we are using that advanced_php database already.1220

And then, I'm going to specify, using the less than sign, the SQL file that I have called items.sql1227

that contains these INSERT statements for the remaining four items that we are inserting into the database.1235

And I am going to specify the absolute path to it, which is in this particular directory that I just copied from the Windows Explorer.1240

It is called items.sql.1255

And when I hit Enter, it is going to ask me for the password.1257

And then, assuming everything runs fine, it is going to run things and then return the command prompt back to the way it was before.1264

So now, to make sure that they were all added properly, I'm going to log back in as phpuser to the advanced_php database.1270

And now, let me type SELECT; again, I am not going to bring up a description, because it makes things longer.1293

Again, typically keywords (they don't have to be capitalized, but) like SELECT in this particular case, by convention, are capitalized.1309

This is going to say, "Give me all of the information about all of the items in our Items table."1315

And we can see that it was able to successfully add those other four items.1320

It added 1003, 1004, 1005, 1006, with the values that we specified.1323

We saw that you can use either the format we used to explicitly specify the column names,1329

or you can just explicitly specify the values in the order in which the columns are defined in the table.1335

Or, you can explicitly specify the columns in any order that you want, as long as the values that you supply match up to those particular columns.1340

The required homework is to get you to populate the Items table you created in the last required homework1353

with the 6 default items we have been using in our store.1359

And what you should do is log into MySQL as phpuser, using the MySQL command prompt.1363

And I'll have you experiment with a couple of different things that we did here in the lecture,1369

so you get experience with both manually typing commands, and then also using that less than sign1373

to import commands to be run by MySQL.1379

After you log in, select the advanced_php database for use (which you use the USE command to do).1384

And then, run an INSERT command that is going to insert item 1001 into the database.1390

And in this case, we are going to have you explicitly set the itemID column to 1001.1396

And what that is going to do is seed that AUTO_INCREMENT column, so that any item added after that--1401

when we specify null for itemID, it is going to add 1 to that.1406

So, the next item will be 1002, then 1003, and so forth.1409

And you can get the information to add for these particular items (the name, the price, the image file extension, the description)1413

from, for example, items.txt, which is the database file we have been using to store information about all of our items in our store.1421

That has been our database to figure out what values you need to put for each item when you enter them into the database.1428

Then, run the SELECT * FROM Items command to verify that the item was successfully added.1436

And then, exit the MySQL Monitor client.1442

Then, what I am going to have you do is create a file called items.sql in whatever directory you want1445

that is going to contain INSERT commands for each of the remaining 5 items in the store.1450

One of them you are going to answer manually; the others, you are going to use an SQL file for.1455

And I want you, for the first three items that you add, to use the INSERT syntax that includes the column name.1461

You are going to explicitly specify the column names (itemID, name, price, and so forth).1468

And then, for the last two, use the INSERT syntax that just provides the values.1473

It just has values with the parentheses containing the values you want.1477

Just remember that you have to specify those values in the order in which the columns are defined for that particular table.1483

Then, use that less than operator to input this SQL file into MySQL Monitor to run these INSERT commands.1489

And make sure that you specify the correct absolute path (or relative path, if you want--if you understand how to do that)1498

to this items.sql file that you have created, which is the name of the file that is going to contain all of these INSERT statements.1505

And so, you need to make sure to specify that it knows where to find that.1513

You can optionally, in your items.sql file, include USE advanced_php as the first line of the file1518

to make sure that these statements get evaluated on the advanced_php database,1528

because if they don't, you will get an error from MySQL.1532

Or, you could use it using the MySQL command prompt, and specifying user, p, and then specifying the name of the database,1536

in which case (and a less than, in the name of your file) you can leave this statement out here.1546

I'll let you decide whatever you want to do for that.1552

Then, log into the server as phpuser; run this SELECT * FROM Items again (which is saying,1554

"Give me all the information about all the rows in the Items table) to verify1559

that all those items that you had in your SQL file were successfully added.1563

A couple things to note: make sure that you properly enclose all of the strings in quotes.1570

All of those string data fields, we have to enclose in quotes.1575

And escape any quote characters, as needed.1578

And you will notice that, on the descriptions that we currently have for some of the items in the store,1581

there are single quotes contained within them; and so, you are going to need to escape those with backslash, single quote.1585

The alternative would be that, as in PHP strings, you could enclose the string in double quotes.1594

And therefore, for example, if you have the word you'll with the apostrophe, you don't have to escape that single quote.1600

I'll leave that up to you.1613

And then also, just make sure that mysql.exe, the MySQL Monitor, can find your MySQL file.1614

So, you are going to have to specify a correct path to that file after the less than sign after the MySQL command.1619

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