PHP and MySQL

1. Overview

2. XAMPP and PHP introduction

3. Alternative Online PHP compilers

4. Online PHP tutorials

5. HTML Introduction

6. HTML Tables and Forms Introduction

7. Forms processing exercise

8. Examples of form XSS injection

9. Forms and PHP processing

10. Forms and PHP processing overview

11. Using date related functions - Date() and mktime()

12. Loops - while and do while

13. Loops - for

14. Using PHPMyadmin to create tables and relationship

15. Connecting the front end HTML and backend PHP

16. MySQL data types and their lengths

17. Introduction to SQL

18. Tables relationship

19. SQL commands

20. HTML + PHP + MySQL integrations

21. MySQL table columns are case sensitive

22. Display fields from two tables

Using the following PHP code as example:

After posting to this page from another form page with id value and you see the following error:

The error is in the select statement:

But after checking the code but cannot locate your error, the easiest way will be to use phpmyadmin to check:

1. Select and copy (CTRL-C) the statement from the browser:

2. Go to phpmyadmin, select the relevant database, paste the code and click GO:

3. Immediately the error will be displayed:

Looking at the error message, it becomes obvious that we have spelled teacher with a missing 'a'.

Correct the PHP file and run again:

Happy debugging!

To combine two related tables for output, we need to use "join ... on" to join two tables with their relationship.

Use these two related tables - staff and classes:

Create a database swd and import the swd.sql file. 

Eg (1) To output the result below, we will need to combine both tables in one SELECT statement:

The command required is:

select surname, firstname, name, size from staff join classes on staff.id = classes.form_teacher_id;

Eg (2) To output the result below:

Observe that the columns are the same as Eg 1 but it is a subset with all size = 40.  Hence, the command required is:

select surname, firstname, name, size from staff join classes on staff.id = classes.form_teacher_id where size = 40;

Check your spellings if there is any error.

By default, the columns/fields for vachar and text type are case insensitive.

This will affect the results of SELECT queries => Although the data is "Goh", it will still be returned for a query that asked for "goh".

If case sensitive is required for a field, it should be defined using Collation ending with _cs.

1. Create a new database customerorder and import the SQL file:

Use a simple HTML form to activate a PHP file that queries the database:

2. Create the following HTML file (display1.html)

3. Create the following PHP file (showcustomer.php)

Note that for security purpose, htmlspecialchars( ) is used when a variable is to be used for display.  For other occasions when a variable is to be used to insert data into MySQL database, we will use mysql_real_escape_string( ) function.

Also, mysql_free_result( ) is not necessary here.  Read this.

4. Execute display1.html and submit:

Retrieve custID from database to allow user to select:

5. Create a new PHP file (display2.php):

6. Execute the file and submit:

This tutorial illustrates the concept of a simple application accessing database.

One of the easiest method to try out SQL commands is through phpmyadmin interface.

Refer to the basic and categories of SQL commands.

1. Start XAMPP Apache and MySQL servers.

2. In browser, access localhost/phpmyadmin interface

DDL (Data Definition Language) commands

3. Create the database: swd

To create database, the command is CREATE DATABASE xxx;

SQL Commands are case-insensitive but for name of database, tables and fields, they are case sensitive.  So we could write “CREATE DATABASE swd;’ instead.

Each SQL command ends with ‘;’

4.1 Create the staff table with the given definitions below:

Since each SQL command ends with ‘;’ and white spaces such as blanks and next line have NO effect on the command, we should use white spaces to make code readable and easier to check.

To create table, the command is CREATE TABLE xxx (aaaa, bbbb, cccc);

Do NOT type the following since white space is NOT used to format.

Instead follow this:

Explanation of each part is as follows:

:

:

End each field definition with ‘,’ except the last one. 

Difference between command and using the phpmyadmin interface to define a field can or cannot be empty: In the phpmyadmin interface, we only check the null box when the field can be empty.  Whereas using command, we need to specify when the field cannot be empty.

4.2 Create the classes table with given definitions below:

The steps are:

5. Create relationship between staff and classes:

Note that ‘—’ is used to add comments:

6. Confirm that the staff table, classes table and their relationship are created.

DML (Data Manipulation Language) commands

7. Insert values to the staff table:

To add values, the command is ‘INSERT INTO xxx (yyy, yyy, yyy) values (aaa, aaa, aaa), (bbb, bbb, bbb), (ccc, ccc, ccc);’

8. Insert values to the classes table:

9. Make sure the values are added:

We tried a few SELECT commands to retrieve data from the database.

For example:

and the output is:

That’s all for this simple tutorial.  Refer to the basic and categories of SQL commands for more details.

There are 3 types of table relationships: 1. One-to-one relationships 2. One-to-many relationships 3. Many-to-many relationships

More info on table relationships.

In a one-to-one relationship, each row in one database table is linked to one and only one other row in another table. In a one-to-one relationship between Table A and Table B, each row in Table A is linked to another row in Table B. The number of rows in Table A must equal the number of rows in Table B.  For example, if we have two related tables: one for students' bio data and another for students' emergency contact.  These two tables are linked by student's ID.  There is only one row of data for each student in both tables.

In a one-to-many relationship, each row in the related to table can be related to many rows in the relating table.  For example, one table for every Singapore addresses and another for every citizen information. Assuming everyone has only one official address, for every address, there may be one or more citizen staying there.

In a many-to-many relationship, one or more rows in a table can be related to 0, 1 or many rows in another table.  For example, one table for teachers teaching info and another for class info.  Every teachers may teach one or more classes while every class may have one or more teachers.

Example of using diagrams to illustrate the above three types of relationship.

