Jotlab

Now we’re about to get our database table set up. But before I show you the SQL code to run against your database, let me first explain how the logic of the site is going to play out:

1. First a user arrives on our awesome webpage
2. They next enter a website url into the url input field, they submit the form
3. Our system first checks if the url exists within the system
4. If the url exists, then the already shortened code is displayed back to the user on the following page
5. If the url doesn’t exist, then a random set of 6 characters is generated to be its shortened url code. Also something else happens, the original url is stored, so as to redirect to the correct page, but the url is also md5 encoded, to represent a unique identifier for that url. We’ve indexed this unique identifier column, to reduce the stress on our cringing database.
6. The user gets redirected to the url if the shortened code is navigated to.

So what does this mean? Well, we need some columns! A place to store the real URL, a md5 of the url and a randomly generated unique string for the url. Below is the SQL we’ll be using to get our url table sorted:

1
2
3
4
5
6
7
8
9
10

CREATE TABLE IF NOT EXISTS `urls` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` text NOT NULL,
  `shortcode` varchar(16) NOT NULL,
  `unique` varchar(32) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `unique` (`unique`)
);

Get this table sorted, either use the mysql shell or phpmyadmin. Why are we md5-ing the url you might be wondering? Well we don’t want to insert the same url more than once, why should we? It’s the same url, it’d just be taking up a unique code and space on the poor database servers hard drive. We md5 to the url to create a unique reference to the url, because searching through on the field of type text on what could easily be a massive database is very costly to performance! So why don’t I just make the field a varchar of 255? as you might normally. Well URL’s can be quite big, in-fact apache and alot of browsers will go up to a URL length of 4000 characters. Using a varchar field of 4000 is extremely inefficient!

Anyhow, now I hope you understand what’s going on behind the scenes here, let’s get going with making things happen! With the database table set up, go back and refresh your page. Didn’t change anything right? That’s because we did a naughty, and we didn’t follow the conventions of CakePHP! Rebels! The convention should of been to have a table set up called shortens. Or a table called urls and a controller called url_controller.php. Because I like neither of these options, i’ve decided to go rebel.

But it’s all good, we just need to create a model, and to tell it to use the urls table! Models are used for representing table associations, data validation and data handling logic. They are the data droid files if you will. In fact a website and its operations can be regarded as so data centric, that is it probably best to develop functions within the models and called within the controllers, as a way to modularise and reuse code! So this is where fat-assed models and skinny-weeny controllers comes into play.

Let’s make a fat-assed model now. Create a file within the models directory called shorten.php and let’s tell the model which table in the database we wish to associate it with. The key to it is on line 5:

1
2
3
4
5
6
7
8

<?php
	class Shorten extends AppModel {
 
    var $name = 'Shorten';
    var $useTable = 'urls';
 
	}
?>

Awesome! Go and refresh the page again. Argh! Another error! All good! We’re still setting up! This time its telling us that we don’t have a view set up for this action. Which is true, we don’t but as soon as we do, you won’t be seeing anymore errors, only what your final front page should look like. Promised. Before we flip the page, notice anything different in the debugging? You see it’s communicating with our database:

Postscript: Leave A Comment // Subscribe (RSS Feed)