Difference between revisions of "PHP-and-MySQL/C3/MySQL-Part-3/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
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:06
+
|0:07
 
|To do this we will use our "mysql query" function.
 
|To do this we will use our "mysql query" function.
 
|-
 
|-
|0:10
+
|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:21
+
|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:36
+
|0:37
 
|we can see that there is nothing in here.
 
|we can see that there is nothing in here.
 
|-
 
|-
 
|0:40
 
|0:40
|These are just our field names here.
+
|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:59
+
|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:01
+
|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:30
+
|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:34
+
|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:40
+
|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:50
+
|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:55
+
|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:03
+
|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:10
+
|2:15
 
|We don't need to insert our id  here.
 
|We don't need to insert our id  here.
 
|-
 
|-
|2:13
+
|2:18
 
|Our next one is firstname - so I'll say "Alex".
 
|Our next one is firstname - so I'll say "Alex".
 
|-
 
|-
|2:17
+
|2:22
 
|My lastname I'll say is "Garrett".
 
|My lastname I'll say is "Garrett".
 
|-
 
|-
|2:20
+
|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:26
+
|2:31
 
|I'll put this in the particular structure.
 
|I'll put this in the particular structure.
 
|-
 
|-
|2:28
+
|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:39
+
|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:45
+
|2:50
 
|Its the year in long format.
 
|Its the year in long format.
 
|-
 
|-
|2:47
+
|2:52
 
|Next is the month and then the day.
 
|Next is the month and then the day.
 
|-
 
|-
|2:48
+
|2:55
 
|So 2009 02 23 which is 23rd of the 2nd, 2009.
 
|So 2009 02 23 which is 23rd of the 2nd, 2009.
 
|-
 
|-
|2:54
+
|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.
 
|-
 
|-
|3:05
+
|03:13
 
|So this will be structured like that.
 
|So this will be structured like that.
 
|-  
 
|-  
|3:09
+
|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.
 
|-
 
|-
|3:13
+
|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.
 
|-
 
|-
|3:24
+
|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.
 
|-
 
|-
|3:28
+
|03:34
 
|Presuming that will work, we can run this.
 
|Presuming that will work, we can run this.
 
|-
 
|-
|3:30
+
|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.
 
|-
 
|-
|3:38
+
|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.
 
|-
 
|-
|3:44
+
|03:50
 
|Ok so refreshing our page.  
 
|Ok so refreshing our page.  
 
|-
 
|-
|3:48
+
|03:53
 
|What you see is from the last tutorial.  
 
|What you see is from the last tutorial.  
 
|-  
 
|-  
|3:50
+
|03:52
 
|ummmm..... Lets comment this out.
 
|ummmm..... Lets comment this out.
 
|-
 
|-
|3:56
+
|03:59
 
|Lets ignore this.
 
|Lets ignore this.
 
|-
 
|-
|3:57
+
|04:01
 
|This will completely ignore this part of tutorial.
 
|This will completely ignore this part of tutorial.
 
|-
 
|-
|4:02
+
|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.
 
|-
 
|-
|4:10
+
|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.
 
|-
 
|-
|4:14
+
|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.
 
|-
 
|-
|4:26
+
|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.
 
|-
 
|-
|4:33
+
|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.
 
|-
 
|-
|4:39
+
|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.
 
|-
 
|-
|4:43
+
|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.
 
|-  
 
|-  
|4:53
+
|05:02
 
|You should know that by now.
 
|You should know that by now.
 
|-
 
|-
|4:54
+
|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.
 
|-
 
|-
|5:00
+
|05:09
|So first I will comment these 2 lines so we don't have to rerun this.
+
|So first I will comment these 2 lines so we don't have to re-run this.
 
|-
 
|-
|5:04
+
|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".
 
|-
 
|-
|5:08
+
|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.
 
|-
 
|-
|5:14
+
|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.
 
|-
 
|-
|5:20
+
|05:32
|So here we will type "UPDATE" and we are going to say the table name which is "people".
+
|And here you  will type "UPDATE" and we are going to say the table name which is "people".
 
|-
 
|-
|5:28
+
|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.
 
|-
 
|-
|5:34
+
|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.
 
|-
 
|-
|5:47
+
|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.
 
|-  
 
|-  
|5:54
+
|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.
 
|-
 
|-
|5:57
+
|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.
 
|-
 
|-
|6:08
+
|06:18
 
|Lets have a look here.
 
|Lets have a look here.
 
|-
 
|-
|6:10
+
|06:23
 
|Otherwise it will update everyone else's.
 
|Otherwise it will update everyone else's.
 
|-
 
|-
|6:12
+
|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.  
 
|-
 
|-
|6:16
+
|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".
 
|-
 
|-
|6:24
+
|06:41
 
|But we can also say "AND lastname equals Garrett".
 
|But we can also say "AND lastname equals Garrett".
 
|-
 
|-
|6:26
+
|06:46 
|However if we still have two people in the database with the same firstname and lastname, we are still running the same risk as before.
+
|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.
 
|-
 
|-
|6:43
+
|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.
 
|-
 
|-
|6:48
+
|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.
 
|-
 
|-
|6:53
+
|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.
 
|-
 
|-
|7:00
+
|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.
 
|-
 
|-
|7:08
+
|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.
 
|-
 
|-
|7:15
+
|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.
 
|-  
 
|-  
|7:18
+
|07:34
 
|I could have updated my lastname.
 
|I could have updated my lastname.
 
|-
 
|-
|7:20
+
|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.
 
|-
 
|-
|7:24
+
|07:40
 
|So I said this record which is this long line here.
 
|So I said this record which is this long line here.
 
|-
 
|-
|7:28
+
|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.
 
|-
 
|-
|7:35
+
|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.
 
|-
 
|-
|7:54
+
|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.
 
|-
 
|-
|8:04
+
|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.

Contributors and Content Editors

Krupali, PoojaMoolya, Pratik kamble, Sandhya.np14, Sneha