12:03:57 Hello, today I'm going to talk about PHP. In connection with SQL. Because it's okay to watch this presentation first, it's okay to watch the ones on forms first.
12:04:11 And I will emphasize that. The focus of this class is not on the PHP or the HTML, but some of you may want to use the PHP.
12:04:22 In your projects. So I'm in. In the presentation. No.
12:04:33 Again, I have examples which I talked about in the HTML.
12:04:42 These examples I'm talking about are mainly focusing on the driftless healthcast example. I'm going to start.
12:04:53 By looking at the title search. Option now.
12:05:04 The title search.
12:05:08 The HTML code had a form in it. Which gave information. Now, I wanna.
12:05:19 Emphasize one of the first things you need.
12:05:24 To learn is. The per passing of parameters. And I'm going to draw your attention to this line, which is actually a comment.
12:05:35 But this is how you could call the title search. PHP file. In fact, I'm going to.
12:05:46 Check and see if I can actually call that.
12:05:53 Yep, it works.
12:05:57 Okay, what this says is go to Lemuria, my area, the area devoted to the driftless health care podcast.
12:06:10 This is the Title search point PHP. Now. Note that question mark. That question mark means whatever is after the question mark is going to be essentially the parameters So whatever an HTML code would call part of title.
12:06:35 Will get the value banana. And if there's more than one.
12:06:44 Parameter. In the example before you use an ampersand. And and sign. To separate the different parameters.
12:06:57 So you can what I do when I'm writing PHP code. Is I first debug the SQL code, do it manually.
12:07:07 Then I debug the PHP code by calling it directly specifying.
12:07:14 The parameters and then So this is the title search. So.
12:07:24 And then I make the HTML code with the form. Which says title search. Now the part of title is what was being passed.
12:07:39 So.
12:07:42 That parameter passing is a little bit non standard to people who have not worked on the web before. Now I have
12:07:55 When I write code. I love to debug it and I intentionally leave some of my debugging statements in there.
12:08:04 PHP, of course, begins with the Less than question mark PHP.
12:08:13 And it ends with.
12:08:16 Question mark greater than that's how you bracket it and you name the file. Dot p HP.
12:08:24 When I do anything with PHP in a database, I put in a A line that says connecting to database.
12:08:33 So that I know something is happening. And if I modify that. I can tell which version. Of the pilots working sometimes because of caching.
12:08:48 Files don't get updated. Now, what I'm doing, PHP, there's more than one approach.
12:08:55 Do you have to use PHP? There's other approaches. But does some of the things that are most important The server name.
12:09:04 Now, you can specify the name of the server. But since you're HP code is always going to be running.
12:09:14 On a server. It's usually okay to say the server name is local host. Which defaults to the machine you're on.
12:09:25 Now, in my case.
12:09:30 I'm using the username K. Toky, which is my, a username.
12:09:37 Then I have a password. Now this is the password for the database. I'm not going to give you the password for my the Murray account, but password for the database in honor of my second grandson.
12:09:54 Is Samuel. And the name of the database is. And off Driftless.
12:10:03 So I use those.
12:10:06 As names and if. Let any other name constant if I change the password I could just change it once without having to change it other places, etc.
12:10:17 And then. Bye. By the way, when you do. Set up the password.
12:10:26 You need to do an alter roll to initialize the password. And earlier I had to reinitialize password for some reason.
12:10:35 Now.
12:10:38 PHP is usually doing SQL instructions. So dollar query is what I use for whatever the current.
12:10:49 Query I'm playing with but
12:10:53 Any SQL function. And go there.
12:11:03 Now, you may recall. That I set up here. Part of title is banana.
12:11:13 So.
12:11:16 Because in my HTML, I said. Method get.
12:11:28 That means I can get. We value this way. I specify part of title is the name that's associated with the value.
12:11:41 And when I say dollar underscore get, that gives me the value that's associated with part of title, which in that particular example was called banana.
12:11:54 And once I figured that out, I stored in a variable called the new part of title, the part of title for this call.
12:12:04 Is banana. And the first time I do it.
12:12:12 By putting in a print or an echo.
12:12:17 Echo and print work slightly differently, but that's not really a big concern for this class.
12:12:25 Right print out part of title.
12:12:29 And then I print out. New part of title now that squiggle brackets. Around new part of title means that it will print what the value of new part of title rather than the actual letters, new part of title.
12:12:47 Notice that's in quotes.
12:12:50 So. If you didn't realize about that attribute, you would expect it to print less Google dollar, new part of time, writes Google.
12:13:01 But the squiggles our special. And that means the value. So you can print the value and you can print the better value.
12:13:12 I have that as a debug statement.
12:13:18 Sometimes EGO works better for debug statements and I did a couple of trial queries. Earlier in here, you know.
12:13:32 So, but the query that I ended up settling on.
12:13:39 That's a long query.
12:13:47 For readabilities, I'm going to break it up a little bit.
12:13:54 And hope I don't. Mess it up for later. Okay.
12:14:01 What I'm interested in given a word in the title in this case I'm interested in the date.
12:14:10 The title and the link from an episode. But the episode. Information is not all in one table of course.
12:14:20 So I selected from episode table, network, description, and cable, natural join link table that gives basically all the information about an episode in one table.
12:14:30 And I classify it as where.
12:14:36 Basically the lowercase version of But Anna, the new part of title. Appears in. The lowercase version.
12:14:48 Of the episode title.
12:14:52 And just to make sure I did my query right. Sometimes I echo the query.
12:15:00 It should come out looking exactly like an SQL instruction. Look at what's in the quotes.
12:15:09 It's an SQL instruction down to the semicolon at the end of it.
12:15:17 Now.
12:15:22 PHP needs. A try-catch. So that's my standard try-catch formula.
12:15:29 Do, at least turn an error message if something happens. And in fact, earlier presentation on the HTML maybe.
12:15:38 I accidentally generated one of those errors. Now.
12:15:48 We're going to try to connect to the database. And just remember this. We're going to make a connection variable.
12:16:01 Holds the whole connection. Not just text or string. And the syntax is different if you're in different kinds of SQL.
12:16:13 This line is one of the most different ones. But in. Post GRE SQL.
12:16:19 You're asking for a new connection. What do you need for the connection?
12:16:27 The Post GRE SQL host. Is the local server. The database name. The username and the password.
12:16:42 Noote the positions of the quotes.
12:16:46 Just do it the way that works. That, ideally, establishes a connection. If it fails in establishing the connection, It will go down and print an error message.
12:17:04 For each
12:17:08 Now, this is a for loop. It's called a for each loop, but think of it as a for loop.
12:17:15 We want to go through. Each road results. From the SQL query.
12:17:25 So this dollar connection. Is the
12:17:32 Connection that's actually made to the database.
12:17:38 And this query. Is the SQL query we're making. And so what this is saying is for each.
12:17:51 Line of the query. Which we will call row.
12:17:59 We're going to do whatever's in these squiggle brackets.
12:18:04 Now, Just say. Grint underscore are. Dollar row, it will print The whole row.
12:18:18 And since it's in a for each loop. It will rent all of the data that's resulting.
12:18:26 From your query. But if you don't need All the data resulting from the query. You can specify.
12:18:39 I want to print out the episode date field from the row. I want to print out the episode. Title field from the row.
12:18:51 And if I want, I can embed.
12:18:55 A break line.
12:18:59 That's actually HTML code. And I want to print the episode link.
12:19:08 And then this is one of the things. That I do for debugging purposes and clarity purposes, it is possible.
12:19:17 That your query for whatever reason will get nothing back. If it gets nothing back printing for each row will print nothing because there are no rows.
12:19:30 So I usually end with. End of list.
12:19:37 And then I say, okay, I'm done with the connection.
12:19:43 That Sintax can be adapted to do a lot of the things.
12:19:53 You might like to do. Now I'm going to.
12:19:59 Go back up. Since it's a string, I really hate to have line breaks in a string if I can avoid it.
12:20:07 I don't know why I must have had trouble with it. One time, but.
12:20:15 That's my title, search. And. I think I already showed you the banana.
12:20:33 So I will go up.
12:20:45 And I will. Call this PHP. Instead of with banana, I'll just look for the word diet.
12:20:59 And I get a podcast about. Top sources of salt in the American diet. If you're want general interest medical information, that's what this podcast is about.
12:21:14 Okay, so. I will mention.
12:21:24 That it's called from. Hey, form in HTML, you know, the part of titles.
12:21:40 Now. Similarly, there's a description search.
12:21:53 PHP file.
12:21:59 And.
12:22:02 Again.
12:22:05 That's,
12:22:14 Oh, okay. I see it here. No, I This is something I was looking. I was using the part of title name.
12:22:22 Even in the description.
12:22:28 So I will have to check. And make sure I didn't. Pick something improperly. But,
12:22:41 Again, I can call this.
12:22:55 This is not really about the podcast. But. It's called. A similar way.
12:23:03 And I have similar statements connected to database. I play with the queries.
12:23:14 And my query.
12:23:19 That I ended up with this case.
12:23:28 I select. Episode.
12:23:33 Where that thing that I typed. New part of title. Is in the description. And the biggest difference is not in the episode title.
12:23:47 It's in the episode description. Description table plugged up the sub-. But the A lot of the same stuff is.
12:24:00 Is there there's The biggest difference. Between the 2 PHP files. Is the query.
12:24:10 So once you've got your query checked with SQL, you can usually just adapt it in.
12:24:21 Let's see.
12:24:27 And. Now the wordy 3 point HTM file.
12:24:36 Calls. You search for port P. PHP.
12:24:47 Now word search function.
12:24:51 Does some funky things with word searches. And has several different versions of the word search function. So I'm not really worried about that, but if anyone's curious, I'd be glad to go over it with you.
12:25:05 But again, this one. We've mentioned in the HTML version. There's a huge table with lots of words.
12:25:13 And in or all of those words can be sent.
12:25:19 Then for each word I have an if statement. The word in the HTML for word 1 one is athlete.
12:25:29 Now. In this particular one, I wanted to If someone was looking for athletic. Or athlete, I wanted to catch it.
12:25:41 So I searched word for athlete. So it would catch any variations. And then manually I decided, you know what, if somebody's asking about a sport.
12:25:53 Or the activity. Or about running. Or about shin splits, that's one of them.
12:26:00 Maybe that should show up. If you're looking for things about athletics.
12:26:07 So. I added those searches if someone clicks. The word for athlete. In the HTML.
12:26:16 It will find all of those.
12:26:21 And for allergy. I search for Allerg. So the allergy, allergies would all show up.
12:26:35 And if you're interested in bladders, yeah, my son's a doctor, okay.
12:26:42 My wife's a nurse practitioner. Bladder, Urol, to urination in continents.
12:26:47 Is someone looking for information on Blad or anything that mentions any of those forwards should show up?
12:26:54 Notice that I have different versions. Of the search word. Thank you. Or that and so on.
12:27:03 Down the lines.
12:27:10 Okay. Now.
12:27:14 In you might be done now and that's okay. In the registration
12:27:25 Example.
12:27:32 This is
12:27:36 Add a course.
12:27:40 Or add a section, I think it's added section. But I still have server name. dB name, username.
12:27:50 Password. And in this case. I have something called new discipline name. New ID number and new section code.
12:28:01 Okay, well that's the giveaway. It's adding a section. And this version. Really under those registration for one semester at a time.
12:28:13 And then I have debug. Prince statements.
12:28:22 Do this one just to make sure. But it got. Passed incorrectly. Now my query.
12:28:32 And I, you know. It was good to me, inserting is not a question. But Is that bothers who live with it?
12:28:44 My query. My first query is insert into this section table the discipline number in section given the values.
12:28:56 Included. Now notice. We have single quotes there. Which means that
12:29:06 When it echoes to the SQL. It has values left parentheses, single quote.
12:29:16 And then the CIS. Uncalled because you need to put quotes. Around string values in SQL.
12:29:27 And then my second query.
12:29:31 Is select start from. Section, that's my debug print.
12:29:41 So first I will try my connection.
12:29:49 Then.
12:29:55 Hmm. I haven't even looked for this for a little while. I initialize number of rows.
12:30:02 And you know, that dollar doesn't mean string, that dollar sign means variable. So that 0 is an integer.
12:30:10 For each connection, Korea's row did that before.
12:30:15 I do. A number of rows. Incrementation. Add one to the number rows.
12:30:25 This will count how many rows. So that my. PHP code. Knows how many rows were printed.
12:30:33 And of course I could print the whole row information. I could echo the number of rows so far.
12:30:42 You know. Left over debugs. But I'm really only interested in the discipline number and section.
12:30:54 But this will print it for. Every section.
12:31:00 Okay, so that I can look to see that the course I included. I'm the section I'm adding.
12:31:08 Is not a duplication and it's actually there.
12:31:12 Then you remember I did the number of rows.
12:31:21 Yes, after I've added a section. Good says the number of rows is 0.
12:31:31 Maybe something went wrong. You might not have added the section.
12:31:39 Yeah, now I can go on. To a second query.
12:31:48 Where I re-initialize the number rows to 0 just in case. I use query 2.
12:31:54 And as you may recall from up above. Query too is just
12:32:04 Good everything from the section. Okay, so basically if you Add something. I don't know if I said it quite right before but if you're insert into a section and nothing comes out
12:32:23 Maybe it didn't get added.
12:32:27 But as a debug.
12:32:31 Let me make sure I'm, yeah, query 2.
12:32:37 Query 2 is about Everything in the section query one is the insertion.
12:32:46 And so for query 2, I print all the sections available in a larger university. Of course, you wouldn't want to do all the sections of available for the whole thing.
12:32:57 But it would be certainly appropriate to do all the sections. Where the discipline is the same.
12:33:08 Okay, so you see that there's really strong parallels.
12:33:18 But so. This is dropping, probably dropping a section, but let's check. Yeah, this is.
12:33:30 Delete a section.
12:33:37 From the list. And
12:33:43 It goes through and deletes the section and checks to make sure there is something resulted. And then.
12:33:53 Yeah, as a check.
12:33:57 We print the list so that you can manually check to share. Be sure it's deleted.
12:34:03 There's a lot of these cute little examples hiring a faculty member.
12:34:16 The query is Insert a new faculty member. So basically if you have your SQL correct.
12:34:25 With just a little bit of knowledge of. The syntax here.
12:34:33 You can make a PHP. Okay. That does whatever your SQL query is. And if anyone, especially those of you who not have ever events.
12:34:45 Web design, but do want to do it in your. So now here I commented out.
12:34:57 My list of all the faculty once I got it working. You can certainly do that. Now.
12:35:13 This is, and I'm not sure that all of these things work because they were. Ported over from another machine.
12:35:21 That had a different IP address but Get the idea.
12:35:31 You can.
12:35:38 Request a reservation.
12:35:42 Now, if it's working.
12:35:48 You know, you're passing in a year, a month, and a day that you want a reservation for at a bed and breakfast.
12:35:58 And the query.
12:36:05 Is and then you say
12:36:12 For each query, you don't have to worry about the details of it. But. If you reserve it.
12:36:21 You put it as taken.
12:36:27 Yeah, if someone wants to go through in depth details. We glad to help you, but.
12:36:42 So if you well again, the count. 24 it does use PHP but it's only like adding by one and subtracting by one.
12:36:54 I think the health cast is the
12:36:59 Best example. But I do have other examples besides registered haven. In my web design area. Which is
12:37:12 Exceptable the same way the database. Design area is. But if anybody has any Questions or problems or needs help.
12:37:23 Good in PHP, but I'll say one more time. PHP is not the function of the database class.
12:37:32 PHP is something you can use to manipulate a database. If you have questions about that. Especially with respect to your project, please feel free to ask.