Introduction

This resource aims to show readers how to develop basic databases and has been developed to cover the material in Achievement Standard 91879.

Most sections have a link to a video tutorial showing the process. These links can be found in coloured boxes (like the box around this paragraph).

If a page appears to be blank, please click on the right arrow to see the content.

A Note on the Videos: If a given video appears as a black screen / does not seem to play, hit the 'pause' button, go back to the start and then click the 'play' button again.

Have feedback? I'd love to hear from you. Please feel free to email through your thoughts.

Creative Commons License

This work has been made available under the following license:

Teachers are encouraged to modify this work to suit their students' needs. Please click on the image above for more detail on the CC license applied to this material.

I'd like to acknowledge the support of Whangaparaoa College and the New Zealand Ministry of Education during the creation of this resource.

Visual Check

What do you see below <choose an option from the list> ?

Here is some test code with a hyperlink.

Text in a pale orange box with a dark orange outline

Text in a white box with a dark orange outline (no orange background)

Other

Great!

Your ePub reader seems to be displaying the material as intended. Please click the link below to proceed.

Onwards!

Eeek!

Your ePub reader does not show all of the intended formatting. You can still use it but it does not look as pretty.

You might want to consider using readium (with Chrome) or qiu (with Mozilla).

If you wish to continue with your current reader, click the link below.

Onwards!

Annotating - How To

From time to time, you will need to take screen shot evidence of your program output and you will need to annotate this. Please watch this video for some hints on how to easily annotate those screenshots.

More Hints

Make sure any annotations are large and easy to read

Do not resize images if they have been annotated!

To easily gather evidence, consider videoing your screen when testing and using screencasting software.  Then grab the screenshots you need from the video (use the snip tool if you have a windows device). This makes getting the screen shots quick and easy and it also means that you won't forget to screen shot key evidence while testing.

Annotating - how to (Video)

01_annotate_howto

Introduction

The video for this section is at the preceding link.

Key Ideas

Many of the websites we routinely use are actually large databases

Databases can store vast amounts of information which can be sorted, filtered and displayed to users

Introduction - Video

01_db_intro

Resources

Below is a list of resources / web pages used in this eBook. Please consider bookmarking the sites below for easy reference.

Software

Brackets (text editor)

XAMPP

GIMP (image manipulation software)

Other Tools

draw.io - Wire Framing Tool

html validator

css validator

Practice Task

In this resource, you will learn how to create a simple database. The database will hold reviews for several books with the potential for more books to be added via phpMyAdmin if desired.

The focus in this standard is to create a basic, refined database. What this means is that we will create a website / database and then refine / adjust it based on testing and user feedback.

We will...

Plan our website / database

Recycle code to create the index page and contact page

Create a database to hold the book review information

Link the database and website so that users can see information from the database via the website

Create a search area that allows users to search by title, author, genre and rating

Test that what we have works as expected and is easy to use

Test that the final site is accessible and ensure that it displays well on both IE / Edge and Firefox / Chrome

At each state we will test that our html and css validate. We will also carefully document how the site has been refined by taking 'before' and 'after' screenshots to show how the outcome has been developed (and improved).

Important

Deal Breakers (Must Haves)

As you develop your outcome, you need to...

Plan and create a database / website using HTML, CSS and PHP

Comment your HTML, CSS and PHP

Test that your website / database works as expected

To Get More Than Achieved...

Check that your HTML and CSS validate

Have correct data types and sensible size limits on your database fields

Have comments which describe the behaviour of your HTML, CSS and PHP

Refine your outcome (ie: test and fix, use the information from your testing to improve your outcome - you need to provide evidence of this process)

Check that your database is easy to use with the help of a usability volunteer

Ensure that your outcome is aesthetically pleasing (ie: make good use of design concepts such as contrast, repetition of elements, alignment and proximity).

Going Beyond...

The items below go *way* beyond the standard.  Videos showing how to implement these features are right at the end of the eBook.

Genre drop down set up so that it is populated by the contents of the database

A nicely designed search area which uses FontAwesome's magnifying glass instead of larger buttons with the word 'Search' on them

Folder Structure

Your folder structure should look like this...

Software Suggestions

To create this outcome you need...

A text editor (we recommend brackets)

An environment which lets you create sql databases (eg:XAMPP, phpMyAdmin). If you have your own device and have worked through the Web Design eBook in this series, you probably have this software installed.

Database Planning

The video for this section is at the preceding link.

Key Ideas

Read through the task and plan the database

The 'test data' is critically important as this usually shows you what your table needs to include

Either recycle an old design or create a new wire frame for this project

Decide on the data-types and sizes

Document your planning and justify your decisions if necessary

Data Types

When planning your database, you'll need to work out what data type to use for each column. A full list of data types is at the preceding link.

Here are the data types we'll be using ...

varchar - variable character, length must be defined

integer

text - suitable for our book review, maximum length is 65,535 characters

Field Size

