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

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.

Contributors and Content Editors

Krupali, Pratik kamble, Sandhya.np14, Sneha