Advanced User-Customised Content

In the last article we took a look at how we could make a very simple system to display content in categories that the user is interested in. By default, the user sees everything, but can amend  their preferences to select categories that (s)he does not wish to see.

The only drawback to this system was that we could only classify an article under a single category or topic, thereby having to create over-generalised topics so that we could accurately classify the articles.  So instead of having topics for Linux, Windows and Macintosh OS X, we had to have a catch-all category of Operating Systems, in case an article covered more than one type of operating system.

In today's article we'll alter the structure of our database and change the admin pages so that we can give an article more than one topic.  We'll then take a look at how this affects the code that we wrote to display the articles on the main page.

Read More

Beginner's SQL: Loop The Output

In this second article in the new Beginner's SQL series, a common performance problem is solved. When a database-driven Web page runs a query to retrieve some data, and then uses a value from each row in the result to run an additional query, this "query in a loop" requires excessive and unnecessary database overhead. More importantly, the total time to complete the database retrievals, and thus the resulting Web page download time, balloons. In this article, four examples are shown which "loop the output" and not the query: simple lists, header/detail listings, row differences, and subtotals. Read More

Building content based on user profiles

If you’re building a web site that provides different kinds of dynamic content to the users of your web site, a nice feature is to let your users decide what types of content they want to see. A good example of this is a site like slashdot.org – every day they will post links to articles that cover a wide variety of subjects, and in the preferences you can decide what kinds of articles you want to see.

The way that their system works is an opt-out solution. By default you will get all articles of every topic displayed on the main page. You can then go into a preferences page and there select article topics that you are not interested in.

 

We build up such a system today; it's simple to follow and the code is provided.

Read More

Beginner's SQL: Cross Joins

A BAZILLION ROWS OF DATA! Has this ever happened to you? You have a working SQL query to retrieve data from a database table. Now you want to add a second table into the query, for related or more detailed data, and all of a sudden you have a bazillion pages of output. You have discovered the dreaded cross join effect. Despair not.

Whenever there are two tables in an SQL query, they will be joined. This means the rows of one table are combined with the rows of the other table. You must always pay attention to how you want them to be joined. With a bazillion rows, maybe you don't want a cross join. Other times, a cross join is exactly what you want. So let's look at this cross join effect more closely.

 

Read More