The size of fields matters quite a bit. If the field length is too short, it might mean that data gets left out / is inaccurate. If a field is too large, then over thousands of entries we could end up with a database that uses much more space than necessary.

When creating this eBook, I googled for the ideal size for a name field and found that the "UK Government Data Standards Catalogue suggests 35 characters for each of Given Name and Family Name, or 70 characters for a single field to hold the Full Name"*. I rounded this up to 100 for the field name for Author Name.

* Source: https://stackoverflow.com/questions/30485/what-is-a-reasonable-length-limit-on-person-name-fields

Database Planning - Video

02_db_planning

Common Errors

The video for this section is at the preceding link.

Key Ideas

When writing codes, things sometimes go wrong

If you get an 'Unable to Connect' error when you try and access 'localhost', check that you have XAMPP running

If you get an 'unexpected ...' error, look at the preceding line mentioned in the error message - you might have a missing semi-colon (;) or close bracket

As you code, comment your end brackets!

If you get an 'unexpected end of file' message, it means you have an unclosed curly bracket.

Common Errors - Video

00_common_errors

Comment Your Code

Why Comment?

The standard requires comments - even to get A

To get more than 'A', your comments should describe the code's behaviour

Commenting makes it easy for your to edit your code and fix errors

How to Comment...

Index Setup

The video for this section is at the preceding link.

Key Ideas

We can recycle code from a previous project to save time

The font folder will only be used toward the very end of this resource when we change our clunky 'Search' buttons into nice magnifying glasses (this part is optional)

Recycling the contact page lets us kill two birds with one stone

Index Setup - Video

03_index_setup_1

Formatting & Includes

The video for this section is at the preceding link.

Key Ideas

Comment your code!

Changing the colours / look & feel is optional

Change your sidebar so that it becomes a 'search' area - we will include this on every page of our site

Formatting & Includes - Video

04_formatting_includes

Sidebar Setup

The video for this section is at the preceding link.

Key Ideas

Create a contact page if you have already done so.

The text for the home page is in the support files area.

Put in some placeholders for our Search area.

Optionally change the background / colours of the site.

Sidebar Setup - Video

05_side_setup

Checkpoint 1 - Website Setup

Submit the following:

Evidence template

Screen shots showing that your index and contact pages have been created

Database Setup

The video for this section is at the preceding link.

Key Ideas

Use your planning to help set up your database.

Use phpMyAdmin to create your database, then create a table within your database.

Set up an ID column so that it is an auto-incremented integer and is also the primary key.

Insert your data using the 'import' option (see the video for details).

Remember to skip the first row when importing!

Database Setup - Video

06_database_setup

Checkpoint 2 - Database Setup

Submit the following:

Evidence template which includes wire frame and database planning

Screenshots showing that database has been created (see below)

Database Connect (part 1)

The video for this section is at the preceding link.

Key Ideas

We can use a 'config' file to store up our database details - this makes changing the database name, password or host very easy.

Include the config file in your head_nav.php file

PHP variables always start with a $ sign

Database Connect (part 1) - Video

07_dbconnect

Show All (Part 1)

The video for this section is at the preceding link.

Key Ideas

Create a 'showall.php' page and set up the structure.

Then validate your html

Set up the sql, a query and place the results of the query in an associative array. Confused? Watch the video

Use phpMyAdmin to generate the sql

Back ticks are different to apostrophes.

Show All (Part 1) - Video

08_get_alldata

Show All (part 2)

The video for this section is at the preceding link.

Key Ideas

Once we have connected our database and website, we can display data from the database on the website.

Comment your code

Use an 'if' statement to check if there are any results of the query.

If there are no results, display an error message.

If there are results, display them using a 'do / while' loop.

Put in the title information first, then test!

Show All (Part 2) - Video

09_showtitle

Show All (Part 3)

The video for this section is at the preceding link.

Key Ideas

Take a 'before' screenshot showing how your formatting looks.

Refine your formatting and take an 'after' screenshot.

Systematically replace the 'author', 'genre', 'rating' and 'review' placeholders with information from your database.

Use a 'for' loop to display actual stars rather than the number of stars.

The ascii symbol for a star (★) is &#9733;

Show All (Part 3) - Video

10_ShowAll_part

Checkpoint 3a - Show All

Submit the following:

Screen shots showing that your database and website are linked (ie: screen shot the page which shows all entries in the database). If you like, feel free to add your screen shot to your evidence template.

Title Search (Part 1)

The video for this section is at the preceding link.

Key Ideas

Create a 'title' search box and Search button

Set the width of the search box using CSS

Title Search (Part 1) - Video

11_titlesearch_form

Title Search (Part 2)

The video for this section is at the preceding link.

Key Ideas

Recycle your 'showall' page to create your title search

Use phpMyAdmin and the 'Search' button to generate the sql

Test your page

Title Search (Part 2) - Video

12_titlesearch_working

Data Sanitisation

Sanitising data is really important as it can help prevent hackers from subverting our site. Curious? Read more about SQL Injections and why you want to sanitise your data at the preceding link.

The video for this section is at the preceding link.

