PHP-and-MySQL/C3/MySQL-Part-5/English-timed

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.
08:01	Bye for now. This is Juanita Jayakar, dubbing for the Spoken Tutorial Project

Contributors and Content Editors

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