Difference between revisions of "PHP-and-MySQL/C3/MySQL-Part-4/English-timed"
From Script | Spoken-Tutorial
| Line 3: | Line 3: | ||
!Narration | !Narration | ||
|- | |- | ||
| − | | | + | |00:01 |
|Welcome to the Spoken Tutorial on the fourth part of My SQL and php tutorials. | |Welcome to the Spoken Tutorial on the fourth part of My SQL and php tutorials. | ||
|- | |- | ||
| − | | | + | |00:08 |
|In the last tutorial, I used "mysql_query" function to insert some values into our table. | |In the last tutorial, I used "mysql_query" function to insert some values into our table. | ||
|- | |- | ||
| − | | | + | |00:21 |
|I made a mistake here by putting the date as current date, which isn't my date of birth. | |I made a mistake here by putting the date as current date, which isn't my date of birth. | ||
|- | |- | ||
| − | | | + | |00:26 |
|I was able to update here. I was also able to specify where I wanted to update. | |I was able to update here. I was also able to specify where I wanted to update. | ||
|- | |- | ||
| − | | | + | |00:32 |
|Using a unique ID key, I was able to specify exactly where I wanted to update. | |Using a unique ID key, I was able to specify exactly where I wanted to update. | ||
|- | |- | ||
| − | | | + | |00:40 |
|So we've already seen the "update" in mysql code. | |So we've already seen the "update" in mysql code. | ||
|- | |- | ||
| − | | | + | |00:46 |
|This is very useful. | |This is very useful. | ||
|- | |- | ||
| − | | | + | |00:48 |
|This query and code is the one that is mostly used while working with tables or if you're working with mysql in general. | |This query and code is the one that is mostly used while working with tables or if you're working with mysql in general. | ||
|- | |- | ||
| − | | | + | |00:59 |
| − | |The next thing I | + | |The next thing I show you is how to get data from your table and how to display it really well. |
|- | |- | ||
| − | | | + | |01:07 |
|So I will call this "update data" so that we know what that is. | |So I will call this "update data" so that we know what that is. | ||
|- | |- | ||
| − | | | + | |01:12 |
|Here, we will say "extract data". | |Here, we will say "extract data". | ||
|- | |- | ||
| − | | | + | |01:15 |
|That's a good word to use. | |That's a good word to use. | ||
|- | |- | ||
| − | | | + | |01:18 |
|Now, again we will say "extract" and we will create a variable. | |Now, again we will say "extract" and we will create a variable. | ||
|- | |- | ||
| − | | | + | |01:23 |
|This again is "mysql query" and here is some code. | |This again is "mysql query" and here is some code. | ||
|- | |- | ||
| − | | | + | |01:28 |
|This is slightly more complicated than using these single line queries. | |This is slightly more complicated than using these single line queries. | ||
|- | |- | ||
| − | | | + | |01:37 |
|We use single line queries here, but we could have some code after this in order to display properly. | |We use single line queries here, but we could have some code after this in order to display properly. | ||
|- | |- | ||
| − | | | + | |01:44 |
|First I will create another record in the table. | |First I will create another record in the table. | ||
|- | |- | ||
| − | | | + | |01:47 |
|So we don't need this "current date" function anymore. | |So we don't need this "current date" function anymore. | ||
|- | |- | ||
| − | | | + | |01:51 |
|I need this "write" to be shown. Lets create some new value. | |I need this "write" to be shown. Lets create some new value. | ||
|- | |- | ||
| − | | | + | |01:57 |
|I'll say "Kyle Headen" and I'll set a date of birth here. This one is month. So that is the 7th and lets say here, 24th. | |I'll say "Kyle Headen" and I'll set a date of birth here. This one is month. So that is the 7th and lets say here, 24th. | ||
|- | |- | ||
| − | | | + | |02:12 |
|So now we got the date of birth. | |So now we got the date of birth. | ||
|- | |- | ||
| − | | | + | |02:14 |
|Now we've got male and then we've got "Kyle Headen" and we are again inserting this into our database. | |Now we've got male and then we've got "Kyle Headen" and we are again inserting this into our database. | ||
|- | |- | ||
| − | | | + | |02:23 |
|Lets refresh. | |Lets refresh. | ||
|- | |- | ||
| − | | | + | |02:25 |
|Here I'll create another new value. | |Here I'll create another new value. | ||
|- | |- | ||
| − | | | + | |02:28 |
|I'll say "Emily Headen" and I'll just leave the date of birth as it is for now. | |I'll say "Emily Headen" and I'll just leave the date of birth as it is for now. | ||
|- | |- | ||
| − | | | + | |02:34 |
|This will be "Female" because I'll extract these records at one point. | |This will be "Female" because I'll extract these records at one point. | ||
|- | |- | ||
| − | | | + | |02:39 |
|Refresh this again. | |Refresh this again. | ||
|- | |- | ||
| − | | | + | |02:41 |
|So we've created 3 records here. | |So we've created 3 records here. | ||
|- | |- | ||
| − | | | + | |02:44 |
|I'll comment this "write". Backup my database. | |I'll comment this "write". Backup my database. | ||
|- | |- | ||
| − | | | + | |02:48 |
|I'll click on browse in this specific table and you can see that I've got 3 records. | |I'll click on browse in this specific table and you can see that I've got 3 records. | ||
|- | |- | ||
| − | | | + | |02:54 |
|Each one of these is called a "record of data". | |Each one of these is called a "record of data". | ||
|- | |- | ||
| − | | | + | |02:58 |
|We can see this id has also automatically incremented. | |We can see this id has also automatically incremented. | ||
|- | |- | ||
| − | | | + | |03:04 |
|We've got the data that we specified and everything that we need. | |We've got the data that we specified and everything that we need. | ||
|- | |- | ||
| − | | | + | |03:08 |
|Ok, so we are extracting data here and I will uncomment this. | |Ok, so we are extracting data here and I will uncomment this. | ||
|- | |- | ||
| − | | | + | |03:13 |
|Our mysql query is going to start with "select". | |Our mysql query is going to start with "select". | ||
|- | |- | ||
| − | | | + | |03:17 |
|This will be either specific records or we can use asterisk (*) to get all the data we need. | |This will be either specific records or we can use asterisk (*) to get all the data we need. | ||
|- | |- | ||
| − | | | + | |03:24 |
|Now I'll use an asterisk (*). | |Now I'll use an asterisk (*). | ||
|- | |- | ||
| − | | | + | |03:27 |
|What you could do is type "select firstname". | |What you could do is type "select firstname". | ||
|- | |- | ||
| − | | | + | |03:30 |
|But usually, when you have table, you will need most of the data and it'll take longer to do. | |But usually, when you have table, you will need most of the data and it'll take longer to do. | ||
|- | |- | ||
| − | | | + | |03:36 |
|Depending on the source of the table, this won't take very long. | |Depending on the source of the table, this won't take very long. | ||
|- | |- | ||
| − | | | + | |03:40 |
|So you already have a couple of records or fields. | |So you already have a couple of records or fields. | ||
|- | |- | ||
| − | | | + | |03:45 |
|But for now I'll say select asterisk (*), which is a star. | |But for now I'll say select asterisk (*), which is a star. | ||
|- | |- | ||
| − | | | + | |03:50 |
|We can say select star and then we say FROM. | |We can say select star and then we say FROM. | ||
|- | |- | ||
| − | | | + | |03:54 |
|Again we say, the specified table which is "people". | |Again we say, the specified table which is "people". | ||
|- | |- | ||
| − | | | + | |03:57 |
|Here, we can say WHERE and how can you ummm...... filter for the data you want. | |Here, we can say WHERE and how can you ummm...... filter for the data you want. | ||
|- | |- | ||
| − | | | + | |04:05 |
|So I can say "SELECT star (*) FROM people WHERE firstname= "Alex'". | |So I can say "SELECT star (*) FROM people WHERE firstname= "Alex'". | ||
|- | |- | ||
| − | | | + | |04:11 |
|This query will return only one value because we can see that if we open up here, there is only one record with "Alex". | |This query will return only one value because we can see that if we open up here, there is only one record with "Alex". | ||
|- | |- | ||
| − | | | + | |04:22 |
|We can do this by using another really useful function called "mysql numrows" and what I can do is echo this out. | |We can do this by using another really useful function called "mysql numrows" and what I can do is echo this out. | ||
|- | |- | ||
| − | | | + | |04:32 |
|I'll say "echo mysql_num_rows". This is the reason we have given these variables here to be stored in. | |I'll say "echo mysql_num_rows". This is the reason we have given these variables here to be stored in. | ||
|- | |- | ||
| − | | | + | |04:43 |
|Here we can just type "extract". | |Here we can just type "extract". | ||
|- | |- | ||
| − | | | + | |04:46 |
|Our "extract" variable holds our query and our function here tells us how many rows are there in the query that is given out. | |Our "extract" variable holds our query and our function here tells us how many rows are there in the query that is given out. | ||
|- | |- | ||
| − | | | + | |04:55 |
|Presuming that we gave firstname as "Alex", it'll show when we refresh. | |Presuming that we gave firstname as "Alex", it'll show when we refresh. | ||
|- | |- | ||
| − | | | + | |05:01 |
|However you get 1. | |However you get 1. | ||
|- | |- | ||
| − | | | + | |05:03 |
|Lets change this. Lets put something that's common to two people in this database. | |Lets change this. Lets put something that's common to two people in this database. | ||
|- | |- | ||
| − | | | + | |05:09 |
|That would be the "gender". | |That would be the "gender". | ||
|- | |- | ||
| − | | | + | |05:11 |
|So that'll be "Male" or "Female" . Here we can say "WHERE gender = M" and when we refresh, we get two records. | |So that'll be "Male" or "Female" . Here we can say "WHERE gender = M" and when we refresh, we get two records. | ||
|- | |- | ||
| − | | | + | |05:24 |
|So we can tell how many records we are getting out. | |So we can tell how many records we are getting out. | ||
|- | |- | ||
| − | | | + | |05:28 |
|This is really useful for saying how many people in my database are male, for example. | |This is really useful for saying how many people in my database are male, for example. | ||
|- | |- | ||
| − | | | + | |05:34 |
|And we can see how many males or females are registered to our website. | |And we can see how many males or females are registered to our website. | ||
|- | |- | ||
| − | | | + | |05:40 |
|So you can store registered information inside here. | |So you can store registered information inside here. | ||
|- | |- | ||
| − | | | + | |05:44 |
|What we can also do is order the records. | |What we can also do is order the records. | ||
|- | |- | ||
| − | | | + | |05:47 |
| − | |So I'll say "ORDER BY id" and we can choose descending that is "DESC" | + | |So I'll say "ORDER BY id" and we can choose descending that is "DESC" and we can choose ascending, which is "ASC". |
|- | |- | ||
| − | | | + | |05:58 |
|But for now I'll take this out because we haven't actually echoed out our data yet. | |But for now I'll take this out because we haven't actually echoed out our data yet. | ||
|- | |- | ||
| − | | | + | |06:03 |
|We haven't displayed out our data to the users that has been selected. | |We haven't displayed out our data to the users that has been selected. | ||
|- | |- | ||
| − | | | + | |06:08 |
|So there is no point in using that at the moment. | |So there is no point in using that at the moment. | ||
|- | |- | ||
| − | | | + | |06:11 |
|Now, here I'll say select star (*) from "people" because I want to select all the data from this table here. | |Now, here I'll say select star (*) from "people" because I want to select all the data from this table here. | ||
|- | |- | ||
| − | | | + | |06:21 |
|So I can manipulate and show it to the user the way I want. | |So I can manipulate and show it to the user the way I want. | ||
|- | |- | ||
| − | | | + | |06:25 |
|I'll create something here called "numrows"; "numrows =" that. | |I'll create something here called "numrows"; "numrows =" that. | ||
|- | |- | ||
| − | | | + | |06:30 |
|I'll use a "while" loop. This will use a specific function which is "mysql_fetch_assoc". | |I'll use a "while" loop. This will use a specific function which is "mysql_fetch_assoc". | ||
|- | |- | ||
| − | | | + | |06:43 |
|It puts this into an associative array. | |It puts this into an associative array. | ||
|- | |- | ||
| − | | | + | |06:46 |
|If you don't know what an associative array is, check out "Arrays" tutorial. | |If you don't know what an associative array is, check out "Arrays" tutorial. | ||
|- | |- | ||
| − | | | + | |06:51 |
|Coming back, "WHILE the row= mysql_fetch_aasoc" or associative is what I will say and this is inside the "extract" query. | |Coming back, "WHILE the row= mysql_fetch_aasoc" or associative is what I will say and this is inside the "extract" query. | ||
|- | |- | ||
| − | | | + | |07:06 |
|We are selecting "row" as array name and we are selecting this as an array for all the selected data. | |We are selecting "row" as array name and we are selecting this as an array for all the selected data. | ||
|- | |- | ||
| − | | | + | |07:15 |
|I'll stop here. In the next tutorial I'll show you how to echo out this data. | |I'll stop here. In the next tutorial I'll show you how to echo out this data. | ||
|- | |- | ||
| − | | | + | |07:21 |
|I'll probably explain this a bit more in detail. | |I'll probably explain this a bit more in detail. | ||
|- | |- | ||
| − | | | + | |07:25 |
|This is Juanita Jayakar dubbing for the Spoken Tutorial Project. | |This is Juanita Jayakar dubbing for the Spoken Tutorial Project. | ||
Revision as of 10:29, 12 July 2013
| Time | Narration |
|---|---|
| 00:01 | Welcome to the Spoken Tutorial on the fourth part of My SQL and php tutorials. |
| 00:08 | In the last tutorial, I used "mysql_query" function to insert some values into our table. |
| 00:21 | I made a mistake here by putting the date as current date, which isn't my date of birth. |
| 00:26 | I was able to update here. I was also able to specify where I wanted to update. |
| 00:32 | Using a unique ID key, I was able to specify exactly where I wanted to update. |
| 00:40 | So we've already seen the "update" in mysql code. |
| 00:46 | This is very useful. |
| 00:48 | This query and code is the one that is mostly used while working with tables or if you're working with mysql in general. |
| 00:59 | The next thing I show you is how to get data from your table and how to display it really well. |
| 01:07 | So I will call this "update data" so that we know what that is. |
| 01:12 | Here, we will say "extract data". |
| 01:15 | That's a good word to use. |
| 01:18 | Now, again we will say "extract" and we will create a variable. |
| 01:23 | This again is "mysql query" and here is some code. |
| 01:28 | This is slightly more complicated than using these single line queries. |
| 01:37 | We use single line queries here, but we could have some code after this in order to display properly. |
| 01:44 | First I will create another record in the table. |
| 01:47 | So we don't need this "current date" function anymore. |
| 01:51 | I need this "write" to be shown. Lets create some new value. |
| 01:57 | I'll say "Kyle Headen" and I'll set a date of birth here. This one is month. So that is the 7th and lets say here, 24th. |
| 02:12 | So now we got the date of birth. |
| 02:14 | Now we've got male and then we've got "Kyle Headen" and we are again inserting this into our database. |
| 02:23 | Lets refresh. |
| 02:25 | Here I'll create another new value. |
| 02:28 | I'll say "Emily Headen" and I'll just leave the date of birth as it is for now. |
| 02:34 | This will be "Female" because I'll extract these records at one point. |
| 02:39 | Refresh this again. |
| 02:41 | So we've created 3 records here. |
| 02:44 | I'll comment this "write". Backup my database. |
| 02:48 | I'll click on browse in this specific table and you can see that I've got 3 records. |
| 02:54 | Each one of these is called a "record of data". |
| 02:58 | We can see this id has also automatically incremented. |
| 03:04 | We've got the data that we specified and everything that we need. |
| 03:08 | Ok, so we are extracting data here and I will uncomment this. |
| 03:13 | Our mysql query is going to start with "select". |
| 03:17 | This will be either specific records or we can use asterisk (*) to get all the data we need. |
| 03:24 | Now I'll use an asterisk (*). |
| 03:27 | What you could do is type "select firstname". |
| 03:30 | But usually, when you have table, you will need most of the data and it'll take longer to do. |
| 03:36 | Depending on the source of the table, this won't take very long. |
| 03:40 | So you already have a couple of records or fields. |
| 03:45 | But for now I'll say select asterisk (*), which is a star. |
| 03:50 | We can say select star and then we say FROM. |
| 03:54 | Again we say, the specified table which is "people". |
| 03:57 | Here, we can say WHERE and how can you ummm...... filter for the data you want. |
| 04:05 | So I can say "SELECT star (*) FROM people WHERE firstname= "Alex'". |
| 04:11 | This query will return only one value because we can see that if we open up here, there is only one record with "Alex". |
| 04:22 | We can do this by using another really useful function called "mysql numrows" and what I can do is echo this out. |
| 04:32 | I'll say "echo mysql_num_rows". This is the reason we have given these variables here to be stored in. |
| 04:43 | Here we can just type "extract". |
| 04:46 | Our "extract" variable holds our query and our function here tells us how many rows are there in the query that is given out. |
| 04:55 | Presuming that we gave firstname as "Alex", it'll show when we refresh. |
| 05:01 | However you get 1. |
| 05:03 | Lets change this. Lets put something that's common to two people in this database. |
| 05:09 | That would be the "gender". |
| 05:11 | So that'll be "Male" or "Female" . Here we can say "WHERE gender = M" and when we refresh, we get two records. |
| 05:24 | So we can tell how many records we are getting out. |
| 05:28 | This is really useful for saying how many people in my database are male, for example. |
| 05:34 | And we can see how many males or females are registered to our website. |
| 05:40 | So you can store registered information inside here. |
| 05:44 | What we can also do is order the records. |
| 05:47 | So I'll say "ORDER BY id" and we can choose descending that is "DESC" and we can choose ascending, which is "ASC". |
| 05:58 | But for now I'll take this out because we haven't actually echoed out our data yet. |
| 06:03 | We haven't displayed out our data to the users that has been selected. |
| 06:08 | So there is no point in using that at the moment. |
| 06:11 | Now, here I'll say select star (*) from "people" because I want to select all the data from this table here. |
| 06:21 | So I can manipulate and show it to the user the way I want. |
| 06:25 | I'll create something here called "numrows"; "numrows =" that. |
| 06:30 | I'll use a "while" loop. This will use a specific function which is "mysql_fetch_assoc". |
| 06:43 | It puts this into an associative array. |
| 06:46 | If you don't know what an associative array is, check out "Arrays" tutorial. |
| 06:51 | Coming back, "WHILE the row= mysql_fetch_aasoc" or associative is what I will say and this is inside the "extract" query. |
| 07:06 | We are selecting "row" as array name and we are selecting this as an array for all the selected data. |
| 07:15 | I'll stop here. In the next tutorial I'll show you how to echo out this data. |
| 07:21 | I'll probably explain this a bit more in detail. |
| 07:25 | This is Juanita Jayakar dubbing for the Spoken Tutorial Project. |
