[Jack]

I've restored Lesson 8 below - however I will not be writing any more lessons after this. Please view my posts elsewhere to read why an how to contact me if you so wish.
===============================================================================

Hi all,

Sorry I haven't posted in a few weeks - There was a bit of a mix up on what I was covering along with a few other things - but the lessons are back.

This week I want to go through a bit of Database design, along with teaching some SQL (pronounced See-Qua-L ). The first thing that you will need is to have MySQL and phpMyAdmin installed on your server... Which (unfortunately) is beyond the scope of this tutorial... but email me if you need help because I do personally compile everythign from source on Linux Ubuntu servers anyway. If you are using XAMPP on any system... MySQL and phpMyAdmin is already available.

So access phpMyAdmin ( usually at <a href="http://localhost/phpMyAdmin)" target="_blank">http://localhost/phpMyAdmin)</a>. You should see on the front page there "Create new database". Name it something like "school". We'll be modeling some of the database requirement's in running a "school" system.

The type of databases we are dealing with here are RDBMS or Relational DataBase Management Systems. On a simple level its like dealing with a collection of Excel sheets ( within the one file). Each table you store contains data - which is "related" to data in another table. For example we will create a table to store student information, and one to store course information in, then another table to store which student's do which courses - thus creating the "relationship".

The relationships are purely theoretical, they arent something we build into the database. Systems such as phpMyAdmin and Microsoft Access can treat the relationships as something physical within the database - but it is essentially there to help us conceptualise the database, and make sure that we enter the correct information/data when performing the data entry step.

So in your database, create a new table called "students". Now here we want to have 5 fields or columns in the table. They are as follows:

<u>students </u>
uid | Integer
f_name | VarChar(20)
l_name | VarChar(20)
year | Integer
Email | VarChar (30)

The Integer data type is similar to our integer in PHP - but its a MySQL specific one (most datbase systems such as Access Oracle and MySQL have different levels of accuracy, and not all of them support the same data types).

The Varchar field is like a string, except we are specifying the number of characters that can be stored in the field. To enter this into the phpMyAdmin "create table" form, you enter the number in backets into the "Length" field.

Before you create the table, we need to do two last things... Create a Primary key. A Key is used to identify something in the database, our primary key is always unique to the table. We will create it on the uid field to create a "Unique ID". Firstly select the "extra" dropdown and choose Auto_Incriment, and then select the primary key field. The AutoIncr field means that the database will automatically increment the value in that field by one, for each new record in the database.

Go ahead and submit it and you should be given the success page. Now click on the "Insert" tab at the top. This will allow us to insert data into that table. Go ahead and enter some example data in there, 10 'students' should work nicely.

Next create a "courses" table, with the following fields ( I'll let you decide on the length of the varchars).

<u>courses</u>
cid | int
name | varchar
description | varchar
teacher | int


Now you can see that our course ID at the top will logically be our primary key, so go ahead and make that an auto increment and primary key.

Before we enter test data here, we need to know about the teacher dont we? There are two ways to do this.. We could use the system to store information about the teacher just as we do the students, or we could simply enter their name... This is where you need to assess the needs of your system and database. For this example you can do either, however I personally find its easier to place the teacher data into another table and create the relationship (which we'll do in a second). The reason is its easier to scale, both this table and anything else that might require teacher information. For example what happens if in a years time the school comes back to you and asks you to extend the system so that they can use it for new job placements - and not just for the student enrolment? Also, what if a staff member gets married and changes name.... Its going to be hard to change their name in every course they teach... not to mention time consuming and impractical!

So - whatever you decide to do, lets create another table called `enrolments`. Notice that I'm using "`" or baticks to show the table name? That's usually best practice, MySQL doesnt care and nor does Access - however its usually best to get your syntax correct.

Our enrolments table is designed to store which student is enrolled in which course - and its where we start to get interesting relationships. There are four types of relationships: "one to one", "one to many", "many to one", and "many to many". A one to one relationship implies that "for this record, there is only one match in the other table". This might be useful if you need to spread student information over multiple tables ( for example student contact information such as emergency numbers and gaurdian information, and another table for marks etc). the one to many relationship is when one record relates to more than one in the other table, for example if you had a user table and a magazine registrations table, you would have one user to many registrations. The many to one is the inverse of this - so the magazine registration table would have a one to many relationship, where one magazine has many subscribers. The many to many relationship is often the hardest to understand, as many records in this database can be related to many records in the other table. One example is if you had a table of sporting events and a table of age groups, and you wanted to generate a table of events, where every sport had an event per age group - thus many ages would have many sport's to compete in. This by the way is called a Cartesian product (though that's just a point of interest... where "every" record is related to "every" record).

Next, you need to create an `enrolments` table. This will store who is enrolled in what. I personally have a unique ID as the primary key - however you dont necicarily have to for this table. You do at least need a field to store the student ID and the course ID.

So in phpMyAdmin you need to select the students table, and you will be given a list of the fileds in that table. Below this list you should see a "relation view". If you dont, then you may have an older version of phpMyAdmin, or you might need to enable it in the configuration file ( google is your friend).

Here you can define what fields your fields link to. You need to select your student_ID field, and do the same with the course ID....

Next you need to select the field to display. When another table links to this one - which peice of data will be displayed in PHP as the option? Well for students we want it as the first name (or the last name... unfortunately we cannot have both)... Do the same with the courses table and its "display name". Now - the fun part. Click on the database name on the left, and then click on the "designer" tab. Have a look around in here.... this is the section of phpMyAdmin that really makes the relationships easy to understand. You can export this to PDF along with the database layout etc - so to have a backup of the structure in hardcopy.

So if you "insert" into the enrolments table, you should present you with dropdown menus for the data relationships - and it should show the name that you requested be shown. Go ahead and enter some test data into the courses, and then enrol a few students in a few classes.... you can make sure its correct by viewing the "browse" tab for each table.

Now, we get to the coding part. For this it will all be done in the SQL tab under our database name ( so click on the database name on the left, then the SQL tab).

Yhe SQL that we will use can generally be broken down into two categories, getting data and updating data. The first is the easiest, and it works in the format of:

SELECT [what] FROM [where]

The [what] lists the field names that you want, and the [where] is the name or names of the tables you want to get the data from. So lets try some:

SELECT `fname`, `email` FROM `students;

Enter that into the SQL and you should get all the students from the students table, however only have their firstname and their email.... yes?

But I don't want everyone, I only want the people who are seniors ( or in year 10 or higher)... how do we do it?

SELECT `fname`, `year`, `email` FROM `students` WHERE `year` >= 10;

That should work... yes?

Now... joins. I wont cover all of them here - however the concept goes:
You can JOIN table together on a given reason:

JOIN `table1`, `table2`
ON `table1`.`field`=`table2`.`field`

So we can query that:

SELECT `students`.`email` , `students`.`fname`
FROM `students`
INNER JOIN `enrolments` ON `courseid`
WHERE `enrolments`.`courseid` =1
LIMIT 30

Now, a few new things. Here I'm creating an INNER JOIN on the table enrolments, where the courseid is as specified (course id1 which for me was physics). As a result, I'm getting all my students enrolled in that course.

Also - I've added in a LIMIT statement. What I'm doing is I'm returning the first 30 records in the query. If there are more then they are NOT returned or displayed for me.

Well thats all well and good, but what happens when a new student signs up with the system?

Well:

INSERT INTO [where] ([fields]) VALUES ([values list]);

eg:

INSERT INTO `students` (`fname`, `lname`, `email`) VALUES ('first', 'name', 'me@demo.com');

Run that and look at it being added within the browse tab. Note that the year field enters 0 instead of a Null value. If you want the field to store a null value, then you will have to select "Not Null" when creating or editing the table structure. Otherwise the integer field will make the value 0. Also within the table structure you can tell it the default value if data isn't entered.... for example you could start all anonymous students in kindergarten (though you might annoy a few people).

OK, so you made a mistake on your entry? You forgot that their email is actually them@somewhere.com ? Not a problem, we can simply UPDATE the table:

UPDATE [what] SET [field]=[value] WHERE [condition];
note that you usually want to set a condition because otherwise it will update ALL records in the specified table.

UPDATE `students` SET `email`='them@somewhere.com' WHERE `fname`='first';

One final thing to cover is creating a VIEW. A VIEW can be considered a read only table which displays selected information. Later on we will cover permissions on databases... but you will see that we can grant a specific user access to only a specific list of tables or views. Say for example the students table contains a field about their medical record... We dont want the admin to be able to view that - so we can create a view which only displays their crucial information that admin needs to know about ( so that no matter what, the admin cannot read it)....

CREATE VIEW [name] AS
SELECT [what] FROM [where] WHERE [condition]

This is similar to the JOIN, in fact the JOIN's can be used in here - so that a view represents data from more than one table.

I personally don't use views as often as I probably should, however I do find them useful in a number of different occaisions ( such as simplifying future SQL queries whenver a specific join is constant through multiple tables).

Thats all for tonight, As always post if you have questions. Next lesson I'll cover the PHP side of handling these queries.

C YA!

This post has been edited by Jack: 10 August 2009 - 09:06 PM