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

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

Contributors and Content Editors

St-admin