Common Issues with MySQL and PHP

Overview

1. Alternatives to Deprecated mysql_* Functions
2. SQL Injection: What to do with $_POST and $_GET variables
3. How to Insert Tablenames and Fieldnames Properly
4. What to do With Dynamic Values?
5. How to Use Aggregate Functions (COUNT, SUM, MIN etc)
6. How to Paginate and Create Page Number Links?
7. How do I Store Files: Filenames or BLOB?
8. Splitting the Big Table: Normalization
9. How to Get Data From More Than One Table
10. How to Store Menus and Submenus
11. Storing and Retrieving Authentication Data

References and Further Reading

_______________________________________________________________________________________________

Overview

The sections below will give examples on how one can approach certain issues with
SQL and PHP.

What they are not meant to do is provide a working snippet that you can just drop into
your own code - this is not an Idiot's Guide, just a few notes.

Don't be afraid to ask questions. If you spot any mistakes or disagree with anything
listed below, again please post. This is especially important if you know of a better way
that is also not too complicated for the novice to understand.

Tutorials should be organic, not monolithic.

_______________________________________________________________________________________________

1. Using Deprecated mysql_* Functions - The Alternatives

mysql_* functions like mysql_query, etc. were deprecated in version 5.5.0 and are due
to be completely removed in version 7, as are all deprecated functions from the 5.x cycle.

Panic! Well, no, not yet. Version 7 is not due until the end of the year (2015) and then
your host should give the option of upgrading rather than automatically doing it for you.

However, it makes little sense to keep producing code based on
deprecated-soon-to-be-removed functions. So, we seem to have 2 options - MySQLi
 or PDO.

For most general purposes, it seems to make little difference which you choose, but you
 should invest some time in researching the differences (beyond the scope of this tute)
and make an informed decision.
There are many PDO vs. MySQLi pages out in the wild - but here are a few which I
found particularly useful - some of the comments are even better than the posts
themselves:

• http://code.tutsplus.com/tutorials/pdo-vs-mysqli-which-should-you-use--net-24059
• https://secure.php.net/mysqlinfo.api.choosing
• http://rudiv.se/Development/Resource/when-to-use-mysql-vs-mysqli-vs-pdo-in-php

A common theme from undertaking more research that those listed above seems to be
 that PDO is more convenient but less powerful. I have no particular view on which is
'best' - they're simply different. I did however, find one particular issue though with
MySQLi that has not to my knowledge, been widely discussed - some functions
(e.g. fetch_all) failed to work as the mysqlnd (native driver) wasn't installed on some
hosted servers I used - see the following link for details:
http://php.net/manual/en/mysqli.summary.php.

For beginners, MySQLi may prove to be easier as it offers procedural functions,
which are very similar to mysql_* functions, in addition to OOP methods, where PDO
is purely OOP.

Daniweb's own pritaeas has created some excellent code snippets on the use (binding)
 of these here:

PDO: https://www.daniweb.com/web-development/php/code/462126/pdo-binding-example 
MySQLi: https://www.daniweb.com/web-development/php/code/462098/mysqli-binding-example

Any further points in this tute shall use PDO (and/or MySQLi) instead of the old
mysql_* functions.

_______________________________________________________________________________________________

2. SQL Injection: What to do with Variables

Many new to DBs assume that SELECT statements are inherently safe, as opposed the
more dangerous UPDATE and DELETE. While there is some merit in this way of
thinking - especially if you create a DB user that only has SELECT privileges, this is
not strictly true.
Statements such as the following can be deemed 'safe' due to their hard-coded, static
nature:

SELECT * FROM `table1`
SELECT * FROM `table1` WHERE `id` = 6
SEELCT * FROM `products` WHERE `cat` LIKE 'drill%'

However, statements such as:

SELECT * FROM $myTable
SELECT * FROM `table1` WHERE `id` = $id
SELECT * FROM `products` WHERE `cat` LIKE '$cat%'

could be problematic. Especially true if the variables store user input (e.g. $_POST and
$_GET).

The much-maligned W3Schools website has some good examples as to the nature of
 these dangers here (which I shall not plagiarise):

http://www.w3schools.com/sql/sql_injection.asp

So how to mitigate against this?

We have 2 main approaches - using PDO, we can use prepared statements but using
MySQLi we can use prepared statements or choose to sanitize input variables.

