Tip:
Highlight text to annotate it
X
Good evening. This is P H P class. We only have two more weeks. We
are down to the last stretch of the semester. Do your homework.
We have a few weeks left and then you can have a break during summer.
Last stretch.
Any questions before I begin? Last week we're talking about the data base connection and
everything. Whoever joined the conference
was fine with homework and the rest of the class were probably having problems. I
sent an e-mail out for people to review the recording and other stuff. Any
questions that you encountered during last week assignment?
Any particular problems far the last weeks assignment?
That means I can move onto this chapter.
Last chapter we were working on the data base kind of
directly. We had a remote connection to my S Q
L data base and we were creating tables into the data base that we already have.
Created data base. We don't have permissions but we can create tables in
the data base already provided for us. We are creating tables and doing the query
and update and all of that.
Now think how often you would have access
a remote
terminal to the data base? Rarely. You are hosting a companies -- let's say you are
a site. You have a hosting company that will host your site. You are doing some
programming and all that. Most likely for your users
to get your web pages your website they don't remote /SREBGT
connect to your data base and try to do their
data base work there. You provide a web interface for the user to update your data.
Most often your users will not have access to the data base directly. They only
have the screen they work with. Then those
screens would -- with the code behind it, would update, insert,
delete from the data base.
This chapter is the most important chapter of the second half of the semester is
going to -- we are going to learn how to do that in P H P. With
P H P I can
run insert, delete statement so the user can fill out the form and grab the data and save
that data to the data base. That is what we are going to learn today. It is a very important chapter.
We are going to learn how to connect to my S Q
L -- on screen.
When you think about all this data base driven web
sites, like Amazon, I use that a lot. Amazon, FaceBook,
any bank of America site, all of your -- if you go to
-- all these sites and let's say you
are looking for houses and checking the real estate site and looking
for buying some E bay -- all the sites have
huge data bases in the back end. Even net
F L I X if you want to watch movies. All these have data base in the back independent.
end. The site features -- united air lines site
have data base in the back end and write pages for you to interact with their data base.
You want to make a reservation, you fill out the form and click on next.
Retrieve some information here and here are some flights on the
day and you can pick which one you want.
You get the payment screen and fill out the form and give your credit card information and
submit. Your credit card gets charged. You have a
reservation is also another record in the data base so they remember you bought that ticket.
Everything interacts on a daily basis -- anything with a
data base connection we are making a connection to the data base, grabbing data, present to the
user, retrieve user data, validated, save back to the
data base and present confirmation e-mail. All of this is
what we are learning in this chapter and all the chapters before this. It
all comes together here.
P H P has a -- on screen.
P H P is coding the problem and then you attach the back end. It can be
Microsoft S Q L server. O R A C L E data base,
my S Q L data base. You can use P H P to connect to
it and do your thing.
Now let's look at -- how do you do that? How do you make a
connection? How do you run the query. This is
functionality. Every page needs to do that make the data base connection, run the query
and give me the results back and all that. It is all very common. Instead of everybody writing their own
code, then there be existing functions already written because this
is very common. Existing packages already written for those purposes. You
can just call the existing function.
In the P H P world there are two versions. We have a my S Q
L I package. Improved package which works with
a later version of P H P. The newer package and it is
object oriented approach. It is object package
classes. That is my S Q L I package. Earlier
version we have functions. We don't have like objects. We have functions. Those functions is the
older style my S Q L
package my S Q L package. Those are just set of functions.
In this book the examples are presented using the older package so
comparable to web servers using older version of this.
P H P these function calls still work. In appendix there
is corresponding table for my S Q L I package. Whatever there is my S Q
L connect and my S Q L I connect
function provided. You can compare the chart and see the
difference. We are using my S Q L connect for this class.
It is okay and in the future as you are writing your own code you probably want
to switch to the my S Q L I package. It is a very easy switch.
The first thing is before we can do anything we have to make the connection to the data base.
How do you make the connection? Call my S Q L under score connect function that is
already written. Provide parameters and
use existing function and makes that connection.
How do you provide the parameters. My S Q L
connect takes 3 parameters. First is host. It is the server I
P or server and then
user name and pass word. Three things. With these three things you
can use that to connect.
Here is the example. You would have the D B
connect. After you make the connection D B is variable that stands
for that connection. You call my S Q L connection and go to local host
and put in user name and pass word. That is the connection part. After you use it, you
can say my S Q L close to close that connection and you pass the connection.
Which connection because it close? The one you open. You can pass that and you
can close it.
Here are other useful but not very
common is the application. You can ask for the host information and serve are information
r information and different information about my S Q L serve
r.
Let's look at
this common errors for connection is data server is down or can't connect.
You don't have enough privilege to log in
and invalid user name and pass word. Those are the common errors.
How do you get the error. You can use my S Q L
E R R N O to get the errors
. On screen.
Let's look at one example of connecting.
Chapter 8 -- first one. On screen. If
you run this one, you get some server information. To do that you first have
to connect. Let's look at the code. Just pay attention to the connection part.
My S Q L -- this is the code that is making this connection retrieving this data.
On your P H P part here and then you
call D B connection variable and equal to my S Q L
connect -- this is a P H P function you can call. You pass three
things. Remember this is the host.
For our purposes our S Q L server my S Q L server and P H P
server realize on the same box same server
running the -- web server and data base server. Relative to the
web server -- we are writing this in P H P. Web server is running
this code. Relative to web server my data base server is the same one. It is
the local host. Our server is local host. Don't put anything else. Don't put P H
P dot mission college dot E D U. We are connecting local host because our
web server and data server are on the same server. Relative to the
web server this is local host.
We do local host and our user name would be our account. C 86
under score two digit account. For
the one I'm running I'm using 99 for the test. Pass word is P H P.
I put that in my connection. Now D B connect is the connection to the data base.
That is that connection.
Here is the printing. I'm calling get
client I N F O and printing it out.
This is checking for errors. Data base connect --
has problem I get false.
If false connection failed. Otherwise I have a good connection and I can
ask information using these functions.
The most part is how do you connect. You
call the connect and it gives the connection string and check to see if
the connection is good. If not good, just print the error message. If it's good, then you
are set. From this point forward you can do your job running S Q L
command. That is the connection piece.
Whatever that we learned in chapter 7 all
those S Q L commands, now with this connection I can
run those
insert, and I can run the update. I can do all of that. Let's see how we do that.
Before I do that I want to point out data base connection
code with pretty much every page I
need to make that data base connection. If I'm moving and first thing
I see is the home page.
First thing
I see is the home page. Amazon dot com. When you first
come here you see this stuff like the new
selling stuff. Kitchen
sink. You get it from the data base. You need data
base connection. On screen.
Books. If you are doing a search, you need to make a data base
connection as well over here. Every page on this site, first thing I need to do is make that
data connection. That code of D B connect -- all
this stuff it is pretty much I have to do this every page. Okay?
So then now I don't want to repeat this many many
many times over because I have like a 50 page site. What is
the strategy? How can I write it one time and /RE use it?
Anybody? Any ideas?
Use an include, right? Just like what you did for your project. The things
you use over and over and over again put it into an include. Okay.
For most web pages out there the data base
connection code is in the include file separate from the other code
and usually in the secure location. You have more
security on the file so people don't get to it and crash
it. That is include file.
Now let's go back and see how do we make that connection. Actually, let
me stay with the sharing and go back to the code.
Let look at select test. There is example in the book on
using create data base. From website you can call my S
P H P my S Q L create D B to create the data base but usually
you don't do that. You don't use a website to create a data base.
Data base is usually create first. Tables -- you design the tables and they
are designed on the data base. Web page interacts with those tables. You are not usually
most cases you don't create tables on the fly on the web page.
I'm going to skip the creating data base. We don't have rights any way and
it's very -- this select test I want to just show you
because it is a repeating of the connect.
Select -- this example shows you how do you point to a data base. Remember if
we are on my S Q L monitor like last weeks
assignment, once you log in the first thing you do is say use which
data base. Use the 86 dash 99. You are picking the data base. The
picking of the data base is what this example is saying. But before
you pick a data base, you have to connect first.
Data base I'm going to pull into include. If the connection is good
call include and in this include I'm
making the connection and picking the data base.
Let's look at this include. In this include I'm going
to give the data base name as 86 dash 99. This
is the connect part local host 99 P H P. If the
connection is false, we have errors. If not, we pick the data base. We pick the
data base by running my S Q L select D B function.
It is already. We don't have do code it just use it. Provide what data
base you want to use and connection so it points the connection to this data
base. If this is good,
no problem, then you have a good connection. Then the connection is pointing to the data base you want.
That is a good connection. You can start using D B to do your work. Otherwise
you have message bad connection.
What is
this at sign -- error messages -- I don't want
the error messages to show up on my web page. Sometimes you do want that. You
don't want my error messages coming from the data base showing on the web page. You want
to during debug you may take it out and you see all the error
messages and fix all of them and when you move the code to the live server, you don't want your
user to see those messages. You put the at sign
. That is the include.
This include we are using this data base C A 86 dash 99.
This include is good for anything you want to do if you need to connect to this data base, right?
Your homework examples project you can just create an include statement
and use it in all of those places. That will be a connection to your data base.
Use include.
Now create table. Again this create table
example you can create a table with P H P but
not recommended. You make a connection to the data base. If the connection is
good, you can -- this is checking to see if you are already have a
table with the same name. Run show tables and
like whatever you want to create command. Here is the command. This shows you how
do you run a query. Construct a query. It is like a long string.
Has the quotes around it. That is your S Q L command inside the quotes. This is your
S Q L command. Then you are going to run this in the connection.
You call my S Q L under
score query. It is the function that you pass the connection
and the command so this is the string
and my S Q L command and this is the connection. When you do this this com
mand is going to run into the function and whatever it gets back will pass that. The query
result will get the result. Whatever you did
in the previous chapter show data base or show table all
those commands now instead of running directly type it in
the S Q L monitor and run it, you can type it in and put into a variable and
then you call my S Q L query to run it through the connection and the result will be
sent back. With this function the result is sent back. You save
it into this variable. You see the connection?
Everything you did in chapter 7 you can do it this way to run it through P H P.
Let's not look at this for now. If I don't have the table, let's say
if I don't have the table -- the table I want to
create, if I don't get anything we don't have existing table I
can create the table. How do you run a create table?
Go to monitor and type create table and then
values and list the columns and say values and list values. You do the same thing.
You construct a query. Create table. Whatever is inside
this quote is exactly what you would type you have
a direct connection to a data base. Create table and I Ds
and all this and list the column and the type. Then you run it
again my S Q L query and S Q L string you just did and
the command and this connection. You run it and get the result back. That is just shows you
how you make the connection and how do you
construct and run it. Creating tables through data base not
recommended. Create table ahead of time and has a good design
and everything is created and linked and your page is just doing the data part.
Now let's go to the example and do some
data. Here is the most important example. We will practice on this and your final exam will
be on this. Final exam you will write a form and then have the user fill
out the form and gather information and go to the data base. Save it.
Then you would retrieve the information saved and present it to
user. That will be on the final exam. This is the very important part.
Let's look at this example. I go to a news letter the website and
I join this club and they have a news letter. If you want to subscribe you fill out
a simple form. This form is simple and you can expand it
and include more fields. The same processing. Let's use a
simple form to illustrate the processing. Put
my name and e-mail and hit submit.
On screen. You are now subscribed to your news
letter -- that is confirmation. If I want to
see who subscribed,
show news letter subscribe. I click. On screen. I did it this afternoon.
In here we didn't really
have -- I was added to
data base again. This shows list.
Let look at the code behind this one and this one. On screen. Adding to the data
base and showing records on the data base.
Look at that code. Okay. News letter subscribe dot P
H P. If you go to
Fill it out, hit submit.
It goes to the subscriber. This is all in one form. One
program. It doing display the form and processing the form. All this one form. All
in one form. Let's look at the
M C part. If I come to this page for the first time. I didn't hit submit
button. I come
to the form the first time. I should present the user the empty form.
This corresponding here that is processing.
Logic -- I didn't hit submit. I got to the page for the first time. This is the part of the code.
I am going to show this form. This form the action news letter
subscribe P H P myself and method is post.
. Method is post. On screen.
This is if I have something I put it in. This is the first time. Subscriber
is one and e-mail is one. I get
blank form. Fill in and hit submit.
Then what happens? I go to this code again. At this time somebody hit the submit.
Post submit -- there will be values in there. Then we
go to the processing logic.
On screen. Let's see what is going on for the processing. Just
like with files, we have to do retrieving, validating. Those steps are the same. Retrieve data from
the user. We are getting the subscriber name from the
post of array. Somebody typed something. Then I'm doing the cleanup.
I'm going to --
trimming spaces. On screen. Doing the cleanup. If after
the cleanup I still have a 0, no one typed in. There
is no data in there. I get an error message and say there is an error.
Otherwise this is the --
otherwise my submit subscriber name will take on the
name put in like Helen sun. Then we are
checking the e-mail part. Is there the e-mail.
Clean it up. If cleanup is good, then my subscriber e-mail now has my
e-mail address. On screen.
With that now I'm going to check if there is any errors. If my data is
all good, then my form, error count will stay at 0.
No error. Then comes the party have no error. Now
I have the data ready to be saved into the data base.
Now what happens? I need to make the data base connection. I'm ready to save
the data. I make the data base connection but getting
the include part. The include part will be the part that is connecting to the data base. If my data base
base connection is bad, there are some other things. If the connection is
not bad not equal to false then I have a good connection and I'm ready to
use it.
The table name I want to save this data to is subscribers. The
date -- this is current date. We
are going to take the current date in the format
of year month day. On screen. Insert into
table name. I'm calling the column -- on screen.
After that I'm going to run that command through the connection.
Then I get something back. Usually when you run an insert statement,
what do you get? One row is inserted. One
If you get something back, that is good. You already saved it.
This part I'm going through details of the code again and I'll show you with
debugging message so you will see what is going on here.
Question. In chat.
I will continue to then come back to look into this in detail
what this blob of code is.
After you do insert and are successful you can say my S Q L insert
I D because I'm using this table now as a
system -- it is
like student and fill form and submit and get system generated I D.
This one you call insert I D this is generated
. Subscriber will get that I D. You get that I D. You can
print it out and close the connection. Then
you don't need to show the form because your processing page. You skip the
rest of this page.
Now let go to the rest of this S Q L statement.
Let me connect to this my S Q L and I'll show you the
table structure and then show you some data and you can see.
Okay?
Sharing. On screen. This is a quick review for connecting to the data base.
On screen.
Connect to the P H P mission college dot E D U.
This is a remote log into the
server. On screen.
Don't tell me it's broken. Good. Be patient.
Now P H P pass word. I'm in the server physically. Remote into
the server box but I'm not in the data base yet. I have to call my S Q L monitor.
On screen. Provide the user
name. Prompt for pass word. This is connection step.
On screen.
I'm going to use C A 86 dash
99 for my default data base. I'm here. Show table to see what are the tables I have.
I have company cars subscribers and
visitors. Subscribers are one example is looking at.
Subscribers -- here is my structure. I
have I D column. Small I N T.
Not null. Primary key. That is the identifier.
It is like the student I D you have. Not null and
extra information. Auto incrementing. The system will generate this number.
If I have first one will get I D of one. Second one will get I D of
two. Third one will get I D of three. The one I
Insert statement the I D will automatically
be
assigned as four. I have I mail
e-mail is subscriber date. Confirmation column
and -- on screen. E-mail can be null
and subscribe date can be null and confirmation can be null to start
a record. In most cases you probably want to
have main
name as not null and e-mail as not null
-- that is our table structure.
What is in there already? Subscribers -- here are the
two records. I have me this afternoon and
this evening. That is the record.
Now
.
Go back to the P H P code. I'm making a connection and pointing to
this subscriber table. Let's see what this insert.
-- if I'm running this again, let me
uncomment this out. For
me even in the days where I'm coding this every day.
I'm the web programmer for a long time. I was coding this every day. That was my career my
job. I did this 8 hours a day. Even at this time I do this many many
many many times over. Most times I can do it in one shot without any
error messages. Put together my S Q L statement
in my sleep, I did it so often but
still every time I have this debug statement there when I'm running my program. This will show
what the S Q L string command will be on the web browser. I
know by looking at it to see if I miss anything, because there are so many
quotes. You are constructing you know you have to put quotes. Data you can have
have to put quote and you
have single quotes and double quotes and you have to connect and dots and commas and
everything. It is easy to make a mistake and you think you have it right and can't
figure out why it is not inserting or is inserting all this data. You can't figure it out.
If you printout your S Q L command you constructed
in one glance you can pin point your problem. It is a good tip or habit to put a
debug in your code for printing out your S Q L command and then after you test it
and everything works fine, then comment it out. Leave it out there and
just comment it out so the user won't see your long complicated S Q L
command. Let me uncomment this out.
This is on any flash drive. I have to do it on
the server.
Let's go to connect so I can show you what it looks like.
We can decipher this.
I need to use the 99 account. I'm using connecting to
the 99 --
no. Chapter 8 -- on screen. The subscriber dot
P H P. This is the one. I need to uncomment the
echo part.
Save. Let's run this again.
Now I'm subscribing. Let's go back.
Now this time John
Smith. Made up name.
E-mail is -- on screen.
If I hit submit I should see my S Q L statement. The one that
echo. There we go. Here is my S Q L statement that I was instructing in the code.
On screen. This is exactly
what you kind of need if you are having a direct connection to the data base on my S Q
L monitor. That is the same thing you would write to run it on
P U T T Y. If I type the same one, I should hit
interand inserted that record.
enter ^ oh ^
-- on screen. It is a little bit complicated in the P
H P side. Let's look at how we constructed this.
On screen. The first part double quotes.
Table name is described. This is a variable and subscribe
rs. Inside double quote with
variable name and values it is
replacing will be here so subscribers will be here. This first part my S Q L
string will be equal to insert into subscribers. That is only the first part.
Now I need to say what and I need
to say
left parentheses -- on screen. How can I get that?
You have to in the code -- on screen. The second part because
I have more to come, right? I need
to con cat this part with my first part. The second party need to con
cat -- the operator is the period. It is not anything in the S Q L command. This is a
my S Q L string continue. That continues.
It continues --
. The next part is the left parentheses, right? --
no, we are here. I'm con cat ^ oh ^
-- next part is name e-mail subscriber. I put that whole thing in
a string.
Values. That is my next part, part two of the string. See
that? On screen. Part two of the screen. This is part two of the
screen in double quotes. That is my second part.
Now I need to say
values and then my name in single quote comma,
e-mail single quote comma and date single quote comma.
That part.
Let's continue. I need the left parentheses. I put
a double quote. This is my string, left parentheses and this
quote. It's a single quote. That is that part? That is
this symbol. This little bar. The left P /AFPLT
A R E N and single quote. Put that in.
That is the next part of the string. There is my left P
A R E N and single quote. We need
that. Then con cat the next part is John
Smith. What is John Smith? In this code John Smith is a variable.
Subscriber name in subscriber variable.
It is John Smith.
We put the variable name here.
Now that John Smith will be con cat with left P /AFPLT
A R E N and single quote. Now I'm up to here.
Now what is coming next is single quote a comma a single
quote.
Next part is single quote comma, single quote. Put that next string there.
What is this? It is con catting.
Continue to do the same thing. Put the subscriber variable and then
con cat with subscriber e-mail is single quote comma
single quote. Next comes the date. I put the date and after the date I have a single quote,
a right P A R E N and the end. Finally I'm
done.
See how this S Q L command is constructed with the data you
save? Okay. This part is pretty
T E D I O U S and it's hard to get it
right the first time because of the double quotes, single quote, period and
variable name here and all this stuff. It is very
easy. It is pretty easy to make mistakes so when you
are debugging, make the next this
debugging message there so you can see it. If you forgot the
dot, then you see only part of your string. You won't even see the rest of the string.
You forget the single quote somewhere, you see that John Smith one single
quote here and no quote and then comma and you say I'm missing a quote. Print it
out. Then it is easier to see your mistakes.
If you forgot a double quote the P H P will
complain. Let's say I do this.
It is creating this as a string. This is the start of my string. This
is the end of my string.
P H P will complain. The one thing that you kind
of -- here is a hint. In P H P the variable name
s are blue. If you put your quote wrong, your literal text is like black.
Your variable name inside double quotes is bolded and
if your variable name is black, then it is a
literal text. It is no longer a variable. You will get subscriber name rather than
John Smith.
After you construct you can run your S Q L command.
Let's go to this one. I run it and now I should have three records.
John Smith. I inserted that one successfully from the web page.
I'll leave this one on so we can see the debug message.
Any questions so far? This is the important part. That is saving the data /-PLT.
. Now if I want to show how many subscribers I have now, let's look at the second part.
I click on show subscriber. I get listing of
my subscribers. This is very
common too. Listing of your product. You can have another form to order things.
Listing of data in a particular table. Let see the code
behind this. Show
news letter subscriber dot P H P. This
one -- before we look at the code what do you think should happen? What
is behind the scenes? I know the table and I want to get the data to display.
Before we were to do this on the data base
directly through P U T T Y, I want to list all the subscribers that I have. What
do I do? I just do the command that
I wanted to do. Say select star from
subscribers. This
web page I want to do the same thing I'm doing here. I'm going
to run this command and show it on the page. That is my path.
This page naturally we think the flow would be making the data base connection
first step. Second, run a select star subscribers command.
Third, get the result and then
-- the result has two records.
three records and display
the result data. Show the result data. Connect, run is
select star command and show it.
Now let look at the code and see if that is matching what we think should happen. Here is
the data base connection part. Include -- on screen.
Include P H P file making the data base connection and point to go the right
ing to the right data base. All right. I have a
good connection pointing to the right data base.
Here is the table name. I'm going into the table subscribers. Here is my string.
Select star from table name which is subscriber. That is the select statement. Here is
running the query. Through the data base I'm
running
. -- on screen. Query result is pointing
to this
. It is pointing to three records.
If I'm on Amazon searching for a book
Android -- I got 34811
results. I got that many results back. That is my result if I
do a select star from. What is this query running? I'm running
Amazon. Behind this
page -- assume they are using P H P. I don't think Amazon is using P H
P. What is the query that is running that
produced this result. Selecting star from inventory.
Inventory table. Where? Title.
The book title. Like
percent sign Android books percent sign. That is like
star. The title contains Android books then I'm going
to return the result.
In this case 34,000 records. In our case we only have three records.
Three records. We have three records in the query result. It is point to go
ing to the first one. It is pointing there to the
record set.
Now what happens next? We are looking through the three records
and placing the information on the table in the table
format. This is H T M L table and
what is this? This is setting up the table tag with
boarders and this is the row that has the heading. This one is setting up the table
and this one is producing what? Producing this header line.
This line is what produced with
that P H P code. Subscriber, name -- the column headings is
printed here with this line.
Now I'm looping. On screen. This
is S Q L result is pointing to the
three records at the beginning of the records. Retrieve one row from
the query result and give me back that row
and then because retrieve that row and the pointer goes to the next row the next
record. This one grab one row and pointer goes to
the next row. Grab one row. I get the first one. If you get the first
one -- the first one would be this record. On screen. This record is my first one.
I have the first one. Then this one is
grabbing the -- on
screen. How did it put the result? It put the result in the index array.
Using array. Array is like that. Go to data types that we use
in P H P. We have this
array we put the first record in.
How did it get into the row? The column
becomes the array element. Helen the first
name part will become row 0 is the name part.
Row 0 is the I D. Subscriber I D column. I forgot. The first column
is subscriber I D. Row 0 will have subscriber I D. Row with
square bracket one will have the name. Row with square
bracket two will have the e-mail and three will have the subscriber date and four will have the confirmation date.
Now we are going to put these in the cells in the table.
Then we are going to loop and go to the next one
and do the same thing. Every time I process a row in my output I have
the new
tag for a new row in H T M L. This is how you present
a table. When I'm done I close the data base connection.
Any questions on this one? I want to make sure you understand this part. Your
homework and final will be similar to this and your project will deal with this also.
Any questions on this part? So far so good? Clear?
Review. Make connection.
Query.
Subscribers like your customer with date. Five
years ago so stay with you for five years.
Something like that. Subscribers they just joined so you
want the new members. You want to send them a welcome letter or
package or something. On screen.
Things like that. In this case we don't have a state. If
you want to
put the state and have them fill it
in -- pull out all the California customers.
Have the questioner I and run with my S Q L query.
Result. Do a while loop. You use my S Q
L fetch row and pass the result so every time you get one row and then put
it P A R S E it into the array and print the array out.
Okay?
One I T E R A T I O N of this while loop you are printing the
result for one record. All the columns for that one particular record. Then you go to
the next record and then print that information.
That is how you show the
results. Any questions?
Return the result and give you some extra information. On screen.
It is the same logic. Write a select
statement and run from the data base. However just like
Amazon example, you probably want to show how many you have. There
are so many matching records. Then you want to
know the total number of
rows. You can use my S Q L number of rows function
to say okay. Give me the number -- the total
number. This gives you the total number of columns. Usually you
don't display columns. You just display rows. How many
records you have. That is how you get everything my S Q
L, N U M ^ rose ^ rows and query.
Result -- if you get a lot back, then a lot of
memory you are taking up. You can click the data
base connection.
In a lot of web sites when you
are searching for things you get this stats like how many records. Then you
probably won't show all of them in one page if there are a
lot of then you would have this navigation. This is very common, right? Second page,
first page and then navigation
for pages. This is very common.
How do you do that? It is a little bit beyond that book but how do you do that?
How does that work? Is it actually I have 34,000
records? It would be very slow processing.
Technique
is you first get a total and do a comp start. That
is the S Q L part that we didn't cover. You can get a total number of records for
your return. With that total you can --
you usually have how many per page that number is in the configuration or
something in your website. For my website we are displaying books. I usually go
with 15
16. Then I know how many
pages, right? I would print that information up here and I would retrieve
the top 16 first and display.
If user hits next, then I go and grab the next 16 and show it. I
don't retrieve all 34,000 and store it in an
array. That is going to take a lot of memory from the server. You only
display what you have and only retrieve what you need to. Then you go back and grab the
next set and print it out and display it.
That is usually what happens.
If you go to the real estate site -- even the mission college site ^
-- we are in the registration period for the summer and fall. You can start
registering and check out our courses in the fall.
This is our form. What is happening here? Can anybody? If I
print fall, mission college, C I S, I hit
submit. What happens behind the scenes? With what you have learned so
far, you pretty much came to this page. You this is a drop down box. This is
a check box on a web page. Text field.
Text field. Drop down. This is a drop down and four rows, five
rows of this drop down and some check boxes and everything and when they hit
submit, what happens? You
check for the fields the user fills. I have
2013 fall calling the post. Then I have -- this is a
form with a post method. I grab this location. Whatever the field
user put in, I grab those and those are my criteria. I hit submit. What
happens? Behind the
scenes I would construct my S Q L query. Select star from
course -- section that we offer. Where?
On screen.
You run in query through the data base connection. You grab all this data back.
With the data then I loop through.
Result. I have the
while loop and I place in the table. Title of course. Term.
Status. I put the course and some of these may not
come from single table from multiple tables. Those taking
my data base class you know that my query will be
many tables. Course table, section table from the faculty
table from the
classroom, joining altogether and
putting all the columns we needed.
Loop through -- exactly like we are
doing here. We do the fetch row. The result
and looping through. You
are placing it in the tag and get the result.
How do you put
the link? This is JAVA script. You can code it to show the details.
On screen.
You click it. They
go to the other page. Detail page. Have another page that shows the
detail.
After this chapter the most common --
all of the common and very useful techniques you
have learned so you can code pretty much good chunks of
the websites out there if you really try. You have the technique already.
Complex takes a little more. Your form is just two fields. You have ten
or 20 fields. Your select statement may be a little more com
plicated depending on your data base design. If you are coding having data
base driven web sites, I would really encourage you to take a /TKAT
data base course
. Take a one semester long data base course. It will give you
more power on the back end on how to construct your queries and how do you pull data from many
different tables and how do you code these things. If you want data base
driven website, please take a data base class.
Okay. Any questions so far? I think I covered most things.
I gave you the highlights of most of the important examples.
Any questions?
If not, you can sign off. I'll stay on a few minutes to answer questions.
I'll see you next Thursday.
We have a rather quiet group tonight. Everybody is tired
probably. Usually this period
is -- the hard couple weeks.
Please hang in there and get over this and then you
. Please do remember to do your project. We only
have two more weeks until finals. Do your
project.
In chat. You use the query. I have a question.
In the S Q L query you don't want to retrieve all the record in one
shot especially if records are huge. On the query part you limit. You limit like 20
at a time or hundred at a time. Hundred is not bad. Once you get into like thousands or
10,000s, that is when you don't want to do it in one shot.
Your record is only like a hundred you might take it one shot and display.
You do 20 or 25 at a time in the while loop.
If record is huge, limit it in the query. If the record is not huge, then you get
it in one shot and in the while loop you limit it.
It is both ways.
Data base connection although expensive,
it takes some time. If the record is huge,
you don't want
to user sitting there saying nothing while you are trying to get
that record back. Difference between
100 and 20 retrieval time is about the same. No big deal. Grab that
100 and processing time
do 20 at a time.
Any other questions?
We are actually at this chapter piecing everything together. This is the
one that ties everything. Now after this one and the next chapter we talk about a little bit
of the section where you have the user log in. That ties everything together. After
chapter 9 you can code most of the sites out there on the web
today.
In chat. 16 and 16.
You can do a pop.
a top.
You can put 16 it retrieves
only 16.
No questions?
This has paging --
this one is similar. It is displaying one page at a time.
If you have a certain number, you go to each page. You can see we have
a -- on screen. Class is new this fall.
39 computer
This is a transfer course. Android course. That
is new. If you are interested, you can sign up. On screen.
I already have student that signed up.
Any more questions? If not, you can sign off. I'll
see you next week on line. Next week is an interesting topic. It is
Come to class.
I hear somebody talking.Thank you everybody for joining me.