Quickshot - Using Subqueries and Count to Display Membership Counts Per Region
Reader Level:

In this quick shot, I'll show you how to take a list of locations and provide a count of members per location.

Getting Information Out of Your mySQL Database
Reader Level:

You've just landed a new client, you've got a lot of work to do and they have over their FTP information - and wish you well. You ask for their control panel or phpmyadmin login and they look at you funny - like you're speaking in unknown language to them. You need their existing database information, what are you to do?

Adding Columns to Large Database Tables
Reader Level:

When creating tables for an applications database, most people try to arrange the fields in an order that would make sense to a user reviewing the data right out of the table. The fact of the matter is that a basic principle of relational database design is that physical data storage is unimportant. It does not matter, as a practical point, what order the fields appear in the table definition and it does not matter what order the records are stored in. Properly written SQL queries that avoid the use of the * character to retrieve all fields and that define the order in which you want to retrieve records make it meaningless what physical construction the database uses to store the data.

Formatting Dates in SQL Server
Reader Level:

Whether because of a national standard or a particular file format, a common issue in SQL is how to retrieve a date value in a certain date format. As we have discussed before, SQL Server in particular stores dates as a number of seconds (and ractions of a second) since the epoch. So, in reality, dates almost always come out of SQL Server formatted differently than they are stored. If you want something other than the standard 2009-08-02 16:10:11.620 (YYYY-MM-DD hh:mm:ss.mmm) have to tell SQL Server in your query how you want the date formatted. Since SQL Server does not really have a date format function (like you might find in ColdFusion) you will need to use the convert function to accomplish this.

Checking Against Data In Multiple Columns
Reader Level:

I am amazed every day by the complicated ways that people need to look at the data in their databases. On a fairly regular basis, I am asked to run queries and reports in ways that were never anticipated when the database was designed or that the original designer did not take into consideration. It often takes some creative work to get to the desired goal.

Using Table Aliases
Reader Level:

When writing SQL statements, using the table names in the code is often necesary to avoid confusion in the database server. For example, if you have a Customers table with a customerid field, and an Orders table with a customerid field, if you are joining both tables, you will need to use the table name on the customerid field in order to differentiate between the two tables. This can turn your queries into much longer, hard to read statements -- especially if you are using proper naming conventions and your table names are long and descriptive.

Using a table alias in the code is a beneficial way to increase readability and performance. Readers of this article should understand basic SQL coding, including joins. It will apply to most databases (SQL Server, MySQL, Access, etc).

Quick Shot: SQL Zip Code Radius Search
Reader Level:

Do you need to do a United States Zip Code radius search? Want to do the search by the zip code or by the city? This tutorial will show you the SQL to get it done!

Calculating Percentile Rankings
Reader Level:

Percentile rank is a use for statistical measure for comparative data analysis. You may recall the use of percentile from school in which your grade average resulted in your percentile rank amongst your class. If your grade average placed you in the 87th percentile, it meant that your grades were better than 87% of your classmates.

Percentile has some interesting business usages and can be used to indicate popularity as well as success. For example, it can be used to indicate the popularity of a product in its category or someone's achievement within a group. I was recently called to create percentile rankings from athletic evaluation data to better indicate an athlete's overall skill level among a myriad of tests. I used PHP and MySQL to calculate and display the percentiles, but you can use any scripting language and database.

A more common method of indicating rank is to use a natural set of ordinals (1, 2, 3, ...) This method is popular and gives instant recognition of high achievement, but loses perspective once you leave the highest end of the scale. For example, I was just shopping for digital video cassettes on amazon.com and reviewed a product which had a 5-star customer rating. I noticed on that page that the product had an Amazon.com Sales Rank of #59 in the category of blank media.

So how good is #59 in the category? The answer depends on how many other products are in the category. If there are hundreds in the category, then 59 is good, right? I clicked on the link to show the category and the listings only go through 100. If there are only 100 items in the category then 59 is a slightly less than average performer.

The benefit of percentile ranking over ordinal ranking is that you don't have to know how many items are being compared to understand its relative position among the set. With percentile rank you always know where you stand.