MySQLi still has the procedural mysqli_real_escape_string(), or OOP
mysqli->real_escape_string() (or mysqli->escape_string()).

Prepared Statements (MySQLi OOP)

$id = $_POST['id'];
$sql = "SELECT * FROM `table1` WHERE `id` = ?";
$stmt = mysqli->prepare($sql);
$stmt->bind_param('i', $id); //i = integer and s = string
$result = $stmt->execute();

Prepared Statements (PDO)

$id = $_POST['id'];
$sql = "SELECT * FROM `table1` WHERE `id` = ?";
$stmt = pdo->prepare($sql);
$stmt->bindParam(1,$id,PDO::PARAM_INT); //1 = position of ?
$result = $stmt->execute();

Although the prepared statements appear quite verbose compared to a sanitizing
option, this is not really the case, especially when many input variables are to be
placed into the SQL statement.
PDO offers a 'shorthand' method of binding parameters (or values), within the
execute() method itself.

$id = $_POST['id'];
$sql = "SELECT * FROM `table1` WHERE `id` = ?";
$stmt = pdo->prepare($sql);
$result = $stmt->execute(array($id));

Be aware that passing parameters via the execute() method casts them as 'string',
regardless of whether they are integers, floats etc.

From a personal point of view, I never use a *_query with dynamic data, I always use
a prepared statement.

If you do decide to use PDO prepared statements, remember to read up on the
difference between bindParam and bindValue - in short, bindValue is useful for static
values, whereas bindParam is useful for a variable that may change from the time of
binding to execution.

_______________________________________________________________________________________________

3. How to Insert Tablenames and Fieldnames Properly

Whenever we include tablenames and fieldnames in our SQL statements, we should
enclose them in backticks (`...`). These are notsingle quotes.

The backtick button is usually found before the '1' key on the keyboard or below the
 'Esc' button. This is to ensure that the SQL is parsed correctly so that there is no
ambiguity.
SQL statements can often be parsed without them, but specific cases exist when they
will not.
This should be OK:

SELECT firstname FROM mytable

These will probably fail:

SELECT date FROM mytable
SELECT first name FROM mytable

The first will fail because the fieldname date shares its name with a mysql reserved
word (date). A full list of these reserved words can be found here:

https://dev.mysql.com/doc/refman/5.6/en/keywords.html

The second will fail because we have two distinct words for the fieldname. This will be
 parsed as meaning: "Get all values in field called 'first' and give it the output fieldname
 alias of 'name'".
In our case, the field first does not exist, so the query fails with an error. The simple
fix:

SELECT `date` FROM `mytable`
SELECT `first name` FROM `mytable`

It's good practice to include backticks, even though they may not be strictly required in
 all cases.

_______________________________________________________________________________________________

4. What to do With Dynamic Values?

In general when we place static values into a statement, we need to place quotes
around them if they are NOT numerical (numerical = int, float, decimal, tinyint etc).
If we do not place quotes around string-like values (including date and time), we will get
 an error.
Just search the forums and you'll see the absolute mess some members have gotten
 into.
This is an example of a statment that should parse correctly:

$sql = "SELECT * FROM `mytable` WHERE `cat_id` = 4 AND `username` LIKE 'diaf%' 
AND `date` = '2002-10-17'";

When dynamic values are added to the statement - we shall assume that they have
been sanitized for the sake of argument, there are many ways that they could be
written.
Some members have really gotten their knickers in a twist over this. I'll start with the
easy one:

$sql = "SELECT * FROM `mytable` WHERE `cat_id` = $cat_id AND `username` LIKE '$username%' AND `date` = '$date'";

Other members will do all sorts of stuff to unintentionally mangle their statement - the first example is unnecessary as variables can be parsed within a double-quoted string.
The second example has to be concatenated in this way as variables cannot be parsed within a single-quoted string:

$sql = "SELECT * FROM `mytable` WHERE `cat_id` = " . $cat_id . " AND `username` LIKE '" . $username . "%' AND `date` = '" . $date . "'"; //unnecessary

$sql = 'SELECT * FROM `mytable` WHERE `cat_id` = ' . $cat_id . ' AND `username` LIKE "' . $username . '%" AND `date` = "' . $date . '"'; //totally necessary

So from this it seems a double-quoted string is much easier to deal with than the single-quoted equivalent. However, there is one caveat.
Sometimes the variables will be array elements or class properties. In these cases, simple inclusion within the double-quoted string will fail, e.g.

