Difference between revisions of "PHP-and-MySQL/C3/MySQL-Part-4/English-timed"
From Script | Spoken-Tutorial
Sandhya.np14 (Talk | contribs) |
Sandhya.np14 (Talk | contribs) |
||
Line 91: | Line 91: | ||
|- | |- | ||
|02:48 | |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 | |02:54 | ||
Line 97: | Line 97: | ||
|- | |- | ||
|02:58 | |02:58 | ||
− | |We can see this id has also automatically incremented. | + | |We can see this '''id''' has also automatically incremented. |
|- | |- | ||
|03:04 | |03:04 | ||
Line 106: | Line 106: | ||
|- | |- | ||
|03:13 | |03:13 | ||
− | |Our | + | |Our '''mysql_query''' is going to start with "SELECT". |
|- | |- | ||
|03:17 | |03:17 | ||
Line 115: | Line 115: | ||
|- | |- | ||
|03:27 | |03:27 | ||
− | |What you could do is type " | + | |What you could do is type "SELECT firstname". |
|- | |- | ||
|03:30 | |03:30 | ||
− | |But usually | + | |But usually when you have table, you will need most of the data and it'll take longer to do. |
|- | |- | ||
|03:36 | |03:36 | ||
Line 124: | Line 124: | ||
|- | |- | ||
|03:40 | |03:40 | ||
− | |So you already have a couple of records or fields. | + | |So, you already have a couple of records or '''fields'''. |
|- | |- | ||
|03:45 | |03:45 | ||
− | |But for now I'll say select asterisk (*) | + | |But, for now, I'll say select asterisk (*) which is a star. |
|- | |- | ||
|03:50 | |03:50 | ||
− | |We can say | + | |We can say: '''SELECT''' star and then we say '''FROM'''. |
|- | |- | ||
|03:54 | |03:54 | ||
− | |Again we say | + | |Again we say the specified '''table''' which is "people". |
|- | |- | ||
|03:57 | |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 | |04:05 | ||
− | |So I can say "SELECT star (*) FROM people WHERE firstname= | + | |So, I can say: "SELECT star (*) FROM people WHERE firstname= 'Alex' ". |
|- | |- | ||
|04:11 | |04:11 | ||
Line 145: | Line 145: | ||
|- | |- | ||
|04:22 | |04:22 | ||
− | |We can do this by using another really useful function called | + | |We can do this by using another really useful function called '''mysql_num_rows()''' and what I can do is '''echo''' this out. |
|- | |- | ||
|04:32 | |04:32 | ||
− | |I'll say | + | |I'll say: '''echo mysql_num_rows()'''. This is the reason we have given these variables here to be stored in. |
|- | |- | ||
|04:43 | |04:43 | ||
− | |Here we can just type | + | |Here we can just type '''$extract'''. |
|- | |- | ||
|04:46 | |04:46 | ||
− | |Our | + | |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 | |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 | |05:01 | ||
Line 163: | Line 163: | ||
|- | |- | ||
|05:03 | |05:03 | ||
− | | | + | |Let's change this. Let's put something that's common to two people in this database. |
|- | |- | ||
|05:09 | |05:09 | ||
Line 169: | Line 169: | ||
|- | |- | ||
|05:11 | |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 | |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 | |05:28 | ||
− | |This is really useful for saying how many people in my database are | + | |This is really useful for saying how many people in my database are males, for example. |
|- | |- | ||
|05:34 | |05:34 | ||
Line 181: | Line 181: | ||
|- | |- | ||
|05:40 | |05:40 | ||
− | |So you can store registered information inside here. | + | |So, you can store registered information inside here. |
|- | |- | ||
|05:44 | |05:44 | ||
Line 187: | Line 187: | ||
|- | |- | ||
|05:47 | |05:47 | ||
− | |So I'll say | + | |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 | |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 | |06:03 | ||
Line 199: | Line 199: | ||
|- | |- | ||
|06:11 | |06:11 | ||
− | |Now, here I'll say select star (*) | + | |Now, here I'll say: '''select''' star (*) '''FROM "people" ''' because I want to select all the data from this table here. |
|- | |- | ||
|06:21 | |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 | |06:25 | ||
− | |I'll create something here called "numrows"; "numrows =" that. | + | |I'll create something here called "$numrows"; "numrows =" that. |
|- | |- | ||
|06:30 | |06:30 | ||
− | |I'll use a | + | |I'll use a '''while''' loop. This will use a specific '''function''' which is "mysql_fetch_assoc()". |
|- | |- | ||
|06:43 | |06:43 | ||
Line 214: | Line 214: | ||
|- | |- | ||
|06:46 | |06:46 | ||
− | |If you don't know what an associative array is, check out | + | |If you don't know what an associative array is, check out '''Arrays''' tutorial. |
|- | |- | ||
|06:51 | |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 | |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 | |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 all of this data. |
|- | |- | ||
|07:21 | |07:21 |
Latest revision as of 12:36, 28 May 2015
Time | Narration |
00:01 | Welcome to the Spoken Tutorial on the fourth part of MySQL 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. Let's 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 let's 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 | Let's 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_num_rows() 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 | Let's change this. Let's 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 males, 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 all of 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. |