1 00:00:01 --> 00:00:02 Hi everyone! 2 00:00:03 --> 00:00:05 This is a pure My SQL php tutorial. 3 00:00:06 --> 00:00:11 I will teach you the basics of connecting, retrieving data, handling errors and modifying data. 4 00:00:12 --> 00:00:16 So, that will cover some SQL code and some SQL queries. 5 00:00:17 --> 00:00:18 Ok! Lets start. 6 00:00:19 --> 00:00:22 Here I am going to show the directory structure of "mysql" 7 00:00:23 --> 00:00:28 and we will create a couple of files here. 8 00:00:29 --> 00:00:32 I will create the first file and call it "connect.php". 9 00:00:33 --> 00:00:38 I will come here, click on the folder called "mysql" and Save this as "connect.php", 10 00:00:39 --> 00:00:44 Now here we will create a separate file and include with every page that we use. 11 00:00:45 --> 00:00:47 This is a lot easier to connect to your database, 12 00:00:48 --> 00:00:52 What we will do is type our "include" function and specify this file. 13 00:00:53 --> 00:00:56 I will create another file which is my main "mysql" file 14 00:00:57 --> 00:00:58 with a code that I will show you all. 15 00:00:59 --> 00:01:02 Ok, so I have got my mysql dot php open. 16 00:01:03 --> 00:01:09 This is php codes and we need the php tags and the same will connect to php. 17 00:01:10 --> 00:01:15 I will explain this "include" function in a minute. 18 00:01:16 --> 00:01:19 First, I will teach you how to connect to the database. 19 00:01:20 --> 00:01:27 If you don't know where you have it stored on your web server, then I suggest you check out an application called phpmyadmin. 20 00:01:28 --> 00:01:34 It is a database interface php written program or in other words a script. 21 00:01:35 --> 00:01:40 Here we will look at the inside of my database, my service. 22 00:01:41 --> 00:01:54 Rather my server, My SQL server. It gives our table information, our database information and information about my server etc etc. 23 00:01:55 --> 00:02:05 Although, we don't need to know about it, this is a good start to a program, if you are just starting to use a php mysql or just mysql in general. 24 00:02:06 --> 00:02:12 It is a good way to start interfacing with your database, instead of using the command line to do things. 25 00:02:13 --> 00:02:17 Using command line could be difficult for the first timers. 26 00:02:18 --> 00:02:22 Ok, so what we see here are, our databases. 27 00:02:23 --> 00:02:30 I have got one called "phpacademy" and I have got one called "phplogin" which I mentioned in another tutorial that I have created. 28 00:02:31 --> 00:02:33 The others are just a standard. 29 00:02:34 --> 00:02:35 They are just for holding data. 30 00:02:36 --> 00:02:37 Don't delete them. 31 00:02:38 --> 00:02:40 All you need to do is create new databases. 32 00:02:41 --> 00:02:44 To do this, you have got a simple box here. 33 00:02:45 --> 00:02:46 We are just going to create databases. 34 00:02:47 --> 00:02:50 I will work within my php academy database for now. 35 00:02:51 --> 00:02:52 It is a lot easier. 36 00:02:53 --> 00:02:54 So its creating a database now. 37 00:02:55 --> 00:02:55 It is simple. 38 00:02:56 --> 00:02:57 You type in the name and click on "Create". 39 00:02:58 --> 00:03:00 My php has already been created here. 40 00:03:01 --> 00:03:01 So I will use this one. 41 00:03:02 --> 00:03:07 Click on it and you can see that there are several tables inside. 42 00:03:08 --> 00:03:14 This is denoted by the symbol here when using phpmyadmin. 43 00:03:15 --> 00:03:20 This is guestbook from my guestbook tutorial. 44 00:03:21 --> 00:03:29 Now I will create a new table on this database for the specifics of this tutorial and I shall call it "people". 45 00:03:30 --> 00:03:32 The number of fields is quite important. 46 00:03:33 --> 00:03:34 You can't leave this blank. 47 00:03:35 --> 00:03:41 The number of fields on your table is for storing each column of data. 48 00:03:42 --> 00:03:50 For example, the first one usually when you are dealing with records, you can have an ID which is a numerical value. 49 00:03:51 --> 00:03:55 So it is a number that is going to increment each time. 50 00:03:56 --> 00:04:01 It will allow you to reference your records being stored individually by the unique number. 51 00:04:02 --> 00:04:05 And usually set this to the primary key. 52 00:04:06 --> 00:04:13 If you are not familiar with the databases, you need to start looking out for terms like primary key. 53 00:04:14 --> 00:04:21 I will not be dealing with secondary keys because its quite flexible way to do this anyway with mysql database. 54 00:04:22 --> 00:04:28 Just read up on databases in general, if you have Microsoft access or any other database . 55 00:04:29 --> 00:04:33 I suggest you to learn about groups of database. 56 00:04:34 --> 00:04:38 Ok, so the number of fields depends on how much data you want to store and what data you want to store. 57 00:04:39 --> 00:04:43 Usually when I am creating fields, I will bring out an usual blank document. 58 00:04:44 --> 00:04:46 And I will start by typing out the fields that I want. 59 00:04:47 --> 00:04:49 The first one is always going be ID. 60 00:04:50 --> 00:04:54 This is a self incrementing value every time I create a new record. 61 00:04:55 --> 00:04:59 So it will be 1 for the first record, 2,3,4 and the data will be stored after this. 62 00:05:00 --> 00:05:01 It is a very useful field. 63 00:05:02 --> 00:05:07 Since my table is called "people", I will store some data about people. 64 00:05:08 --> 00:05:16 So I will first type in the firstname and then I will type in the lastname and then the age and the gender. 65 00:05:17 --> 00:05:19 We can leave it there for now, to keep it simple. 66 00:05:20 --> 00:05:22 So here we can see that we have got 5 fields. 67 00:05:23 --> 00:05:27 I shall go back here and type in 5 and click "Go". 68 00:05:28 --> 00:05:30 It will also be great to see a pop up here in a minute. 69 00:05:31 --> 00:05:34 No you won't because we haven't created our field names yet. 70 00:05:35 --> 00:05:37 Ok! We have a standard here. 71 00:05:38 --> 00:05:39 There are a lot of options for these. 72 00:05:40 --> 00:05:41 But, a field is a fieldname. 73 00:05:42 --> 00:05:44 So the first one is going to be "ID". 74 00:05:45 --> 00:05:48 The type is the data type, that you want to store this field in. 75 00:05:49 --> 00:05:53 Anything that goes into it must be added here to this datatype. 76 00:05:54 --> 00:05:59 "varchar" which stands for variable characters is quite common. It is a very useful one and it requires a length. 77 00:06:00 --> 00:06:02 We can have 25 characters long here. 78 00:06:03 --> 00:06:01 50 characters long. 79 00:06:02 --> 00:06:03 Or 100 characters long. 80 00:06:04 --> 00:06:06 Or 1 character long. 81 00:06:07 --> 00:06:13 Actually we are just storing the type and the length of the data stored. 82 00:06:14 --> 00:06:16 It helps in storing, for example your firstname. 83 00:06:17 --> 00:06:23 Lets say our fieldname here is "firstname" and I have a "varchar". 84 00:06:24 --> 00:06:31 There is no point in typing in 500 characters because we will be using unnecessary amount of data. 85 00:06:32 --> 00:06:35 A typical firstname will be no more than 25 characters. 86 00:06:36 --> 00:06:40 Even if it is, it will not be more than 30 or 35 characters. 87 00:06:41 --> 00:06:47 But for now I will store my "firstname" as 20 to 25 characters and I will put 20 here. 88 00:06:48 --> 00:06:52 Our "ID" will be an integer because it is a number. 89 00:06:53 --> 00:06:54 It will be self incrementing. 90 00:06:55 --> 00:06:56 This will be 1,2,3,4 91 00:06:57 --> 00:06:59 The amount of records we use. 92 00:07:00 --> 00:07:02 And we have some other options here. 93 00:07:03 --> 00:07:04 That is here is primary key. 94 00:07:05 --> 00:07:10 We are going to select it and at extra, we can see that we got "auto underscore increment". 95 00:07:11 --> 00:07:12 This is an auto increment. 96 00:07:13 --> 00:07:15 It will give this particular function. 97 00:07:16 --> 00:07:20 Whenever you enter a new record, this will also automatically go up. 98 00:07:21 --> 00:07:22 So here we have "firstname". 99 00:07:23 --> 00:07:26 We have "lastname" and again I will set this as 30. 100 00:07:27 --> 00:07:28 And what else do we have??? 101 00:07:29 --> 00:07:33 We have "age" and obviously this is an integer and we have "gender". 102 00:07:34 --> 00:07:34 Ok. 103 00:07:35 --> 00:07:39 Now here instead of "age", I will say say "Date of birth". 104 00:07:40 --> 00:07:42 So this is Date of Birth. 105 00:07:43 --> 00:07:44 I will set this as date. 106 00:07:45 --> 00:07:50 Here I am trying to find a date datatype and I will see how this works. 107 00:07:51 --> 00:07:53 So our length for date doesn't have to be set here. 108 00:07:54 --> 00:07:57 We have a standard format for this. So we don't have to worry about that. 109 00:07:58 --> 00:08:04 Now I will set the "gender" as "varchar" of character 1. 110 00:08:05 --> 00:08:11 Now we can store "M" for male and "F" for female. 111 00:08:12 --> 00:08:15 Ok. If we go across here, we can see that there are a lots of options. 112 00:08:16 --> 00:08:18 You can comment this yourself. 113 00:08:19 --> 00:08:21 You can remind yourself what this field does. 114 00:08:22 --> 00:08:27 But usually name your fieldname appropriately so you know what data you are saving. 115 00:08:28 --> 00:08:34 Ok. Here I will click on "Save" and you can see that "people" has appeared here. 116 00:08:35 --> 00:08:37 This will ask you a query here. 117 00:08:38 --> 00:08:45 Now when I was talking about command line earlier, this is what you have to type to create that. 118 00:08:46 --> 00:08:49 However, we have used a graphic user interface to save ours. 119 00:08:50 --> 00:08:58 We can see down here, we have our fields, our types and our collation attributes, null data for example. 120 00:08:59 --> 00:09:06 The default value that is stored as.... say for example if you had a field saying "Has the user registered?" 121 00:09:07 --> 00:09:10 Or anything of your choice. You could use the default here too. 122 00:09:11 --> 00:09:20 For example if I wanted to store everyone, I have registered here as male by default or female by default, I could type "M" or "F" here. 123 00:09:21 --> 00:09:27 And we have auto increment here and also some other data that we don't need to know about in this tutorial. 124 00:09:28 --> 00:09:39 Ok, here we have created our table and if you go in part II of this, I will show you how to insert some data and also how to retrieve this data from your database using php. 125 00:09:40 --> 00:09:45 Join me in part 2. This is Evan Varkey dubbing for the Spoken Tutorial Project. (Script contributed by Juanita Jayakar).