$sql = "SELECT * FROM `users` WHERE `user_id` = $user['id'] AND `username` LIKE '$user['name_prefix']%'"; //will fail

In order to get this to work within the double-quoted string, we need to brace {...} the items:

$sql = "SELECT * FROM `users` WHERE `user_id` = {$user['id']} AND `username` LIKE '{$user['name_prefix']}%'"; //should work

Ok, that's pretty much it on that, but I should mention that none of this is really necessary anymore. WHAT??
Yes, heh heh, with prepared statements, we can avoid all this unpleasantness.

$sql = "SELECT * FROM `users` WHERE `user_id` = ? AND `username` LIKE ?";
$sql = 'SELECT * FROM `users` WHERE `user_id` = ? AND `username` LIKE ?';

We then just bind the values for MySQLi or PDO (or place an array of items into the execute() method for PDO).
PDO execute example:

$stmt->execute(array($user['id'], $user['name_prefix'] . '%'));

So, in conclusion - DON'T use braces, variables within a string or concatenation. Create a prepared statement.

_______________________________________________________________________________________________

5. How to Use Aggregate Functions (COUNT, SUM, MIN etc)

Quite often it's useful for our SQL queries to return aggregated data. Aggregate functions include COUNT, SUM, AVG, MIN, MAX. A full list can be found here:

https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html

In order to make use of these, we usually need to use the GROUP BY clause, but there are a few simple cases where they work without this.

Simple Cases

All the examples in this Simple Cases section will return a single record.
If you want just want to get a count of the number of records from a table (filtered or otherwise), then COUNT(*) is all you need:

SELECT COUNT(*) AS `cnt` FROM `mytable`
SELECT COUNT(*) AS `cnt` FROM `mytable` WHERE `myfield` < 73

The AS cnt above is optional, but it provides an ALIAS that makes it easy for PHP to use, e.g. $row['cnt']

If you want a simple SUM:

SELECT SUM(`numericfield`) AS `sm` FROM `mytable`
SELECT SUM(`numericfield`) AS `sm` FROM `mytable` WHERE `date` < '2014-10-13'

Or AVG, MIN and MAX

SELECT MIN(`somefield`) AS `mn` FROM `mytable`
SELECT MAX(`somefield`) AS `mx` FROM `mytable` WHERE `date` < '2014-10-13'
SELECT AVG(`price`) AS `ave` FROM `mytable` 

SELECT COUNT(*) AS cnt, MIN(`price`) AS mn, MAX(`price`) AS mx, AVG(`price`) AS `ave` FROM `mytable` 

More Complicated Cases

Sometimes we need to get multiple counts or sums (etc) from a table, for example, we may wish to count how many entries each user has in a table:

SELECT `user_id`, COUNT() AS `cnt` FROM `mytable` GROUP BY `user_id`

Notice the GROUP BY clause. This has the effect of grouping records into groups(!) and running the aggregate functions on each group individually.
So in the above query we will get a list of distinct users (user_id) along with the count of the number of times that they appear in the table.

We can use a similar method to get the price range of products from each supplier:

SELECT `supplier_id`, COUNT() AS `cnt`, MIN(`price`) AS `mn`, MAX(`price`) AS `mx` FROM `products` GROUP BY `supplier_id`

Sometimes we also need to filter and order our results. The ORDER BY clause comes after the GROUP BY and WHERE comes before.

SELECT `supplier_id`, COUNT() AS `cnt` FROM `products` WHERE `product_type_id` = 6 GROUP BY `supplier_id` ORDER BY `supplier_id`

On occasion, it could be important to filter on a calculated field (or a aggregated field):

SELECT `supplier_id`, COUNT() AS `cnt` FROM `products` WHERE `cnt` >= 10 GROUP BY `supplier_id` //this will FAIL!

The above tries to retrieve records where only the suppliers with at least 10 products. We can't use a calculated field in the WHERE clause, but hope is not lost!
We have the HAVING clause.

SELECT `supplier_id`, COUNT() AS `cnt` FROM `products` GROUP BY `supplier_id` HAVING `cnt` >= 10 

The above also illustrates the usefulness of having an ALIAS (`cnt`), otherwise we'd have to write out the expression (COUNT())in full again.

