1 00:00:01 --> 00:00:11 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. 2 00:00:12 --> 00:00:20 As I mentioned, we have created a row variable and this is "=mysql_fetch_assoc". 3 00:00:21 --> 00:00:26 This is creating an associative array from our "extract" query which is here. 4 00:00:27 --> 00:00:32 So we are selecting everything in the "people" table and ordering them by "id" in ascending order. 5 00:00:33 --> 00:00:45 Inside our WHILE since we wrote row as an array and its an associative array, row[0] will be wrong because these are numeric. 6 00:00:46 --> 00:00:58 These are numeric id tags and instead of this we'll use our fieldnames, since this is associative. 7 00:00:59 --> 00:01:04 So, instead of 0 1 2 3 4, we'll be using the actual name. 8 00:01:05 --> 00:01:14 Lets create variables from this. Let me say id and then firstname equals, we are using the same structure throughout. 9 00:01:15 --> 00:01:18 So its quite easy to copy and paste this. 10 00:01:19 --> 00:01:23 Lets indent this. 11 00:01:24 --> 00:01:27 So we have 5 altogether. 12 00:01:28 --> 00:01:33 So that's five and then lets just change. This is a lazy act! 13 00:01:34 --> 00:01:37 But its just a lot quicker to do this way. 14 00:01:38 --> 00:01:46 So lastname and we have the date of birth. We also have the gender. 15 00:01:47 --> 00:01:50 We have all our data and now how do we use this? 16 00:01:51 --> 00:01:54 We need to use the "echo" command. 17 00:01:55 --> 00:02:01 There might be a loop inside the middle, at the moment. So anything we echo out will be repeated. 18 00:02:02 --> 00:02:06 Every record we have and that's right, too. We'll repeat this code. 19 00:02:07 --> 00:02:12 For example, I say text here. There are currently 4 records. 20 00:02:13 --> 00:02:17 After refreshing this page, you should see text echoed out 4 times. 21 00:02:18 --> 00:02:23 By typing out 4 times, this piece of code here represents every loop. 22 00:02:24 --> 00:02:35 Therefore we can incorporate for example, id or firstname or any other, that we have extracted from the database using our associative array. 23 00:02:36 --> 00:02:48 Now I'll write firstname lastname was born on dob for date of birth and is and I'll put gender up there. 24 00:02:49 --> 00:02:53 Not forgetting our linebreak. I'll refresh our page. 25 00:02:54 --> 00:02:58 Then we have our set of data structured using the variable names. 26 00:02:59 --> 00:03:07 We have given in the correct order and also it has been repeated through for every record we have. 27 00:03:08 --> 00:03:21 Okay, we have just given out the content of our table using this star, declared by this asterisk, where it collects every single data or every record. 28 00:03:22 --> 00:03:38 Now let me do this. I'll say IF gender==F then gender=female. 29 00:03:39 --> 00:03:49 The actual spelling of that and then let us say else gender=male. This is just rewriting the variable depending on the value. 30 00:03:50 --> 00:03:59 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. 31 00:04:00 --> 00:04:06 At the moment I'm selecting from the people table and ordering by id and ascending order. 32 00:04:07 --> 00:04:14 I can also order by descending id. You can see that this switches this data around. 33 00:04:15 --> 00:04:32 We can also order it by firstname. This will put this in descending alphabetical order and ascending will put this in ascending alphabetical order. 34 00:04:33 --> 00:04:35 So we got A D E and K. 35 00:04:36 --> 00:04:38 You can do the same with the surname. 36 00:04:39 --> 00:04:45 You could do the same with anything. Even Date of birth, as long as you include this over here. 37 00:04:46 --> 00:04:57 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. 38 00:04:58 --> 00:04:59 Now I'll limit 1 for the purpose of this. 39 00:05:00 --> 00:05:10 Now lets have 1 to let the user of the page know the last person that was inserted into this table. 40 00:05:11 --> 00:05:15 So I say "echo" here. 41 00:05:16 --> 00:05:26 echo last person to be inserted into table was and I'll leave it like that and add a linebreak. 42 00:05:27 --> 00:05:32 I'll just echo out the first and last name. Ok? 43 00:05:33 --> 00:05:37 So, here we can see that there is a lot of confusion. 44 00:05:38 --> 00:05:42 Last person to be inserted. Yes, in fact it does work! 45 00:05:43 --> 00:05:45 It is already typed in the "limit" command. 46 00:05:46 --> 00:06:00 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 it by 1, 4 will be the only record that is selected. 47 00:06:01 --> 00:06:08 Therefore, the last person in the table, as per the last record displayed, will have its value echoed out. 48 00:06:09 --> 00:06:12 This "while" will only return 1 data value. 49 00:06:13 --> 00:06:17 Since we are returning 1 data value here, we are confused by these. 50 00:06:18 --> 00:06:26 This is one command here, so lets" * from people", "order by id decs" is another and "limit 1" is yet another. 51 00:06:27 --> 00:06:33 We don't use commas or anything. This is just how we write our code inside our query. 52 00:06:34 --> 00:06:44 Okay, just to test this , I'll insert just using the "insert" function in php myadmin and I'll insert another record. 53 00:06:45 --> 00:06:54 For example, lets type in "David Green" and our date of birth could be random. 54 00:06:55 --> 00:06:59 It doesn't really matter what we type in here. We say male. 55 00:07:00 --> 00:07:01 I came down here and submit this data. 56 00:07:02 --> 00:07:05 Click on browse and we have a new value here. 57 00:07:06 --> 00:07:09 When we come back here and refresh, that will change to "David Green". 58 00:07:10 --> 00:07:16 So this is really useful if you have a website in which your putting videos or personal pictures. 59 00:07:17 --> 00:07:20 You can just place in the last thing the user had inserted. 60 00:07:21 --> 00:07:29 Or may be the last person that has been registered on your website or anything. 61 00:07:30 --> 00:07:32 Possibility of using this is endless. 62 00:07:33 --> 00:07:43 Basically how to echo out data and how to manipulate it by just using mysql query. 63 00:07:44 --> 00:07:49 In the next part, we will allow our user to specify which data they want to show. 64 00:07:50 --> 00:07:54 We will create some html forms and to enable them to do this. 65 00:07:55 --> 00:07:59 This will let them select a name from the database or table of their choice. 66 00:08:00 --> 00:08:00 So, join me in the next part. 67 00:08:01 --> 00:08:06 Bye for now. This is Juanita Jayakar dubbing for the Spoken Tutorial Project