Difference between revisions of "PHP-and-MySQL/C3/MySQL-Part-5/English-timed"
From Script | Spoken-Tutorial
PoojaMoolya (Talk | contribs) |
|||
(4 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
{| border=1 | {| border=1 | ||
− | + | |'''Time''' | |
− | + | |'''Narration''' | |
|- | |- | ||
|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 | + | |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". | + | |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 | + | |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 | + | |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 | + | |So, instead of 0, 1, 2, 3, 4 we'll be using the actual name. |
|- | |- | ||
|01:05 | |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 | |01:15 | ||
− | |So | + | |So, it's quite easy to copy and paste this. |
|- | |- | ||
|01:19 | |01:19 | ||
− | | | + | |Let's indent this. |
|- | |- | ||
|01:24 | |01:24 | ||
Line 37: | Line 37: | ||
|- | |- | ||
|01:28 | |01:28 | ||
− | |So that's five and then | + | |So that's five and then let's just change. This is a lazy act! |
|- | |- | ||
|01:34 | |01:34 | ||
− | |But | + | |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 | + | |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 | + | |Every '''record''' we say and that's right too. We'll repeat this code. |
|- | |- | ||
|02:07 | |02:07 | ||
− | |For example, I say | + | |For example, I say "Text" here. There are currently 4 records. |
|- | |- | ||
|02:13 | |02:13 | ||
− | |After refreshing this page, you should see | + | |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. |
|- | |- | ||
− | | | + | |02:49 |
− | |Not forgetting our | + | |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 | + | |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 | + | |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= | + | |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 ''' | + | |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 | + | |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 | + | |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 | + | |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 | + | |Now I'll '''LIMIT 1''' for the purpose of this. |
|- | |- | ||
|05:00 | |05:00 | ||
− | |Now | + | |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 | + | |So I say '''echo''' here. |
|- | |- | ||
|05:16 | |05:16 | ||
− | |echo | + | |'''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 " | + | |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 | + | |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 | + | |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, | + | |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 | + | |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, | + | |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 | + | |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 | + | |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 | + | |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 | + | |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 |
− | + | ||
− | + | ||
− | + |
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 |