We often get questions involving grouping records with regard to Years and Months based on dates. We can use the GROUP BYclause to do this for us:

SELECT YEAR(`orderdate`) AS `yr`, MONTH(`orderdate`) AS `mnth`, SUM(`amount`) AS `total` 
    FROM `orders` WHERE YEAR(`orderdate`) > 2010 
    GROUP BY `yr`, `mnth` 
    ORDER BY `yr`, `mnth`

To finish this section, we're often asked, "is there a way to retrieve a list of values for each user in a single record"?

Yes, there is and we use GROUP_CONCAT to do that. Here's an example of listing all the countries of origin for all users in a users table:

SELECT GROUP_CONCAT(`country`) FROM `users`

That will give a comma separated list of every country in the table and it will include duplicates. In order to get distinct countries, we can do this:

SELECT GROUP_CONCAT(DISTINCT `country`) FROM `users`

Again, GROUP_CONCAT can be used like the other aggregate functions with the GROUP BY clause.

SELECT `date`, GROUP_CONCAT(`species`) AS `animals` FROM `pet_sales` WHERE `date` BETWEEN ? AND ? GROUP BY `date` ORDER BY `date` DESC

_______________________________________________________________________________________________

6. How to Paginate and Create Page Number Links?

There seems to be a couple of threads on pagination every month, even though it has been covered to death.
I shall include a snippet (without error handling) below as an example:

//Posts Per Page
$ppp = 10;

//Assume PDO object $pdo created previously
$res = $pdo->query("SELECT COUNT(*) FROM `posts`");
$numPosts = $res->fetchColumn(0);

$numPages = ceil($numPosts / $ppp);

$inputPage = isset($_GET['page']) ? intval($_GET['page']) : 1;
if($inputPage < 1) $inputPage = 1;
if($inputPage > $numPages) $inputPage = $numPages;
$start = $ppp * ($inputPage - 1);

$stmt = $pdo->prepare("SELECT `post_title`, `post_body`, `post_date` FROM `posts` ORDER BY `post_date` DESC LIMIT ?,?");
$stmt->bindParam(1, $start, PDO::PARAM_INT);
$stmt->bindParam(2, $ppp, PDO::PARAM_INT);
$stmt->execute();
$output = $stmt->fetchAll(PDO::FETCH_ASSOC)); //do whatever with it

NOTE: you cannot bind LIMIT values as an array in the execute() method since they are passed as strings and LIMIT parameters must be integers.

The values for $numPages and $inputPage also allow you to build 'page links', e.g.

$l = [];
for($i = 1;$i<=$numPages;$i++)
    $l[] = ($numPages != $inputPage) ? "<a href='?page=$i'>$i</a>" : "<span>$i</span>";
$links = implode(' | ', $l);

Then at the appropriate place in your markup:

<div id="pagelinks"><?=$links?></div>

There are many variations on this theme, for example, show the first five page links and the last, with an ellipsis (...) between non-continuous links.
That is beyond the scope of this tute, but feel free to post specific questions in the forums.

_______________________________________________________________________________________________

7. Storing Files - Filenames or BLOB?

Hmmm. Well, it depends on what you wish to do. There are pros and cons of using either.

I find storing filenames much easier in general than using BLOB fields, but this has its dangers.

If you are inserting file content into a table BLOB field, the file contents form part of the transaction, which is great, because then you don't have bits of orphaned data on failure.
This is a distinct possibility with storing a filepath linked to a physical location.

Also if you move servers, BLOB fields ensure that all the data is intact. Directory or server changes can ensure that paths are no longer valid.

BLOB fields also have the advantage that access to the content can be decided (e.g. with regard to permissions) as there is not a physical file to link to. This is more difficult to do with physical files in a public directory.

That said, in order to display the contents of a file (BLOB), it may need to be encoded or placed into a file with a specific header:

http://stackoverflow.com/questions/13225726/i-need-my-php-page-to-show-my-blob-image-from-mysql-database

Another issue with storing file contents is that files can be BIG(!) and this may impact negatively on your database's performance.

A Simple Table for Storing Files ('filetable')

id: INT(5), PK, AUTOINCREMENT
filetype_id: TINYINT(3), FK
content: LONGBLOB //If storing BLOB
location: VARCHAR(50) //If storing file location
filesize: VARCHAR(25) 
description: TEXT
filename: VARCHAR(50)
uploaded_at: TIMESTAMP 
user_id: INT(5), FK

