Explore the Premium Content

Beginner's SQL: Aggregates and Grouping

When you create a database-driven Web site, you will encounter a number of common data retrieval scenarios. Simple lists, header/detail listings, row difference calculations, and reports with subtotals can all be satisfied using fairly basic SELECT queries. But when you move on to functions that aggregate data - to find sums, averages, counts and the like, there's a few gotchas that can baffle the newbie and trip up even the experienced developer.

Wave goodbye to Aggregate and groupings gotcha misery with Rudy's easy-to-follow tutorial on some of SQL's powerful aggregating functions!

Read More

SQLite: new PHP-bundled database

SQLite  is an SQL database engine that will be supported in PHP5 and which can be embedded directly into your applications. Because the engine works with a single data file, this means that you can use full SQL functionality without having to install an SQL server. If you've ever despaired at not being able to include database functionality in your PHP pages because the machine you're using as a web server doesn't have MySQL installed, or if you ever need the features of the SQL language without having to connect to database servers, then this article is for you.

Since most PHP people are familiar with MySQL, we'll show you how it differs from that database. We'll then take a look at how to enable SQLite in the new PHP 5, and with a little bit more effort, PHP 4.3.x. With that out of the way we'll get down to the important stuff of using SQLite.

Read More

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

SQL Server Stored Procedures: the Whys and the Hows

In this tutorial, for intermediate ASP/ SQL developers, Rob looks at Stored Procedures, free utilities for developing them, how they can help prevent SQL Injection attacks by seperating ASP and SQL code and speed up processing by allowing pre-compiled code on the SQL Server. Read More

Database performance tuning and optimisation

Welcome to an introduction on database performance tuning and optimisation. My previous tutorial, SQL Made Simple, lookedata normalised database, and explored some of the queries we would use to make information from data in its normalised state. Today, we’re going to look at ways to shorten query processing time on this same database.

Optimising databases is a balance between normalised data and optimised queries. We  look at two ways to find this balance; de-normalisation, and the use of indexes. We'll de-normalise our design just a little to remove unnecessary table joins. We also make use of a new status code value to eliminate a query entirely. We look at indexes and the performance gains they give us, and consider their use in the context of search functionality. Read More

Introduction to Database Design - Normalisation

In this tutorial, for beginners to database design, Rob Stuttaford writes on how to design databases that are normalised - the process of breaking data down into its most basic components. We do this to remove redundancy in data, and for a couple of reasons. Data is more flexible when in a granular format. Instead of using one big table, like with any spreadsheet program, we make distinctions between bits of data and then relate these bits to create meaningful information.

This means that you don't need to store the same data twice (or three times, or four times..) so your databases are smaller; there's less risk of incorrect or contradictory data breaking your apps or confusing reports and queries.

Many beginners don't spend long enough designing their databases before populating them with data, often because received wisdom is that database design is a discipline second only to Quantum Mechanics in complexity. It isn't, as Rob's self-contained normalisation tutorial shows.

Read More

Database design and optimisation made easy

The tutorial series will look at the different methods of retrieving data, always aware that this is the Web and that speed is of the essence, how to monitor performance of your database and tune it for maximum speed. In enterprise-level sites, these tasks are often performed by highly trained, specialist DBAs (database administrators); on smaller sites, it's often the developer who needs to wear this extra hat. With his four years' experience, Rob explains complex concepts and procedures, simply and clearly, to give today's Dreamweaver Professional a heads up in building and maintaining fast, secure data-driven sites.

Often, database driven websites are built without much thought given to the big picture. Also, web sites are often built organically, and databases can become unwieldy very quickly, if you're not careful. Spend a little bit more time with the database design up front, and save yourself hours of work and trouble later!

This first tutorial of a new linked series covers the basics of common database and it's benefits, and will clearly describe the different types of relationship you can use to store your data. It also gives some practical advice for some common scenarios.

Read More

Tips for Building A Project

Gareth is a member of Team Macromedia and is always on hand on the Macromedia support forums offering help and advice to Dreamweaver MX users. In this week's tutorial he has collected some of the frequently asked questions and collated them into a tutorial dedicated to making your workflow just that little bit faster  - and your life just that little bit easier!   Included within the tutorial are the following sections:

• Cloaking – Hiding your Private Files
• Backing up Your Site Definition
• Tips for planning a MySQL Database

Read More

Using Stored Procedures and Commands in DWMX

This tutorial builds up a click-through tracking application that you can use to measure the number of clicks on any given link in your site, even external links. It demonstrates how to use SQL Server stored procedures and commands from Dreamweaver, in conjunction with an interactive demo. All the application code is free for download and re-use in your own pages.

This tutorial is suitable for beginner to intermediate level, and requires SQL Server installed on your machine (free evaluation edition available from www.microsoft.com/sql/evaluation/trial/) and/or your webhosting provider.

Now with interactive Flash movies showing you step by step how to create the application in DreamweaverMX!

Read More