Difference between revisions of "PHP-and-MySQL/C3/MySQL-Part-3/English-timed"
From Script | Spoken-Tutorial
| Line 2: | Line 2: | ||
!Time | !Time | ||
!Narration | !Narration | ||
| − | |- | + | |- |
|0:00 | |0:00 | ||
|Hi, welcome back. In this tutorial we will write some data into a database. | |Hi, welcome back. In this tutorial we will write some data into a database. | ||
|- | |- | ||
| − | |0: | + | |0:07 |
|To do this we will use our "mysql query" function. | |To do this we will use our "mysql query" function. | ||
|- | |- | ||
| − | |0: | + | |0:12 |
|Now you can see here that we have our... records.... | |Now you can see here that we have our... records.... | ||
|- | |- | ||
| Line 15: | Line 15: | ||
|I am redoing this tutorial because the first time I did it, it did not work. | |I am redoing this tutorial because the first time I did it, it did not work. | ||
|- | |- | ||
| − | |0: | + | |0:22 |
|So first of all, I will just delete this data here. | |So first of all, I will just delete this data here. | ||
|- | |- | ||
| Line 21: | Line 21: | ||
|Well... ok... So we have a blank table, we have no data in our table whatsoever at the moment. | |Well... ok... So we have a blank table, we have no data in our table whatsoever at the moment. | ||
|- | |- | ||
| − | |0: | + | |0:37 |
|we can see that there is nothing in here. | |we can see that there is nothing in here. | ||
|- | |- | ||
|0:40 | |0:40 | ||
| − | | | + | |There are just our field names here. |
|- | |- | ||
|0:43 | |0:43 | ||
| Line 36: | Line 36: | ||
|So... "write" and we will use "mysql query" function. | |So... "write" and we will use "mysql query" function. | ||
|- | |- | ||
| − | |0: | + | |0:57 |
|And this takes exactly 1 parameter which is our sql query. | |And this takes exactly 1 parameter which is our sql query. | ||
|- | |- | ||
| − | |1: | + | |1:02 |
|To do this, we will type "INSERT" to insert data. | |To do this, we will type "INSERT" to insert data. | ||
|- | |- | ||
| Line 54: | Line 54: | ||
|If I type anything in lowercase it means its either a table name, database name or it is data that I'm writing to the database. | |If I type anything in lowercase it means its either a table name, database name or it is data that I'm writing to the database. | ||
|- | |- | ||
| − | |1: | + | |1:28 |
|So "INSERT INTO people" because that is our table name here. | |So "INSERT INTO people" because that is our table name here. | ||
|- | |- | ||
| − | |1: | + | |1:33 |
| "INSERT INTO people" and then "VALUES" and then in brackets we will create a little area for each value. | | "INSERT INTO people" and then "VALUES" and then in brackets we will create a little area for each value. | ||
|- | |- | ||
| − | |1: | + | |1:42 |
|So we got 1,2,3,4,5. | |So we got 1,2,3,4,5. | ||
|- | |- | ||
| − | |1:46 | + | |1:46 |
|There are 5 fields so we need exactly 5 pieces of database written here. | |There are 5 fields so we need exactly 5 pieces of database written here. | ||
|- | |- | ||
| − | |1: | + | |1:53 |
|We need the id, firstname, lastname, all the way down to gender. | |We need the id, firstname, lastname, all the way down to gender. | ||
|- | |- | ||
| − | |1: | + | |1:58 |
|These are created in or created using single quotes each separated by comas. | |These are created in or created using single quotes each separated by comas. | ||
|- | |- | ||
| − | |2: | + | |2:07 |
|The reason we don't use double quotes is because we've got these at the end or rather start and end over here. | |The reason we don't use double quotes is because we've got these at the end or rather start and end over here. | ||
|- | |- | ||
| − | |2: | + | |2:15 |
|We don't need to insert our id here. | |We don't need to insert our id here. | ||
|- | |- | ||
| − | |2: | + | |2:18 |
|Our next one is firstname - so I'll say "Alex". | |Our next one is firstname - so I'll say "Alex". | ||
|- | |- | ||
| − | |2: | + | |2:22 |
|My lastname I'll say is "Garrett". | |My lastname I'll say is "Garrett". | ||
|- | |- | ||
| − | |2: | + | |2:25 |
|For my date of birth I'll create a date function which equals to a variable "date" | |For my date of birth I'll create a date function which equals to a variable "date" | ||
|- | |- | ||
| − | |2: | + | |2:31 |
|I'll put this in the particular structure. | |I'll put this in the particular structure. | ||
|- | |- | ||
| − | |2: | + | |2:35 |
|We can see from our database over here that when we go to insert a value, we can scroll down and see that our calender function has dates on it. | |We can see from our database over here that when we go to insert a value, we can scroll down and see that our calender function has dates on it. | ||
|- | |- | ||
| − | |2: | + | |2:44 |
|So on clicking 23rd, we can see the structure this field takes. | |So on clicking 23rd, we can see the structure this field takes. | ||
|- | |- | ||
| − | |2: | + | |2:50 |
|Its the year in long format. | |Its the year in long format. | ||
|- | |- | ||
| − | |2: | + | |2:52 |
|Next is the month and then the day. | |Next is the month and then the day. | ||
|- | |- | ||
| − | |2: | + | |2:55 |
|So 2009 02 23 which is 23rd of the 2nd, 2009. | |So 2009 02 23 which is 23rd of the 2nd, 2009. | ||
|- | |- | ||
| − | | | + | |03:02 |
|So what we can do here is we can structure our date function in capital Y m and then d using hyphen in between to get the structure we need. | |So what we can do here is we can structure our date function in capital Y m and then d using hyphen in between to get the structure we need. | ||
|- | |- | ||
| − | | | + | |03:13 |
|So this will be structured like that. | |So this will be structured like that. | ||
|- | |- | ||
| − | | | + | |03:16 |
|This will equal to this and that will be the current date. | |This will equal to this and that will be the current date. | ||
|- | |- | ||
| − | | | + | |03:20 |
|Using the date and presuming that is in the structure of our date, we can insert it into our table here. | |Using the date and presuming that is in the structure of our date, we can insert it into our table here. | ||
|- | |- | ||
| − | | | + | |03:28 |
|The last one is gender and since I'm a male, I'm putting in "M" for male. | |The last one is gender and since I'm a male, I'm putting in "M" for male. | ||
|- | |- | ||
| − | | | + | |03:34 |
|Presuming that will work, we can run this. | |Presuming that will work, we can run this. | ||
|- | |- | ||
| − | | | + | |03:37 |
|But before that, we could say "or die" at the end followed by a mysql error. | |But before that, we could say "or die" at the end followed by a mysql error. | ||
|- | |- | ||
| − | | | + | |03:44 |
|I will skip that for now but feel free to add them if you like. | |I will skip that for now but feel free to add them if you like. | ||
|- | |- | ||
| − | | | + | |03:50 |
|Ok so refreshing our page. | |Ok so refreshing our page. | ||
|- | |- | ||
| − | | | + | |03:53 |
|What you see is from the last tutorial. | |What you see is from the last tutorial. | ||
|- | |- | ||
| − | | | + | |03:52 |
|ummmm..... Lets comment this out. | |ummmm..... Lets comment this out. | ||
|- | |- | ||
| − | | | + | |03:59 |
|Lets ignore this. | |Lets ignore this. | ||
|- | |- | ||
| − | | | + | |04:01 |
|This will completely ignore this part of tutorial. | |This will completely ignore this part of tutorial. | ||
|- | |- | ||
| − | | | + | |04:08 |
|Ok - so back to the code that I am currently showing and lets refresh. | |Ok - so back to the code that I am currently showing and lets refresh. | ||
|- | |- | ||
| − | | | + | |04:14 |
| I've refreshed it twice so accordingly 2 records have been put in. | | I've refreshed it twice so accordingly 2 records have been put in. | ||
|- | |- | ||
| − | | | + | |04:24 |
|But by going back to browse and scrolling down we can see, lets delete 1 of these, we can see the data I just specified has been put into the database. | |But by going back to browse and scrolling down we can see, lets delete 1 of these, we can see the data I just specified has been put into the database. | ||
|- | |- | ||
| − | | | + | |04:35 |
|In fact what I have done is I have put my date of birth as the current date which I didn't mean to do. | |In fact what I have done is I have put my date of birth as the current date which I didn't mean to do. | ||
|- | |- | ||
| − | | | + | |04:43 |
|I don't want my date of birth as the current date because I was not born today. | |I don't want my date of birth as the current date because I was not born today. | ||
|- | |- | ||
| − | | | + | |04:48 |
|My firstname is ok. My lastname is ok. My gender is fine. | |My firstname is ok. My lastname is ok. My gender is fine. | ||
|- | |- | ||
| − | | | + | |04:53 |
|We can see that my id is 6 at the moment and the next time we insert a record this would go up to 7 and then again to 8. | |We can see that my id is 6 at the moment and the next time we insert a record this would go up to 7 and then again to 8. | ||
|- | |- | ||
| − | | | + | |05:02 |
|You should know that by now. | |You should know that by now. | ||
|- | |- | ||
| − | | | + | |05:03 |
|Next what I'll show you is how to change my date of birth because I have made a mistake. | |Next what I'll show you is how to change my date of birth because I have made a mistake. | ||
|- | |- | ||
| − | | | + | |05:09 |
| − | |So first I will comment these 2 lines so we don't have to | + | |So first I will comment these 2 lines so we don't have to re-run this. |
|- | |- | ||
| − | | | + | |05:15 |
|And I'll create a new variable. We will just comment this as "update data". | |And I'll create a new variable. We will just comment this as "update data". | ||
|- | |- | ||
| − | | | + | |05:20 |
|Current variable called "update" and that's equal to "mysql query" function. | |Current variable called "update" and that's equal to "mysql query" function. | ||
|- | |- | ||
| − | | | + | |05:26 |
|And inside the parameter that we are calling is "mysql query" code itself. | |And inside the parameter that we are calling is "mysql query" code itself. | ||
|- | |- | ||
| − | | | + | |05:32 |
| − | | | + | |And here you will type "UPDATE" and we are going to say the table name which is "people". |
|- | |- | ||
| − | | | + | |05:38 |
|Then we will say "SET" and we need to pick a particular field in which to set. | |Then we will say "SET" and we need to pick a particular field in which to set. | ||
|- | |- | ||
| − | | | + | |05:43 |
|This happens to be the "d o b" and that's equal to my actual date of birth which is 1989, the year I was born in and the month is November and the day I was born is 16th. | |This happens to be the "d o b" and that's equal to my actual date of birth which is 1989, the year I was born in and the month is November and the day I was born is 16th. | ||
|- | |- | ||
| − | | | + | |05:57 |
|By running this command what we are actually doing is we are updating everyone's date of birth in this table to this. | |By running this command what we are actually doing is we are updating everyone's date of birth in this table to this. | ||
|- | |- | ||
| − | | | + | |06:05 |
|That's because we've not specified where we want to update this. | |That's because we've not specified where we want to update this. | ||
|- | |- | ||
| − | | | + | |06:10 |
|But we can do is after this we can say "WHERE id=6" because my unique id is 6. | |But we can do is after this we can say "WHERE id=6" because my unique id is 6. | ||
|- | |- | ||
| − | | | + | |06:18 |
|Lets have a look here. | |Lets have a look here. | ||
|- | |- | ||
| − | | | + | |06:23 |
|Otherwise it will update everyone else's. | |Otherwise it will update everyone else's. | ||
|- | |- | ||
| − | | | + | |06:26 |
|Remember I said the id is unique. Its better to say update my id. | |Remember I said the id is unique. Its better to say update my id. | ||
|- | |- | ||
| − | | | + | |06:32 |
|What I could do instead, is say, "WHERE firstname equals Alex". However this will update every record that has firstname "Alex". | |What I could do instead, is say, "WHERE firstname equals Alex". However this will update every record that has firstname "Alex". | ||
|- | |- | ||
| − | | | + | |06:41 |
|But we can also say "AND lastname equals Garrett". | |But we can also say "AND lastname equals Garrett". | ||
|- | |- | ||
| − | | | + | |06:46 |
| − | |However if we | + | |However if we will have two people in the database with the same firstname and lastname, we are still running the same risk as before. |
|- | |- | ||
| − | | | + | |06:54 |
|So its best to use our "unique" and thats the key word "unique id" which for me is 6. | |So its best to use our "unique" and thats the key word "unique id" which for me is 6. | ||
|- | |- | ||
| − | | | + | |07:00 |
|So at the moment, you can see that my date of birth is set to 2009 which is the current date. | |So at the moment, you can see that my date of birth is set to 2009 which is the current date. | ||
|- | |- | ||
| − | | | + | |07:06 |
|But by refreshing this page, nothing's happened because we are just running a command. | |But by refreshing this page, nothing's happened because we are just running a command. | ||
|- | |- | ||
| − | | | + | |07:11 |
|Now if we click on browse to refresh and we scroll down, we can see that it has changed to what we specified and everything else has been left intact. | |Now if we click on browse to refresh and we scroll down, we can see that it has changed to what we specified and everything else has been left intact. | ||
|- | |- | ||
| − | | | + | |07:21 |
|So if you need to update data in your database or anything like that, you can specify what data you want to update. | |So if you need to update data in your database or anything like that, you can specify what data you want to update. | ||
|- | |- | ||
| − | | | + | |07:29 |
|I used "dob" and that equals to the date of birth that was necessary. | |I used "dob" and that equals to the date of birth that was necessary. | ||
|- | |- | ||
| − | | | + | |07:34 |
|I could have updated my lastname. | |I could have updated my lastname. | ||
|- | |- | ||
| − | | | + | |07:36 |
|You also need to specify where you want this to be updated. | |You also need to specify where you want this to be updated. | ||
|- | |- | ||
| − | | | + | |07:40 |
|So I said this record which is this long line here. | |So I said this record which is this long line here. | ||
|- | |- | ||
| − | | | + | |07:46 |
|These are called records and I specified "WHERE" the id was equal to 6 and that has updated my unique record. | |These are called records and I specified "WHERE" the id was equal to 6 and that has updated my unique record. | ||
|- | |- | ||
| − | | | + | |07:56 |
|So that's what you have learnt - how to insert values and also how to update some values if you get it wrong like I did or if you just want to update some data which happens most of the time when your doing your databases. | |So that's what you have learnt - how to insert values and also how to update some values if you get it wrong like I did or if you just want to update some data which happens most of the time when your doing your databases. | ||
|- | |- | ||
| − | | | + | |08:10 |
|Ok - so join me in the next part to find out how to start reading from your database and display the data to the user. | |Ok - so join me in the next part to find out how to start reading from your database and display the data to the user. | ||
|- | |- | ||
| − | | | + | |08:17 |
|See you soon. This is Juanita Jayakar dubbing for the Spoken Tutorial Project. | |See you soon. This is Juanita Jayakar dubbing for the Spoken Tutorial Project. | ||
Revision as of 10:35, 12 July 2013
| Time | Narration |
|---|---|
| 0:00 | Hi, welcome back. In this tutorial we will write some data into a database. |
| 0:07 | To do this we will use our "mysql query" function. |
| 0:12 | Now you can see here that we have our... records.... |
| 0:16 | I am redoing this tutorial because the first time I did it, it did not work. |
| 0:22 | So first of all, I will just delete this data here. |
| 0:29 | Well... ok... So we have a blank table, we have no data in our table whatsoever at the moment. |
| 0:37 | we can see that there is nothing in here. |
| 0:40 | There are just our field names here. |
| 0:43 | To begin with, in here, lets just comment this. |
| 0:47 | So "write some data". Then we will set up a query that will write data. |
| 0:52 | So... "write" and we will use "mysql query" function. |
| 0:57 | And this takes exactly 1 parameter which is our sql query. |
| 1:02 | To do this, we will type "INSERT" to insert data. |
| 1:06 | We are going to say "INSERT INTO". |
| 1:09 | Now the reason I've typed this in capitals is because its sql code. |
| 1:14 | If I type anything in uppercase it means it is sql code. |
| 1:19 | If I type anything in lowercase it means its either a table name, database name or it is data that I'm writing to the database. |
| 1:28 | So "INSERT INTO people" because that is our table name here. |
| 1:33 | "INSERT INTO people" and then "VALUES" and then in brackets we will create a little area for each value. |
| 1:42 | So we got 1,2,3,4,5. |
| 1:46 | There are 5 fields so we need exactly 5 pieces of database written here. |
| 1:53 | We need the id, firstname, lastname, all the way down to gender. |
| 1:58 | These are created in or created using single quotes each separated by comas. |
| 2:07 | The reason we don't use double quotes is because we've got these at the end or rather start and end over here. |
| 2:15 | We don't need to insert our id here. |
| 2:18 | Our next one is firstname - so I'll say "Alex". |
| 2:22 | My lastname I'll say is "Garrett". |
| 2:25 | For my date of birth I'll create a date function which equals to a variable "date" |
| 2:31 | I'll put this in the particular structure. |
| 2:35 | We can see from our database over here that when we go to insert a value, we can scroll down and see that our calender function has dates on it. |
| 2:44 | So on clicking 23rd, we can see the structure this field takes. |
| 2:50 | Its the year in long format. |
| 2:52 | Next is the month and then the day. |
| 2:55 | So 2009 02 23 which is 23rd of the 2nd, 2009. |
| 03:02 | So what we can do here is we can structure our date function in capital Y m and then d using hyphen in between to get the structure we need. |
| 03:13 | So this will be structured like that. |
| 03:16 | This will equal to this and that will be the current date. |
| 03:20 | Using the date and presuming that is in the structure of our date, we can insert it into our table here. |
| 03:28 | The last one is gender and since I'm a male, I'm putting in "M" for male. |
| 03:34 | Presuming that will work, we can run this. |
| 03:37 | But before that, we could say "or die" at the end followed by a mysql error. |
| 03:44 | I will skip that for now but feel free to add them if you like. |
| 03:50 | Ok so refreshing our page. |
| 03:53 | What you see is from the last tutorial. |
| 03:52 | ummmm..... Lets comment this out. |
| 03:59 | Lets ignore this. |
| 04:01 | This will completely ignore this part of tutorial. |
| 04:08 | Ok - so back to the code that I am currently showing and lets refresh. |
| 04:14 | I've refreshed it twice so accordingly 2 records have been put in. |
| 04:24 | But by going back to browse and scrolling down we can see, lets delete 1 of these, we can see the data I just specified has been put into the database. |
| 04:35 | In fact what I have done is I have put my date of birth as the current date which I didn't mean to do. |
| 04:43 | I don't want my date of birth as the current date because I was not born today. |
| 04:48 | My firstname is ok. My lastname is ok. My gender is fine. |
| 04:53 | We can see that my id is 6 at the moment and the next time we insert a record this would go up to 7 and then again to 8. |
| 05:02 | You should know that by now. |
| 05:03 | Next what I'll show you is how to change my date of birth because I have made a mistake. |
| 05:09 | So first I will comment these 2 lines so we don't have to re-run this. |
| 05:15 | And I'll create a new variable. We will just comment this as "update data". |
| 05:20 | Current variable called "update" and that's equal to "mysql query" function. |
| 05:26 | And inside the parameter that we are calling is "mysql query" code itself. |
| 05:32 | And here you will type "UPDATE" and we are going to say the table name which is "people". |
| 05:38 | Then we will say "SET" and we need to pick a particular field in which to set. |
| 05:43 | This happens to be the "d o b" and that's equal to my actual date of birth which is 1989, the year I was born in and the month is November and the day I was born is 16th. |
| 05:57 | By running this command what we are actually doing is we are updating everyone's date of birth in this table to this. |
| 06:05 | That's because we've not specified where we want to update this. |
| 06:10 | But we can do is after this we can say "WHERE id=6" because my unique id is 6. |
| 06:18 | Lets have a look here. |
| 06:23 | Otherwise it will update everyone else's. |
| 06:26 | Remember I said the id is unique. Its better to say update my id. |
| 06:32 | What I could do instead, is say, "WHERE firstname equals Alex". However this will update every record that has firstname "Alex". |
| 06:41 | But we can also say "AND lastname equals Garrett". |
| 06:46 | However if we will have two people in the database with the same firstname and lastname, we are still running the same risk as before. |
| 06:54 | So its best to use our "unique" and thats the key word "unique id" which for me is 6. |
| 07:00 | So at the moment, you can see that my date of birth is set to 2009 which is the current date. |
| 07:06 | But by refreshing this page, nothing's happened because we are just running a command. |
| 07:11 | Now if we click on browse to refresh and we scroll down, we can see that it has changed to what we specified and everything else has been left intact. |
| 07:21 | So if you need to update data in your database or anything like that, you can specify what data you want to update. |
| 07:29 | I used "dob" and that equals to the date of birth that was necessary. |
| 07:34 | I could have updated my lastname. |
| 07:36 | You also need to specify where you want this to be updated. |
| 07:40 | So I said this record which is this long line here. |
| 07:46 | These are called records and I specified "WHERE" the id was equal to 6 and that has updated my unique record. |
| 07:56 | So that's what you have learnt - how to insert values and also how to update some values if you get it wrong like I did or if you just want to update some data which happens most of the time when your doing your databases. |
| 08:10 | Ok - so join me in the next part to find out how to start reading from your database and display the data to the user. |
| 08:17 | See you soon. This is Juanita Jayakar dubbing for the Spoken Tutorial Project. |