The filetype_id is a foreign key which can be related to a table for filetype icons and filetype MIME headers etc.
A quick idea of a filetype table would be:

id: TINYINT(3), PK, AUTOINCREMENT
filetype_header: VARCHAR(25) //MIMEs e.g. image/png, image/gif etc.
icon: VARCHAR(25)

The following couple of mini-sections regarding file uploading are a little beyond the scope of this tute, but I'll include it for completeness anyway:

An Simple Upload Form

<form enctype="multipart/form-data" action="/upload.php" method="post">
    <input type="hidden" name="MAX_FILE_SIZE" value="99999999" />
    <input name="myfile" type="file" />
    <label for="description">Description</label>
    <textarea name="description" id="description"></textarea>
    <input type="submit" value="Upload File" />
</form>      

Note the enctype - this must be included and set to multipart/form-data, otherwise the file will not be uploaded. This is a common mistake with beginners who omit this property.

Handling File Upload

if(isset($_FILES['myfile']))
{
    //validate file for size, type, errors etc...
    $filedata = getimagesize($_FILES['myfile']['tmp_name']);
    $type = $filedata['mime'];
    $content = fopen($_FILES['myfile']['tmp_name'], 'rb'); //Required IF storing as BLOB
    $filesize = $filedata[3]; //for stuffing into an <img> tag
    $filename = $_FILES['myfile']['name'];
    $description = trim($_POST['description']);

    //check size
    //check type is allowed and get $filetype_id from $type with SELECT query 
}

Storing BLOB Data

$stmt = $pdo->prepare("INSERT INTO `filetable` (`filetype_id`, `content`, `filesize`, `description`, `filename`, `uploaded_at`, `user_id`) VALUES (? ,?, ?, ?, ?, NOW(), ?)");

$stmt->bindParam(1, $filetype_id);
$stmt->bindParam(2, $content, PDO::PARAM_LOB);
$stmt->bindParam(3, $filesize);
$stmt->bindParam(4, $description);
$stmt->bindParam(5, $filename);
$stmt->bindParam(6, $_SESSION['user_id']);
$stmt->execute();      

Storing a File to a Location

$stmt = $pdo->prepare("INSERT INTO `filetable` (`filetype_id`, `location`, `filesize`, `description`, `filename`, `uploaded_at`, `user_id`) VALUES (? ,?, ?, ?, ?, NOW(), ?)");
$location = '/uploads';

$stmt->bindParam(1, $filetype_id);
$stmt->bindParam(2, $location);
$stmt->bindParam(3, $filesize);
$stmt->bindParam(4, $description);
$stmt->bindParam(5, $filename);
$stmt->bindParam(6, $_SESSION['user_id']);
$stmt->execute();      

Retrieving BLOB Data

Let's suppose that the files we are storing are images and that we want to put them into an image tag. We have two main ways of doing this - either using a "creator page" to output the data or we can use Base64 encoding.

##== /includes/imagedisplay.php ==##

$pdo = new PDO("mysql:host=localhost;dbname=mydbname", 'root', '');

$id = $_GET['id'];
$sql = "SELECT f.content, ft.filetype_header FROM `filetable` AS f INNER JOIN `filetypes` AS ft ON f.filetype_id = ft.id WHERE f.id=? LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($id)); 
if($data = $stmt->fetch(PDO::FETCH_ASSOC))
{
    if(count($data) == 2)
    {
        header("Content-type: ".$data['filetype_header']);
        echo $data['content'];
    }
}

So in order to display image data and the image itself:

##== somepage.php ==##

$id = 234; //hardcoded for this example

$pdo = new PDO("mysql:host=localhost;dbname=mydbname", 'root', '');
$sql = "SELECT `description`, `filename` FROM `filetable` WHERE `id`=? LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($id)); 
if($data = $stmt->fetch(PDO::FETCH_ASSOC))
{
    echo "<p>{$data['filename']}</p><p>{$data['description']}</p>";
    echo "<img src='/includes/imagedisplay.php?id=$id'>";
}        

Of course this looks like a real mess just for outputting a single image. Usually, we'd stuff this code into a function to make it easier:

##== somepage.php ==##

require '/includes/functions.php';    

$id = 234; //hardcoded for this example        
$pdo = new PDO("mysql:host=localhost;dbname=mydbname", 'root', '');

