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