Far more fun than bash

If your MySQL database is running on a remote server then it can be quite a job to load a binary file (such as a jpg, movie or zip file) directly into the database when you have no file access to that server.

This bash script gets round the problem by converting the file to HEX and uploading it to the database:

#!/bin/bash
#Uploads a file (parm 2) to a database with id=parm 1

#INITIALISATION=====
#the id in the SQL table to update
ID=$1

#filename of file to upload
SOURCE=$2

#Some internal initialisation
TMPFILE="$SOURCE.hex"
SQL="/PATH_to_MYSQL/mysql -uUSERNAME -pPASSWORD DATABASENAME -BNe"

# PROCESS===========
#Let the user know whats going on
echo "Uploading '${SOURCE}' to database with 'id=${ID}'"

#Convert the file to a hexed version
/usr/bin/hexdump -ve '1/1 "%02X"' ${HEXPARMS} ${SOURCE} > ${TMPFILE}

#Upload into the database, unhexing as we go
$SQL "update files set data=unhex('`cat ${TMPFILE}`') where id='$1'"

# CLEANUP============
#Tidy up
rm -f ${TMPFILE}

echo "Upload completed."

The moment the left join becomes clear

Part 1 | Part 2 | Part 3

Mastering the LEFT join

The left join is very powerful, very useful and very under used. In my experience few people have even heard of the left join, let alone used one. The left join comes in handy when you are trying to join tables together where you have unreliable joins or where you are managing subscriptions between two objects. A similar result to the left join can be achieved programatically using nested queries. I’ve seen many a PHP script that uses nested loops of SQL queries to accomplish a similar result. However performing these joins programtically is of course far slower than using a database that has been specially designed for the purpose. Using a query is simpler too, once you know how it works.

As with the standard join, its important to know when a left join is applicable. There ae many situations where the use of a left join is applicable, but i’ll just explain the simple, most obvious and most common ones I come across almost daily.

Unreliable Joins

This is a term I have made up. Please feel free to suggest a more technically correct word but this does it for me. An unreliable join is where we have a relationship between two objects but the relationship is optional – it does not always exist. Its ‘unreliable’.

more…

She really can fly you know

Part 1 | Part 2 | Part 3

Mastering the table join

In this tutorial I try to explain one of the most simplest and useful mechanisms available to you in a relational SQL database: the standard Join (also called Inner Join).

Querying the Tables

To connect these tables together we need to do a query. More specifically a query with a ‘join’ between the tables. The ‘join’ describes how we wish to relate the tables together.

As a simple example, consider you wanted to list all of the babes from the club. You could simply do this with the following query:

select name from surfer where gender_id=1;

This does the job, but it relies on us knowing the unique id of the gender ‘babe’ (which is ‘1′). A join allows us to accomplish this this using the gender table as a lookup.

select s.name, g.gender from surfer as s join gender as g where s.gender_id=g.id and g.gender="Babe";

Returns:

+--------+--------+
| name   | gender |
+--------+--------+
| Sheila | Babe   |
| Kylie  | Babe   |
| Danni  | Babe   |
+--------+--------+

This SQL statment introduces a number of syntax elements. The tables gender and surfer are aliased. e.g. “… surfer as b…”. This means we can reference the fields in the table surfer by simply using the letter ‘b’. You may use the table name but this can get confusing later on eg. “b.name” == “surfer.name”.

n.b. Its good practice, especially with large queries to use table aliasing. Although MySQL allows you to omit the table/alias name, it will bitch if you reference a field that appears in both tables as it won’t know which one you want.

more…

This is Sheila - shes fit as well as mental

Part 1 | Part 2 | Part 3

Building a relational database

Databases are extremely powerful data storage mechanisms, I use them everyday. A lot of people have extremely limited experience with relational databases, using them as they would an Excel document. This sequnce of posts aims to demonstrate how easy they are to use and how best to go about converting an everyday scenario into a useful database that can support your web site or application.

Part 1: Creating a database from a scenario

I believe it is always better to first PLAN what it is you are trying to achieve, so first we’ll construct a reason to have a database in the first place. [If you know how to normalise data then jump to here and skip the scenario]

The Scenario

Okay, we are going to model our Surf Shack Member’s Club, storing details about the members within the club. We’re gonna convert this into a normalised set of data tables and then use those tables in a few queries.

This is the data (a list of surfers, their gender and their skill level), as it might be presented scribbled onto a piece of paper:

BABES

Sheila – Mental

Kylie  - Spectator

Danni  - Advanced

DUDES

Hank   – Spectator

Brad   – Beginner

Joey   – Mental

Tom    - Professional

Jason  - Professional

more…