echo show_image($pdo, $id);
echo show_image($pdo, 139);
echo show_image($pdo, 672);

The function in /includes/functions.php would be something like:

function show_image($db, $id)
{
    $sql = "SELECT `description`, `filename` FROM `filetable` WHERE `id`=? LIMIT 1";
    $stmt = $db->prepare($sql);
    $stmt->execute(array($id)); 
    if($data = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        return "<p>{$data['filename']}</p><p>{$data['description']}</p><img src='/includes/imagedisplay.php?id=$id'>";
    }
    return '<p>No image</p>';    
}

You could even have the second parameter as an array of file ids, so that you'd only need to call the function once and display multiple images. Of course you'd have to pull off the LIMIT clause and introduce a loop into the function.

I mentioned earlier about Base64 encoding. This method can be used to bypass the setting up of a "creator page" and may, on the surface be very much easier:

##== somepage.php ==##

$id = 234; //hardcoded for this example

$pdo = new PDO("mysql:host=localhost;dbname=mydbname", 'root', '');
$sql = "SELECT f.description, f.filename, f.content, ft.filetype_header FROM `filetable` AS f INNER JOIN `filetypes` ON f.filetype_id = ft.id WHERE f.id=? LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($id)); 
if($data = $stmt->fetch(PDO::FETCH_ASSOC))
{
    echo "<p>{$data['filename']}</p><p>{$data['description']}</p>";
    echo "<img src='data:{$data['filetype_header']};base64," . base64_encode($data['content']). "'>";
}        

Unfortunately, there are some drawbacks with this encoding method and it should only really be used for very small images. They can't really be cached in the same way as normal images.

Retrieving a File Location

##== somepage.php ==##

$id = 234; //hardcoded for this example

$pdo = new PDO("mysql:host=localhost;dbname=mydbname", 'root', '');
$sql = "SELECT `description`, `filename`, `location` FROM `filetable` WHERE `id`=? LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($id)); 
if($data = $stmt->fetch(PDO::FETCH_ASSOC))
{
    echo "<p>{$data['filename']}</p><p>{$data['description']}</p>";
    echo "<img src='{$data['location']}/{$data['filename']}' />";
}        

As you can see, very simple compared to retrieving BLOB. Again, we'd usually wrap that code into a function as we did earlier (show_image()).

In order to see what impact each method has on performance, you could perform benchmarking tests.

_______________________________________________________________________________________________

8. Splitting the Big Table: Normalization

If you're new to relational databases, perhaps the idea of relationships and normalization may be a bit alien to you. Often beginners try to stuff all their data into one Big Table.

It quickly becomes apparent that there's something wrong when you begin to add more and more fields to your table and you see loads of duplicate text in those fields.

e.g. If you have a Products table and you start adding Supplier info to it. Now we see the complete data for the supplier being repeated next to each relevant product.

In addition a product_category field may be present which stores all the possible categories for the product. Instead of having separate categories for a product, a comma separated list exists.

If you have this sort of set up, then it needs to be normalized. I will not go through the normal forms (1NF - 3NF), rather just take an overall view of this, however, please read up on these normal forms - see the references section.

Data within 'cells' should be atomic, that is, one piece of data in each field in each record. Do not try to stuff a list of values into one field, e.g. several telephone numbers for an user.

Tables should have a primary key, either simple single field or a concatenated or composite (more than one field).

Tables should not hold data that are not totally dependent on its (entire) primary key, e.g. A products table should not hold data on supplier_address and supplier_telephone. Instead it should have a supplier_id field only.
The supplier info should go in its own suppliers table.

There is a nice tutorial here on normalization: http://phlonx.com/resources/nf3/ and it gives an example of satisfying all 3 normal forms.

This section is hideously simplified - see the references section.

_______________________________________________________________________________________________

9. How to Get Data From More Than One Table

When beginning with MySQL, related tables seem a bit unintuitive and getting data from more than one table in a single query can appear to be down to a bit of black magic.
Many will attempt to use things like needless subqueries or end up with monstrosities with subqueries in WHERE clauses and dog knows what.

The correct way will depend on the data you're trying to retrieve. There are many types of JOIN available for MySQL, but the most common are: LEFT JOIN, RIGHT JOIN and INNER JOIN.

INNER JOIN

This is probably the most frequently required JOIN. This retrieves records from two but only where field values (specified join) are found in both tables.