Key Ideas

First, format your 'error' box so that it stands out.

Use the function supplied in the support files area to 'clean' data that users enter into your search boxes

Then use mysqli_real_escape to handle special characters (like apostrophes) in data correctly.

Show evidence of refining your outcome on your Evidence Template

Data Sanitisation

13_data_sanitisation

Author / Genre Search

The video for this section is at the preceding link.

Key Ideas

Recycle your title search to create an author search

Test that your Author Search works

Then recycle your title search to create a genre search.

For your genre search, create a drop down menu. For now, we will hard code the values in - towards the very end of this eBook, there is a video showing how we can pull the genre from our database (this is *very* optional).

Author / Genre Search - Video

14_author_genre_search

Ratings Search

The video for this section is at the preceding link.

Key Ideas

We can recycle our 'genre' search into a ratings search

This time around, we need two boxes (and two variables)

Ratings Search - Video

15_ratings_search

Query Testing

The video for this section is at the preceding link.

Key Ideas

On your evidence template, create a Test Plan

Then systematically work through your test plan to confirm that the database / website work correctly

Either video your testing or create a series of annotated screen shots showing that the outcome works correctly

Query Testing - Video

16_Query_Testing

Checkpoint 3b - Query Testing

Submit the following:

Your evidence template showing that you have successfully set up and tested your queries.

Usability Testing

The video for this section is at the preceding link.

Key Ideas

It's really important to test that your outcome is easy to use.

Ask a volunteer to complete some typical tasks on the database.

Observe them (record your screen so that you don't need to take notes)

On your evidence template, note down areas that need to be improved on.

For each issue, take a 'before' screen shot, fix it and then take an 'after' screen shot.

Usability Testing - Video

17_Usability_Testing

Almost Done...

Congratulations for getting this far. If you are short of time, you are welcome to write a brief statement towards the end of your evidence template in which you discuss how you have considered the implications of your outcome. You can then submit checkpoint 4 and move on to the assessment at this point. The rest of the videos in this eBook are optional.

Having said that, if you have time, they provide an easy way to further refine your outcome and the features that we are about to implement can be recycled in future projects. If you are going to implement these extra features, you might want to wait until you have implemented the extra features before writing your 'Implications' paragraph.

Genre Options (Part 1)

The video for this section is at the preceding link.

Key Ideas

We can use SELECT DISTINCT to select unique values from a database.

We can pull the data from our database and use it to populate our 'genre' drop down.

Genre Options (Part 1) - Video

18_genre_options_part1

Genre Options (Part 2)

The video for this section is at the preceding link.

Key Ideas

Test your outcome!

Update your evidence template - this time around it is quite easy as you can use 'version 1' of your outcome for the 'before' screen shot and 'version 2' for the 'after' screen shot.

Genre Options (Part 2) - Video

19_genere_options_part2

Refined Search Area

The video for this section is at the preceding link.

Key Ideas

Our 'Search' button is quite clunky, we can replace it with a magnifying glass.

We can style our Search Area by writing rules for classes that we have already defined.

We can use FontAwesome to insert a magnifying glass

As always, remember to document the refinement with a 'before' and 'after' screen shot.

Refined Search Area - Video

20_MagnifyingGlass

Implication

To meet the standard, you need to ...

described relevant implications (must have for A)

address the relevant implications (for M / E)

Below is a list of implications that you could consider. You don't need to include all of them - it is hoped that you will describe / address the implications that are relevant to your database / website.

social

cultural

legal

ethical

intellectual property

privacy

accessibility

usability

functionality

aesthetics

sustainability and future-proofing

end-user considerations

health and safety

The box below shows and example 'implications' paragraph.

Implications...

My website is intended for use by teenagers / adults who enjoy reading. It is appropriate for this audience as the books being reviewed are suitable for a general audience. I have not reviewed any books which are of a sensitive / controversial nature which helps makes this outcome socially (and culturally) acceptable.

The outcome has been designed so that it looks good and works as expected. I have tested it extensively to ensure that this is the case. One of the benefits of creating a book review website is that it is easy to update the site with new books as they are reviewed.

I have written all the reviews myself and have been careful to honour copyright. The image used in the logo was downloaded from Pixabay and has been tagged as being in the public domain.

Finally, I have tested the site's usability with the help of a volunteer and have made changes to ensure that the outcome is easy to use.

Checkpoint 4 - Practice Database

Submit the following:

Your updated evidence template showing that you tested the usability of your site and considered the social implications of your outcome.

Your complete outcome. Please watch the video on the next page to see how to export your database SQL

Exporting SQL - Video

21_exporting_sql

Congratulations!

Well done. You have reached the end of this eBook and should be ready to attempt the level 1 datatbase assessment.

Having said that, your journey is just beginning. Hopefully this material has inspired you to continue creating website / databases. Going forward you might...

Learn how to set up a form on your website to allow users to add their own reviews to your site

Investigate how to set up a 'commenting' feature on your site so that users can comment on reviews and note whether they agree with the review

Have fun!