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

From Script | Spoken-Tutorial
Jump to: navigation, search
 
(3 intermediate revisions by 2 users not shown)
Line 4: Line 4:
 
|-
 
|-
 
|00:01
 
|00:01
|Welcome to mySQL part 5. To echo our data to the user and to display the results from this, we'll need to use "while" statement.
+
|Welcome to mySQL part 5. To '''echo''' our data to the user and to display the results from this, we'll need to use '''while''' statement.
 
|-
 
|-
 
|00:12
 
|00:12
|As I mentioned, we have created a row variable and this is "=mysql_fetch_assoc".
+
|As I mentioned, we have created a '''$row''' variable and this is "= mysql_fetch_assoc".
 
|-
 
|-
 
|00:21
 
|00:21
|This is creating an associative array from our "extract" query which is here.
+
|This is creating an '''associative array''' from our "extract" '''query''' which is here.
 
|-
 
|-
 
|00:27
 
|00:27
|So we are selecting everything in the "people" table and ordering them by "id" in ascending order.
+
|So, we are selecting everything in the ''' "people" table''' and ordering them by "id" in ascending order.
 
|-
 
|-
 
|00:33
 
|00:33
|Inside our WHILE since we wrote row as an array and its an associative array, row[0] will be wrong because these are numeric.
+
|Inside our '''while''', since we wrote '''$row''' as an '''array''' and it's an '''associative array''', '''row[0]''' will be wrong because these are numeric.
 
|-
 
|-
 
|00:46
 
|00:46
|These are numeric id tags and instead of this we'll use our fieldnames, since this is associative.
+
|These are numeric '''id tags''' and instead of this we'll use our field names, since this is associative.
 
|-
 
|-
 
|00:59
 
|00:59
|So, instead of 0 1 2 3 4, we'll be using the actual name.
+
|So, instead of 0, 1, 2, 3, 4 we'll be using the actual name.
 
|-
 
|-
 
|01:05
 
|01:05
|Lets create variables from this. Let me say id and then firstname equals, we are using the same structure throughout.
+
|Let's create variables from this. Let me say '''$id''' and then '''$firstname''' equals, we are using the same structure throughout.
 
|-
 
|-
 
|01:15
 
|01:15
|So its quite easy to copy and paste this.
+
|So, it's quite easy to copy and paste this.
 
|-
 
|-
 
|01:19
 
|01:19
|Lets indent this.
+
|Let's indent this.
 
|-
 
|-
 
|01:24
 
|01:24
Line 37: Line 37:
 
|-
 
|-
 
|01:28
 
|01:28
|So that's five and then lets just change. This is a lazy act!
+
|So that's five and then let's just change. This is a lazy act!
 
|-
 
|-
 
|01:34
 
|01:34
|But its just a lot quicker to do this way.
+
|But it's just a lot quicker to do this way.
 
|-
 
|-
 
|01:38
 
|01:38
|So lastname and we have the date of birth. We also have the gender.
+
|So '''$lastname''' and we have the date of birth ($dob). We also have the '''$gender'''.
 
|-
 
|-
 
|01:47
 
|01:47
Line 49: Line 49:
 
|-
 
|-
 
|01:51
 
|01:51
|We need to use the "echo" command.
+
|We need to use the '''echo''' '''command'''.
 
|-
 
|-
 
|01:55
 
|01:55
|There might be a loop inside the middle, at the moment. So anything we echo out will be repeated.
+
|There might be a '''loop''' inside the middle, at the moment. So, anything we '''echo''' out will be repeated.
 
|-
 
|-
 
|02:02
 
|02:02
|Every record we have and that's right, too. We'll repeat this code.
+
|Every '''record''' we say and that's right too. We'll repeat this code.
 
|-
 
|-
 
|02:07
 
|02:07
|For example, I say text here. There are currently 4 records.
+
|For example, I say "Text" here. There are currently 4 records.
 
|-
 
|-
 
|02:13
 
|02:13
|After refreshing this page, you should see text echoed out 4 times.
+
|After refreshing this page, you should see '''Text''' echoed out 4 times.
 
|-
 
|-
 
|02:18
 
|02:18
|By typing out 4 times, this piece of code here represents every loop.
+
|By typing out 4 times, this piece of code here, represents every '''loop'''.
 
|-
 
|-
 
|02:24
 
|02:24
|Therefore we can incorporate for example, id or firstname or any other, that we have extracted from the database using our associative array.
+
|Therefore we can incorporate for example, '''$id''' or '''$firstname''' or any other, that we have extracted from the database using our associative array.
 
|-
 
|-
 
|02:36
 
|02:36
|Now I'll write firstname lastname was born on dob for date of birth and is and I'll put gender up there.
+
|Now I'll write: $firstname $lastname '''was born on''' $dob for date of birth '''and is''' and I'll put $gender up there.
 
|-
 
|-
|0 2:49
+
|02:49
|Not forgetting our linebreak. I'll refresh our page.  
+
|Not forgetting our '''line-break'''. I'll '''refresh''' our page.  
 
|-
 
|-
 
|02:54
 
|02:54
Line 82: Line 82:
 
|-
 
|-
 
|03:08
 
|03:08
|Okay, we have just given out the content of our table using this star, declared by this asterisk, where it collects every single data or every record.
+
|Okay, we have just given out the content of our table using this star, declared by this asterisk, where it collects every single data on every record.
 
|-
 
|-
 
|03:22
 
|03:22
|Now let me do this. I'll say IF gender==F then gender=female.
+
|Now let me do this. I'll say '''if $gender== "F" ''' then '''$gender= "Female" '''.
 
|-
 
|-
 
|03:39
 
|03:39
|The actual spelling of that and then let us say else gender=male. This is just rewriting the variable depending on the value.
+
|The actual spelling of that and then let us say '''else $gender= "Male"'''. This is just rewriting the variable depending on the value.
 
|-
 
|-
 
|03:50
 
|03:50
|If we refresh now, we can see this has changed to '''male male''' and '''female female'''. We also have some interesting ways of displaying this data.
+
|If we '''refresh''' now, we can see this has changed to male '''Male''' and female '''Female'''. We also have some interesting ways of displaying this data.
 
|-
 
|-
 
|04:00
 
|04:00
|At the moment I'm selecting from the people table and ordering by id and ascending order.
+
|At the moment, I'm selecting from the '''people''' table and ordering by '''id''' and ascending order.
 
|-
 
|-
 
|04:07
 
|04:07
|I can also order by descending id. You can see that this switches this data around.  
+
|I can also order by descending '''id'''. You can see that this switches this data around.  
 
|-
 
|-
 
|04:15
 
|04:15
|We can also order it by firstname. This will put this in descending alphabetical order and ascending will put this in ascending alphabetical order.
+
|We can also order it by '''firstname'''. This will put this in descending alphabetical order and ascending will put this in ascending alphabetical order.
 
|-
 
|-
 
|04:33
 
|04:33
|So we got ''' A D E and K'''.
+
|So, we got A, D, E, and K.
 
|-
 
|-
 
|04:36
 
|04:36
Line 109: Line 109:
 
|-
 
|-
 
|04:39
 
|04:39
|You could do the same with anything. Even Date of birth, as long as you include this over here.  
+
|You could do the same with anything. Even date of birth, as long as you include this over here.  
 
|-
 
|-
 
|04:46  
 
|04:46  
|Another thing to do is, let me just take this back to id and have this as descending. We can use this limit 1 or we can say limit 2, 3 or 4.
+
|Another thing to do is, let me just take this back to '''id''' and have this as descending. We can use this '''LIMIT 1''' or we can say '''LIMIT''' 2, 3 or 4.
 
|-
 
|-
 
|04:58
 
|04:58
|Now I'll limit 1 for the purpose of this.
+
|Now I'll '''LIMIT 1''' for the purpose of this.
 
|-
 
|-
 
|05:00  
 
|05:00  
|Now lets have 1 to let the user of the page know the last person that was inserted into this table.
+
|Now let's have 1 to let the user of the page know the last person that was inserted into this table.
 
|-
 
|-
 
|05:11
 
|05:11
|So I say "echo" here.
+
|So I say '''echo''' here.
 
|-
 
|-
 
|05:16
 
|05:16
|echo last person to be inserted into table was and I'll leave it like that and add a linebreak.  
+
|'''echo''' "Last person to be inserted into table was..." and I'll leave it like that and add a '''line-break'''.  
 
|-
 
|-
 
|05:27
 
|05:27
|I'll just echo out the first and last name. Ok?
+
|I'll just '''echo''' out the first and last name. Ok.
 
|-
 
|-
 
|05:33
 
|05:33
Line 133: Line 133:
 
|-
 
|-
 
|05:38
 
|05:38
|Last person to be inserted. Yes, in fact it does work!
+
|"Last person to be inserted.." Yes, in fact it does work!
 
|-
 
|-
 
|05:43
 
|05:43
|It is already typed in the "limit" command.
+
|It is already typed in the "LIMIT" '''command'''.
 
|-
 
|-
 
|05:46
 
|05:46
|What I've done is by limiting this by 1 in descending order of id - the id is incremental - I get 4 at the top and if we are limiting it by 1, 4 will be the only record that is selected.  
+
|What I've done is by limiting this by 1 in descending order of '''id''' - the '''id''' is incremental - I get 4 at the top and if we are limiting this by 1, 4 will be the only '''record''' that is selected.  
 
|-
 
|-
 
|06:01
 
|06:01
Line 145: Line 145:
 
|-
 
|-
 
|06:09
 
|06:09
|This "while" will only return 1 data value.
+
|This '''while''' will only return 1 data value.
 
|-
 
|-
 
|06:13
 
|06:13
Line 151: Line 151:
 
|-
 
|-
 
|06:18
 
|06:18
|This is one command here, so lets" * from people", "order by id decs" is another and "limit 1" is yet another.  
+
|This is one command here, '''SELECT asterisk (*) FROM "people" ''', '''ORDER BY id DESC''' is another and '''LIMIT 1''' is yet another.  
 
|-
 
|-
 
|06:27
 
|06:27
|We don't use commas or anything. This is just how we write our code inside our query.
+
|We don't use commas or anything. This is just how we write our '''code''' inside our '''query'''.
 
|-
 
|-
 
|06:34
 
|06:34
|Okay, just to test this , I'll insert just using the "insert" function in php myadmin and I'll insert another record.
+
|Okay, just to test this- I'll insert just using the '''Insert''' function in '''php MyAdmin''' and I'll insert another '''record'''.
 
|-
 
|-
 
|06:45
 
|06:45
|For example, lets type in "David Green" and our date of birth could be random.
+
|For example, let's type in "David Green" and our '''date of birth''' could be random.
 
|-
 
|-
 
|06:55
 
|06:55
|It doesn't really matter what we type in here. We say male.  
+
|It doesn't really matter what we type in here. We say '''Male'''.  
 
|-
 
|-
 
|07:00
 
|07:00
Line 169: Line 169:
 
|-
 
|-
 
|07:02
 
|07:02
|Click on browse and we have a new value here.  
+
|Click on '''Browse''' and we have a new value here.  
 
|-
 
|-
 
|07:06
 
|07:06
|When we come back here and refresh, that will change to "David Green".
+
|When we come back here and '''refresh''', that will change to "David Green".
 
|-
 
|-
 
|07:10
 
|07:10
|So this is really useful if you have a website in which your putting videos or personal pictures.  
+
|So, this is really useful if you have a website in which you are putting videos or personal pictures.  
 
|-
 
|-
 
|07:17
 
|07:17
Line 187: Line 187:
 
|-
 
|-
 
|07:33
 
|07:33
|Basically how to echo out data and how to manipulate it by just using mysql query.
+
|Basically how to '''echo''' out data and how to manipulate it by just using '''mysql query'''.
 
|-
 
|-
 
|07:44
 
|07:44
Line 193: Line 193:
 
|-
 
|-
 
|07:50
 
|07:50
|We will create some html forms and to enable them to do this.
+
|We will create some '''html forms'''  to enable them to do this.
 
|-
 
|-
 
|07:55
 
|07:55
|This will let them select a name from the database or table of their choice.
+
|This will let them select a name from the '''database''' or '''table''' of their choice.
 
|-
 
|-
 
|08:00
 
|08:00
|So, join me in the next part.
+
|So, join me in the next part.Bye for now.  This is Juanita Jayakar, dubbing for the Spoken Tutorial Project
|-
+
|08:01
+
|Bye for now.  This is Juanita Jayakar dubbing for the Spoken Tutorial Project
+

Latest revision as of 16:42, 24 March 2017

Time Narration
00:01 Welcome to mySQL part 5. To echo our data to the user and to display the results from this, we'll need to use while statement.
00:12 As I mentioned, we have created a $row variable and this is "= mysql_fetch_assoc".
00:21 This is creating an associative array from our "extract" query which is here.
00:27 So, we are selecting everything in the "people" table and ordering them by "id" in ascending order.
00:33 Inside our while, since we wrote $row as an array and it's an associative array, row[0] will be wrong because these are numeric.
00:46 These are numeric id tags and instead of this we'll use our field names, since this is associative.
00:59 So, instead of 0, 1, 2, 3, 4 we'll be using the actual name.
01:05 Let's create variables from this. Let me say $id and then $firstname equals, we are using the same structure throughout.
01:15 So, it's quite easy to copy and paste this.
01:19 Let's indent this.
01:24 So we have 5 altogether.
01:28 So that's five and then let's just change. This is a lazy act!
01:34 But it's just a lot quicker to do this way.
01:38 So $lastname and we have the date of birth ($dob). We also have the $gender.
01:47 We have all our data and now how do we use this?
01:51 We need to use the echo command.
01:55 There might be a loop inside the middle, at the moment. So, anything we echo out will be repeated.
02:02 Every record we say and that's right too. We'll repeat this code.
02:07 For example, I say "Text" here. There are currently 4 records.
02:13 After refreshing this page, you should see Text echoed out 4 times.
02:18 By typing out 4 times, this piece of code here, represents every loop.
02:24 Therefore we can incorporate for example, $id or $firstname or any other, that we have extracted from the database using our associative array.
02:36 Now I'll write: $firstname $lastname was born on $dob for date of birth and is and I'll put $gender up there.
02:49 Not forgetting our line-break. I'll refresh our page.
02:54 Then we have our set of data structured using the variable names.
02:59 We have given in the correct order and also it has been repeated through for every record we have.
03:08 Okay, we have just given out the content of our table using this star, declared by this asterisk, where it collects every single data on every record.
03:22 Now let me do this. I'll say if $gender== "F" then $gender= "Female" .
03:39 The actual spelling of that and then let us say else $gender= "Male". This is just rewriting the variable depending on the value.
03:50 If we refresh now, we can see this has changed to male Male and female Female. We also have some interesting ways of displaying this data.
04:00 At the moment, I'm selecting from the people table and ordering by id and ascending order.
04:07 I can also order by descending id. You can see that this switches this data around.
04:15 We can also order it by firstname. This will put this in descending alphabetical order and ascending will put this in ascending alphabetical order.
04:33 So, we got A, D, E, and K.
04:36 You can do the same with the surname.
04:39 You could do the same with anything. Even date of birth, as long as you include this over here.
04:46 Another thing to do is, let me just take this back to id and have this as descending. We can use this LIMIT 1 or we can say LIMIT 2, 3 or 4.
04:58 Now I'll LIMIT 1 for the purpose of this.
05:00 Now let's have 1 to let the user of the page know the last person that was inserted into this table.
05:11 So I say echo here.
05:16 echo "Last person to be inserted into table was..." and I'll leave it like that and add a line-break.
05:27 I'll just echo out the first and last name. Ok.
05:33 So, here we can see that there is a lot of confusion.
05:38 "Last person to be inserted.." Yes, in fact it does work!
05:43 It is already typed in the "LIMIT" command.
05:46 What I've done is by limiting this by 1 in descending order of id - the id is incremental - I get 4 at the top and if we are limiting this by 1, 4 will be the only record that is selected.
06:01 Therefore, the last person in the table, as per the last record displayed, will have its value echoed out.
06:09 This while will only return 1 data value.
06:13 Since we are returning 1 data value here, we are confused by these.
06:18 This is one command here, SELECT asterisk (*) FROM "people" , ORDER BY id DESC is another and LIMIT 1 is yet another.
06:27 We don't use commas or anything. This is just how we write our code inside our query.
06:34 Okay, just to test this- I'll insert just using the Insert function in php MyAdmin and I'll insert another record.
06:45 For example, let's type in "David Green" and our date of birth could be random.
06:55 It doesn't really matter what we type in here. We say Male.
07:00 I came down here and submit this data.
07:02 Click on Browse and we have a new value here.
07:06 When we come back here and refresh, that will change to "David Green".
07:10 So, this is really useful if you have a website in which you are putting videos or personal pictures.
07:17 You can just place in the last thing the user had inserted.
07:21 Or may be the last person that has been registered on your website or anything.
07:30 Possibility of using this is endless.
07:33 Basically how to echo out data and how to manipulate it by just using mysql query.
07:44 In the next part, we will allow our user to specify which data they want to show.
07:50 We will create some html forms to enable them to do this.
07:55 This will let them select a name from the database or table of their choice.
08:00 So, join me in the next part.Bye for now. This is Juanita Jayakar, dubbing for the Spoken Tutorial Project

Contributors and Content Editors

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