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

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

Contributors and Content Editors

Chandrika