Structure Query Language (SQL) is a programming language used for storing and managing data in any Relational Database Management System (RDBMS).  Today almost all RDBMS (MySql, Oracle, Informix, Sybase, Microsoft Access) uses SQL as the standard database language.

SQL is used to perform all type of data operations in RDBMS.

More info on Introduction to SQL.

For our module, we will study and use two groups of SQL commands:

1. DDL : Data Definition Language - to create database and tables.

2. DML : Data Manipulation Language - to insert, delete, update or merge row/s of data from/into the tables and also retrieve information from databases.

More info on DDL, DML and Select in details.

We have used the following data type in recent lessons: int for integers, decimal for decimals, varchar for variable length of characters (less than 256 characters), text for paragraph more than 256 characters and time for hh:mm:ss time.

To keep it simple, only the following types we must specify their lengths: varchar and decimal (no. of digits, no. of decimal places).

An interesting Q&A link for reading.

1) Creating new database and tables using PHPMyAdmin:

In the previous tumblr, we learned using PHPMyAdmin to create simple database and tables.

In this tumblr, we will be creating the following database and tables:

We need to create a new database ‘module’ with the following two tables - take note of using ‘INDEX’ for module_id field in tbl_students table for the foreign key:

Then we need to use Designer to create the relationship between these two tables:

We will get the following:

2) Inserting data:

Insert the following test data into both tables, starting from tbl_modules, then tbl_students.  *** The order depends on the relationship we defined in step 1.

3) Creating the front end - a simple form with a button:

Create a new HTML file - module.html:

After the button is clicked, it will use ‘POST’ method to call the backend ‘showmodule.php’ file.

4) Creating user/s to access ‘module’ database:

By default, there is a ‘root’ user with no passsword that come with XAMPP.  It is a good practice to create another user with password to access your own database.

Now we have a user ‘user1’ with password ‘user1’.  For real application, we need to create good password.

5) Create the backend access to database:

Create a new PHP file - showmodule.php:

Read the comments in the codes to understand the steps required to display data from our database. That’s all for this tumblr. 

For the last step, if you have errors, check the spelling of the user, database, tables and fields with the ones you created in PHPMyAdmin.

As an exercise, repeat step 3 & 5 to display the data from tbl_stduents.

The key of getting data from the database tables is the Select Query statements such as ‘Select * from tbl_modules’.

Read this tutorial for more understanding of Select Query.

phpmyadmin is a GUI tool to help beginners in accessing MySQL for common database operations such as creating/deleting database, creating tables, inserting data, etc.

In XAMPP, after starting MySQL service, you may access this tool by clicking on [Admin] button:

Alternatively, just type “localhost/pgpmyadmin/” into the browser URL will be able to access it directly.

A database is a collection of data that is organized for easy access, manage and update.

For example, if we are going to create a database and table for the following data:

From the above, we know the following:

1) we need 5 columns (also known as fields or attributes)

2) for reg_number, it should be auto increment and it should be unique for each row (also known as record or tuple)

3) for remarks, it may be empty

Step 1) create the database

phpmyadmin > Click on top: Database

Type the name of the database and click ‘Create’

Step 2) create the table

Click on ‘addressbook’ database on the left side

Key in the name of the table and the number of column to create

Type in the columns name, types, length, index (Primary key), A_I (auto-increment), null (may be empty) and click ‘Save’.

varchar (variable characters) contains fewer than 256 characters while text is used for longer than 256 characters.

Our table will be created but it will be initially empty.  Click on ‘Insert’ to add data:

Since reg_number is auto increment, we should leave it blank and let system automatically assign a number. Subsequently, after a record (a row of data) is deleted, its number will NOT be reused.

You may continue to add the other two rows of data.

That’s all you need to create a simple database with one table and insert a few data.  Relational database is powerful when you start to create more than 1 table and link them via relationship.

Create relationship between tables

The main advantage of a Relational Database Management System (RDBMS) such as MySQL is its ability to define relationships between tables so that there no redundancy and ensure integrity of data.

External tutorial on using PHPmyadmin to create relationships

We shall learn about one-to-many, many-to-many and one-to-one related tables later on.

http://www.homeandlearn.co.uk/php/php.html

http://www.homeandlearn.co.uk/php/php.html

The requirements:

The Answers:

hobbies.html - http://phpfiddle.org/lite/code/ht5-phj

hobbies.php - http://phpfiddle.org/lite/code/iex-wrp

Copy and paste any of the example codes into our form textbox and submit to see the action.

https://skydrive.live.com/redir?resid=551C0A443C93B84!3045&authkey=!AEhqz111yIMktiQ&ithint=file%2c.txt

We will learn how to use htmlspecialchars() to defend.

Test the above XSS injection using firefox will work but not when using chrome.  Read more about chrome anti-xss feature which works on simple XSS injection.

http://blog.securitee.org/?p=37

Read this tutorial for more details:

1. http://www.homeandlearn.co.uk/php/php9p1.html

2. http://www.homeandlearn.co.uk/php/php9p2.html

3. http://www.homeandlearn.co.uk/php/php9p3.html

lab12.html will look like the page below, take note of the name of the field (given in blue):

lab12.php output when everything are filled up in the html form and submit:

lab12.php output when the textbox or textarea fields are empty:

This is an example of how the process work:

interest.html contains the form with various form elements.  When the submit button is clicked, the information will be sent to the interest.php script to process.

We covered two methods to send the information: GET and POST. Using GET, you will see the content with the URL such as ?name=xxx&register=yyy  (this is also known as query string).  On the other hand, using POST, the information will not be visible to the user's naked eyes.

interest.html page looks like:

interest.html code:

interest.php code to process the information - take note of !empty( ) function and the foreach loop to process checkboxes:

2. HTML – Tables and Forms

from

Telerik Software Academy