LEFT JOIN

This is a more forgiving version of INNER JOIN. This returns all records from the first table, regardless of whether it has a related record in the second table. If a related record is not found in the second table, those fields will show NULL.

RIGHT JOIN

This is similar to LEFT JOIN, just the other way around.

OTHER JOINS

You may come across things like JOIN and CROSS JOIN - these are equivalent to INNER JOIN in MySQL's flavour of SQL.
Avoid using STRAIGHT_JOIN, NATURAL JOIN or JOIN ... USING, unless you really know what you're doing. See some examples of these:

• NATURAL JOIN
• STRAIGHT_JOIN
• JOIN ... USING

SYNTAX

The general full syntax could look something like this:

SELECT `t1`.`field3`, `t1`.`field5`, `t2`.`field2`, `t2`.`field10` 
    FROM `table1` AS `t1`
    XXXX JOIN `table2` AS `t2`
        ON `t1`.`field1` = `t2`.`field4`

where XXXX = INNER, LEFT or RIGHT

Here's a diagram that you may find useful if you're familiar with Venn diagrams. Note that this holds true most of the time, but there are cases when it does not - especially for the FULL OUTER JOINs. The following image is based on others and from bits of info from forum posts.

_______________________________________________________________________________________________

10. How to Store Menus and Submenus

A common use for databases tables is to store navigation menu (and submenu) data. There are two main approaches, the Adjacency List method and the Nested Sets method.
The Nested Sets method is very elegant but not very intuitive so I will concentrate on the easier, and more common Adjacency Listmethod.

A fantastic discussion on both methods can be found here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

The Adjacency List method makes use of a self-referencing parent_id field. Consider the following:

id | label      | uri               | parent_id | position
--- ------------ ------------------- ----------- ---------
1  | home       | /                 | 0         | 1
2  | about      | /about            | 0         | 4
3  | products   | NULL              | 0         | 2
4  | razors     | /products/razors  | 3         | 1
5  | mirrors    | /products/mirrors | 3         | 2
6  | coupons    | NULL              | 0         | 3
7  | gofast     | /coupons/gofast   | 6         | 2
8  | centraal   | /coupons/centraal | 6         | 1

Zero (0) in the parent_id field denotes a top-level menu item. Submenu levels are almost limitless, but IMO you'd have to question the design of a menu if it had more than 3 levels. position is simply the order in which the items should appear in that particular level. The following is an example on how to extract the data into usable markup:

SELECT label, uri, parent_id, position FROM menu ORDER BY parent_id, position

The PHP code:

$pdo = new PDO("mysql:host=localhost;dbname=dwtest","root","xxxxxx");

$stmt = $pdo->prepare("SELECT `parent_id`, `id`, `label`, `uri`, `position` FROM `menus` ORDER BY `parent_id`,`position`");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);

function printList($rows, $parentId = 0) {
    $menu = '<ul>';
    foreach ($rows[$parentId] as $row) {
        $menu .= '<li>';
        $currentParent = $row['id'];
        if (!empty($rows[$currentParent]))
        {
            $menu .= "<span class='drop'>{$row['label']}</span>";
            $menu .= printList($rows, $currentParent);
        }else{
            $menu .= "<a href='{$row['uri']}'>{$row['label']}</a>";
        }
        $menu .= '</li>';
    }
    $menu .= '</ul>';
    return $menu;
}

//where relevant
echo printList($rows);

The code above may need some tinkering - especially the $menu .= "<span class='drop'>{$row['label']}</span>"; line.
This is where you decide what the item that has a submenu is like - should it be a link, or just text? That will depend on the type of dropdown menu script or styling) that you use.

_______________________________________________________________________________________________

11. Storing and Retrieving Authentication Data

There are thousands upon thousands of login and registration scripts out there, but most are seriously out of date and pose security issues - the exact opposite of what they're supposed to prevent!

The following is a quick tour of the relatively new password_hash() and password_verify() functions that significantly simplify creating DIY scripts.

In order to use them as described in the manual or "out of the box", you need to set a password field in your DB to at least varchar (60). Currently the default hashing algorithm is BCRYPT. Changing the hashing algorithm will may mean changing the varchar length. So it may be worth future-proofing for this eventuality by setting the varchar length to 255.

Storing a password, e.g. from a registration form

Modified from the manual:

//obviously you'd validate the password first before going on to this step
$password = password_hash($_POST['password'], PASSWORD_BCRYPT, ['cost'=>12]);

The above takes a password from a form and hashes it to a 60 character string. The 'cost' value is a sort of function of how much time it should take to hash. You can then store the $password via prepared statement in the usual way:

INSERT INTO `users` SET `username` = ?, `password` = ?, `email` = ?

Verifying a password, e.g. for login

$stmt = $pdo->prepare("SELECT `id`,`username`,`password`,`userlevel` FROM `users` WHERE `username` = ? OR `email` = ? LIMIT 1");
$stmt->execute([$_POST['username'],$_POST['username']]);
if($row = $stmt->fetch(PDO::FETCH_ASSOC)
{
    $hash = $row['password']; //from DB
    $password = $_POST['password']; //from login form

    if(password_verify($password, $hash))
    {
        //Yipee! Pass GO and collect £200...
    }else{
        //BAD password! Go directly to jail...
    }
}else{
    //BAD username/email
}

The script above attempts to check a 'username' field in a login against an actual username or an email. It's also generally a good idea not to give the user too much feedback on failed login attempts. That is, do not give info about whether it was 'username' or the password (or both!) that caused the failure.

_______________________________________________________________________________________________

References and Further Reading

Using PDO

1. http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers
2. http://php.net/manual/en/book.pdo.php
3. http://www.phpro.org/tutorials/Introduction-to-PHP-PDO.html
4. http://php.net/manual/en/pdo.error-handling.php
5. http://www.dreamincode.net/forums/topic/214733-introduction-to-pdo/

Using MySQLi

1. http://codular.com/php-mysqli
2. http://php.net/manual/en/book.mysqli.php

SQL Injection

1. http://www.w3schools.com/sql/sql_injection.asp
2. https://en.wikipedia.org/wiki/SQL_injection
3. https://www.owasp.org/index.php/SQL_Injection
4. http://www.veracode.co.uk/security/sql-injection
5. http://www.w3schools.com/php/php_mysql_prepared_statements.asp

Aggregate Functions

1. https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html
2. http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-in-mysql.php
3. https://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/
4. http://www.techotopia.com/index.php/MySQL_Data_Aggregation_Functions
5. http://www.mysqltutorial.org/mysql-aggregate-functions.aspx

Pagination

1. http://www.phpro.org/tutorials/Pagination-with-PHP-and-PDO.html
2. http://www.stepblogging.com/pagination-using-php-pdo-and-jquery/
3. https://en.wikipedia.org/wiki/Pagination

Storing and Retrieving BLOBs

1. http://stackoverflow.com/questions/13435187/what-is-difference-between-storing-data-in-a-blob-vs-storing-a-pointer-to-a-fi
2. http://www.mysqltutorial.org/php-mysql-blob/
3. http://www.stoimen.com/blog/2009/04/23/when-you-should-use-base64-for-images/
4. http://davidwalsh.name/data-uri-php
5. https://www.sitepoint.com/premium/books/php-mysql-novice-to-ninja-5th-edition/preview/recording-uploaded-files-in-the-database-6d9f71e

Uploading Files

1. http://php.net/manual/en/features.file-upload.php
2. http://www.w3schools.com/php/php_file_upload.asp

Normalizing Tables

1. http://searchbusinessintelligence.techtarget.in/tutorial/Database-normalization-in-MySQL-Four-quick-and-easy-steps
2. http://www.devshed.com/c/a/mysql/an-introduction-to-database-normalization/

JOINs

1. https://dev.mysql.com/doc/refman/5.6/en/join.html
2. http://www.sitepoint.com/understanding-sql-joins-mysql-database/
3. http://www.techonthenet.com/mysql/joins.php
4. http://www.phpknowhow.com/mysql/joins/

Storing Hierarchical Data

1. http://www.sitepoint.com/hierarchical-data-database/
2. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
3. https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/

Authentication

1. http://php.net/manual/en/function.password-hash.php
2. http://jeremykendall.net/2014/01/04/php-password-hashing-a-dead-simple-implementation/
3. https://paragonie.com/blog/2015/04/secure-authentication-php-with-long-term-persistence
4. https://www.owasp.org/index.php/Top_10_2014-I2_Insufficient_Authentication/Authorization
5. https://blog.engineyard.com/collections/password-security/