In this post I explain about how to create a simple, database-driven bookmarking page with a filter system. The short tutorial is primarily based on this system I developed to code and save graphics. The page started out extremely simple: just displaying some images and titles on a page. It is now slowly evolving into a more sophisticated system, it currently allows users to filter on a number of aspects.
Here, I will cover the basics of the system plus explain how to setup a database, and create a form that inserts data into the database. The next post explains how to retrieve data from the database, display it on a page and filter on pre-defined aspects. The intended audience is for people interested in web-technologies but without advanced coding skills.
Disclaimer: Code can be smart, elegant, and even funny. If you are looking for that kind of code, you will have to go somewhere else. The code described here is not optimal, but it works and it can be used as a starting point for better, intelligent system.
If you want to make use of a bookmarking system, there are other, maybe more suitable, options such as Delicious with tagging and Pinterest with the use of boards. However, the great thing with a custom-made system is that you are in control: you decide on things such as design, functionality, what you want to show and hide, having it on- or offline and interactive behavior. This can be a big plus compared to making use of third-party systems. And, it can be an interesting project to learn more about web technologies and databases.
The graphics page consists out a form that inserts data in a database, and a page that displays information from the database. Thus things happen on the server side (back-end) and on the client side (front-end). If you are non-technical, think of front-end and back-end as the human body. Front-end is everything you will see which includes behavior, such as skin, clothes, sparkling eyes or the nervously twisting of a hand. The back-end is invisible, but there you’ve got all the organs who work hard to make the magic outside happen.
The first thing to do is describe the purpose of your bookmarking system. Is this as a simple page to collect links to interesting articles, a place to keep all your adorable gifs of swimming dolphins or do you want to use this for research purposes? It is important to think about what you want to achieve with a page like this.
The next step is creating a system to categorize your items. With the graphics page, I initially coded every graphic on more than 23 aspects. You can go as wild or as simple as you want. My advice is to use the KISS principle (read: Keep It Simple, Stupid). If you have to fill in a gigantic form for each item you want to bookmark, it becomes a significant task. After a while, you probably stop using it. I would recommend: start as simple and straight-forward as you can.
For the purposes of this tutorial I will build a new bookmarking system: a system based on the taxonomy of ‘rare visualization type’ proposed by Jeffrey Heer, Michael Bostock and Vadim Ogievetsky in the article ”A Tour through the Visualization Zoo” (2010). I am going to build a page where you will see thumbnails of visualizations with dates, titles and sources. In the navigation bar you have the option to search on title, and allows you to filter on visualizations types and on the ‘exoticness’ of a visualization.
The steps are described in the following order: requirements, setting up the database, and creating a form that inserts data in the database.
With your program of choice, I make use of either Smultron or Dashcode, but it does not really matter. You can also work in a text edit program. For the cropping of images it is nice to have a graphics editing program such as Photoshop or the open source program Gimp. Further, everything is developed with the use of MAMP, Everything will be developed with the use of MAMP, a mac program that enables you to run server-side scripts locally. You can download a free version of the program here. There are also PC programs, probably working very similar but I have not experience with that.
Setting up the Database
The first step is to create a database. Create the folder ‘visualform’ into the root folder of MAMP (check preferences > Apache). In this folder we will put all documents related to the project.
Open the start page and click on the button ‘php MyAdmin.’ Create a new database naming it ‘visualform’ and import the following code in the SQL tab.
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; CREATE TABLE `visualtable` ( `id` int(4) NOT NULL AUTO_INCREMENT, `timestamp` int(8) NOT NULL, `title` varchar(128) DEFAULT NULL, `credits` text NOT NULL, `date` int(4) NOT NULL DEFAULT '0', `url` varchar(126) DEFAULT NULL, `thumbnail` varchar(250) DEFAULT NULL, `comments` text NOT NULL, `exoticness` int(1) NOT NULL DEFAULT '0', `visualform` text, `visualformsub` varchar(250) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `title` (`title`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
This may look like gibberish, but it is actually not that bad. For instance, take this line:
`id` int(4) NOT NULL AUTO_INCREMENT,
In this line, a column named ‘id’ is created. This item is assigned an int, which means positive integer. With a maximum length of 4. It can not be empty and will automatically increment with each new item.
`title` varchar(128) DEFAULT NULL,
This line tellls that the table should create a column named title, with a maximum of 128 characters. The default null tells that it initially set as empty. You can make tables in many ways, and tweak things for clarity, performance etc. I haven’t delved deep in this topic. You can follow a tutorial about sql tables here.
Next we create In the home folder the file login.php which will be used to get permission to access the database and table. It says the following:
<?php // login.php $db_hostname = 'localhost'; $db_database = 'visualform'; $db_username = 'root'; $db_password = 'root'; ?>
Great, now we create a form, with fields and buttons. This is used to fill required information in the database. Thus we create a php page named form.php that has all html elements + inserts the information in the database. Due to technical errors I was not able to post the source code in the post. But you can download below all the required files here (the database files are not included).
Next we create the css file form.css and put this in the css folder. CSS is short for Cascading Style Sheets, and is used for the looks of (web) pages. I kept it simple, but you can make it as nice as you want.
Cool, fill in the form and check in your database if everything works. If it does, you will see something like this:
Sometimes, nothing will happen. This means something is wrong. Be sure everything is checked, nothing is closed. Also check that the data is inserted in the exact order as your database (in this example: id, title, credits etc etc). I did not make use of displaying the errors (which help with bug fixing) but a more sophisticated system is able to do this. With programming, especially when you do something for the first time, there is a lot of trial-error.
The only way to make thins work is to constantly keep testing and saving files – preferably in other locations - when it works. The moment it breaks down, go back step for step to look where it went wrong. For this, you need patience and determination. Or a programmer who can do it for you.
Well, lets assume it did work.
We now have a database with a table, and a form that inserts data in the table. The next step is retrieving the data from the database and creating a page that displays data, plus add a search bar and a filter system, as can be seen on the graphics page. I will describe these steps in part 2, which will be posted in the next couple days.
You can download here the files created (again, without the database and table).