Copy a Record in PHP Using Dreamweaver
Reader Level:

A quick and easy way to copy a record, edit the record contents and insert it as a new record in to your database. By using Dreamweaver server behaviors, this process is a snap!

Save Time with MySQL Temporary Tables
Reader Level:

What happens when you try to work with data from multiple sources? How do you sort through the data and avoid duplicate records? A manual approach will be time consuming and resource intensive. A faster and easier solution is to let MySQL do the work for us.

SQL JOINS: The RIGHT JOIN
Reader Level:

In this tutorial we will learn how to utilise the SQL RIGHT JOIN to gather information from two different tables.

The SQL Joins Series:
SQL Joins: The INNER JOIN
SQL Joins: The LEFT JOIN
SQL Joins: The RIGHT JOIN

SQL Joins: The LEFT JOIN
Reader Level:

In this tutorial we will learn how to utilise the SQL LEFT JOIN to gather information from two different tables.

The SQL Joins Series:
SQL Joins: The INNER JOIN
SQL Joins: The LEFT JOIN
SQL Joins: The RIGHT JOIN

Backing Up Your SQL Server Databases
Reader Level:

When you are working with dynamic websites their can be little more important than backing up your data, the data within each database may represent years of data that would be extremely difficult to replace if lost. In this article we will look at performing backup and restore operations through Microsoft's Query Analyzer and make provision to store our backup files away from the production server.

CMX PHP Download Counter
Reader Level:

So, your site offers it's users downloadable files of some sort, but you have no way of knowing how many files are being downloaded? Well CMX has has a solution for you.

The CMX PHP Download Counter consists of two tiny PHP functions that use MySQL tables to generate lists of file downloads and count the total file downloads each time someone clicks on the link.

Searching for Data Using Wildcards in a Stored Procedure
Reader Level:

This tutorial demonstrates how to create an ASP.NET page that searches for data in specific columns of a table. The technique described has the added advantage that the user can enter just a fragment corresponding to the data that resides in the table. You'll learn how to use the LIKE keyword and % wildcard in a stored procedure. And you'll see how the use of three Dreamweaver server behaviors nearly eliminates the need for you to write any code yourself!

Understanding SQL - Part 5: The SELECT Statement - Utilising the IN & BETWEEN Operators
Reader Level:

In part five of this series we will further explore the SELECT statement and the WHERE clause, and we will be paying particular attention to the the IN and BETWEEN operators.

We will also take our first look at the DISTINCT keyword and we'll see how it can be used to filter the records our queries return and how it can be used in conjunction with the COUNT() function.

The Understanding SQL Series
Understanding SQL - Part 1: The SELECT Statement
Understanding SQL - Part 2: The SELECT Statement - Comparison Operators
Understanding SQL - Part 3: The SELECT Statement - Logical Operators & Clauses
Understanding SQL - Part 4: The SELECT Statement - Evaluation Ordering
Understanding SQL - Part 5: The SELECT Statement - Utilizing the IN & BETWEEN Operators
Understanding SQL - Part 6: The SELECT Statement - The LIKE Operator and Wildcards

Understanding SQL - Part 4: The Select Statement - Evaluation Ordering
Reader Level:

In this tutorial we will see how accepting the default evaluation of SQL operators can lead to unexpected results being returned to the client. We will see how the default evaluation order of logical operators can be overriden using parentheses and nested parentheses.

The Understanding SQL Series
Understanding SQL - Part 1: The SELECT Statement
Understanding SQL - Part 2: The SELECT Statement - Comparison Operators
Understanding SQL - Part 3: The SELECT Statement - Logical Operators & Clauses
Understanding SQL - Part 4: The SELECT Statement - Evaluation Ordering
Understanding SQL - Part 5: The SELECT Statement - Utilizing the IN & BETWEEN Operators
Understanding SQL - Part 6: The SELECT Statement - The LIKE Operator and Wildcards

Creating a Search Engine with iSearch
Reader Level:

If you have always wanted to add a search engine to your site, and didn't know how to program one yourself, you might want to download and install iSearch, a ready-made PHP and MySQL driven application that performs searches right on your own server. This tutorial will take you through the installation and the customization (of the style sheet and header and footer includes) of iSearch.

Understanding SQL - Part 3: The Select Statement - Logical Operators & Clauses
Reader Level:

In this tutorial we will continue building on the knowledge we have gained on the SELECT in Parts 1 & 2.

We will be looking at how we can use the TOP, WITH TIES and PERCENT clauses and we will also look at logical operators and how we can use the SQL syntax to return the same data using different types of queries.

The Understanding SQL Series
Understanding SQL - Part 1: The SELECT Statement
Understanding SQL - Part 2: The SELECT Statement - Comparison Operators
Understanding SQL - Part 3: The SELECT Statement - Logical Operators & Clauses
Understanding SQL - Part 4: The SELECT Statement - Evaluation Ordering
Understanding SQL - Part 5: The SELECT Statement - Utilizing the IN & BETWEEN Operators
Understanding SQL - Part 6: The SELECT Statement - The LIKE Operator and Wildcards

Understanding SQL - Part 2: The Select Statement & Comparison Operators
Reader Level:

In part 2 of this series we will be working with the Northwind database and Microsoft's Query Analyser.

We will be looking further into select statements and the use of comparison operators. We will also touch on logical operators and see the count function at work. We will look at how we can optimise our SQL statements to get better performance and explore when and when not to use the * wildcard.

The Understanding SQL Series
Understanding SQL - Part 1: The SELECT Statement
Understanding SQL - Part 2: The SELECT Statement - Comparison Operators
Understanding SQL - Part 3: The SELECT Statement - Logical Operators & Clauses
Understanding SQL - Part 4: The SELECT Statement - Evaluation Ordering
Understanding SQL - Part 5: The SELECT Statement - Utilizing the IN & BETWEEN Operators
Understanding SQL - Part 6: The SELECT Statement - The LIKE Operator and Wildcards

MySQL Command Line Tips
Reader Level:

MySQL is all the rage these days, and with good reason: its price/power ratio can't be matched. And there are a lot of well-engineered GUI interfaces to make MySQL administration easy. But the fact remains that MySQL's native interface is the command line, and sometimes the command line is the only way to go.

This article walks you through basic MySQL command-line options on diverse operating systems, and shows you how to get up close and personal with MySQL on your local machine and your remote server.

Using CSV Strings in SQL
Reader Level:

Using SQL Server stored procedures, you can parse and insert individual items from a comma-separated list in the form of a string. This has great benefit in that you can avoid complex server-side scripting of batch inserts. This is useful for multiple categories for one product, or any other type of one-to-many relationship in a database table.

Working with Microsoft's Query Analyser
Reader Level:

In this tutorial, we will take a look at Microsoft's Query Analyser and Enterprise Manager. We will use the Query Analyzer to create a database, and then to create and relate tables. We will also look at the different start-up options we can use with our instance of SQL Server. This tutorial contains video demos.

Comparison Shopping, using the IN statement in SQL
Reader Level:

Your client has seen it when looking for their next computer, or next car depending on what has broken down most recently. You know those types of pages where you are presented with a number of options and you want to narrow down their search a little more, so you check the boxes next to the items you're interested in and then off to the next page listing only those items as perhaps a little more information on each item. And now your client wants this same functionality on their site. After you work through this tutorial, you'll be able to tell them it'll take about 5 minutes. That's right. In Dreamweaver you can have both pages described above working in 5 minuets or less (of course, not counting the 40+ things the clients wants to change about that shade of blue you chose for the menu background while you're at it). Read on…

Upsizing from Access to SQL Server
Reader Level:

As your web site grows and your traffic increases you will need to address the scalability of your application to keep up. Moving your database from Access to SQL Server offers significant increases in performance, security, and reliability. Learn the tricks to a seamless upgrade.

Check for duplicate database entries
Reader Level:

You may have a database in which you have duplicate entries in a particular column. This may be by design, or possibly by accident. If you've inherited the site from someone else, you may not be aware of this duplicate data. Or perhaps you are aware, but you'd like to see what or where this data is. This tutorial will allow you to build a page that lists only duplicate entries from a column in your database.