Limiting Results from MySQL SELECT Queries
Reader Level:

Sometimes, when crafting MySQL select queries, we get so caught up in constructing the selection that we overlook the issue of specifying how much data we need to select. For example, you may need only 10 records to show in one page of search results. Or, you might want just one record for displaying a random quote. Selections of the entire table might also result in multiple instances of the same value when you want just one copy of each.

This article will discuss two options for MySQL selections, LIMIT and DISTINCT, which will give you more control over the volume of results returned by your queries. Mastery of these two options is essential in achieving fine control of your MySQL queries.

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.

Doing Calculations with SQL - Part 1
Reader Level:

Many web developers create displays that show calculated results, such as showing a product with taxes or discounts, or a product quantity and total. There are two ways that this can be done -- in your scripting language on the web page (PHP, ColdFusion, C#, or other languages) or in the SQL statement that retrieves the results from the database. It is almost always a better idea to create the calculations in your SQL statement.

This article will show how. Part 1 will focus on SELECT statements, and Part 2 will show UPDATE statements.

The Doing Calculations with SQL Series:
Doing Calculations with SQL - Part 1
Doing Calculations with SQL - Part 2

Using Subqueries
Reader Level:

Most database systems support the use of subqueries. If you have never used them, don't know what they are, or are confused by joins, then this article is for you. I'll be using the Northwind sample database that is readily available for SQL Server and Access, and available as a download for MySQL from my site.

Creating SQL Server Logins for a Web Site
Reader Level:

SQL Server is a popular database to use for your web site, especially since the Express version has been released, allowing the power of SQL Server to be used for free, making it a much more viable solution than some of the other free databases out there, like MySQL. With the added power comes added danger -- SQL Server is frequently targeted by hackers. My article on preventing SQL injection attacks mentioned database login security briefly. In this article, I will expand on how to actually create users for your web site.

Creating an Address Format Function in SQL Server
Reader Level:

Most web applications that have forms for user input need an address form for collecting address information from the user. Addresses can be in many formats, but you have to be able to allow the user to enter one or more lines of an address. When displaying the information, however, you might not want to show the blank lines. This article will show a SQL Server function to format one or more address lines.

SQL Server Function for Lists That Use the IN Clause
Reader Level:

When you use SQL Server for any application -- web or otherwise -- you should be using stored procedures. This creates a dilemma in some situations where you want to provide flexibility. A typical interface on the web has checkboxes that allow you to choose options for a search, or options for a product, or delete various products. When you allow the end user to choose things, it creates difficulties in providing a one-size-fits-all stored procedure. This article will show one way to do this with a function.

Conditional Inserts Using SQL
Reader Level:

It's easy to do a conditional insert using the server language of your choice, but to do it with SQL code exclusively is not as easy. There are some language additions and non-standard SQL code in most of the popular database servers, but a conditional insert can be done using straight SQL making it platform independent. This is useful in writing SQL scripts to perform database updates. Running a script without a conditional insert can create problems if the script is run twice, for example. This article will show the technique I use in doing a conditional insert using MS Access, MS SQL Server, and MySQL.

Speed up Your Site with SQL Server Profiler/Index Tuning Wizard
Reader Level:

SQL Server 2000 comes with several tools that can be used to fine-tune your database. In some cases, if your database has not been optimized with indexes, the improvements can be dramatic. This article will focus on one aspect of the SQL Server Profiler—using it to profile a typical web site session to allow the Index Tuning Wizard to make suggestions based on an actual use case scenario. This article will apply to any web site that uses SQL Server 2000—ASP, ColdFusion, or other.

Don't depend on the tools provided by SQL Server—they are no substitute for knowing how data is accessed and knowing how to properly index your data. However, after you have created what you feel are optimal indexes, it can't hurt to test your application in a real world scenario to see if any further optimization is needed. For an introduction to indexes, see my article Indexing a Database Table. This article will attempt to expand your toolset to include two built-in SQL Server tools.

Easy Eclipse Database Access, with QuantumDB
Reader Level:

The open source Eclipse platform is increasingly popular for developers of every stripe, and one of the chief reasons for this is its rich extensibility. This article will show you how to install and use the popular QuantumDB plug-in for database access, together with the PHPEclipse plug-in, to create a complete and flexible PHP-MySQL development environment. You'll be able to flip between PHP and SQL code with incredible ease!

Flex builders: you can play, too!

Managing Backup Scheduling and Archiving
Reader Level:

In this article we will look at how to build and schedule a successful backup and archiving index for our development work.

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.

Cleaning Email Addresses in SQL Server
Reader Level:

So you have an email list that has everything from valid, well-formed email addresses to useless gibberish that someone thought would get them through the form without hassle. Obviously, the best way to maintain an email list is to catch the badly formed email addresses before they occur -- through JavaScript or server-side validation. That does not always catch the culprits though. Most of these scripts use regular expressions, which catch the badly formed addresses but do nothing with bad addresses. Also, you may have inherited a list or maintained a list that did not validate addresses.

This article will show a couple of SQL Server user-defined functions that you can use to manually go through your list and find/fix/delete the bad addresses.

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.

Creating Daily Stats Pages
Reader Level:

A stat page can be used for an e-store, blog, ad rotater, or any other type of application where you want to track a specific series of "things" that happen each day. For example, on a blog you might track blog hits. In an e-store, you might track daily sales. For an ad rotator you might track click-throughs.

This article will show how to write a query to group the daily activity in the first section, and how to display that as a graph using ColdFusion in the second section. The article will use SQL Server syntax, but could be adapted to any database. Also, the SQL code is applicable to any server model—not only ColdFusion. This is part 1 of 2.

Moving Data into an Existing Database
Reader Level:

There are a lot of situations where you will need to move data from one database to another, or from one data format to another. For example, if you have a CSV file containing an address list, it will most likely not fit the format/style of your existing address table. Another situation is converting data from one e-store database to another—most e-commerce software has similar data formats but different fieldnames and possibly different data structures.

This article will show a few techniques for moving data accurately. The techniques should work with any good database administrative interface that has the capibility to import data and to write/execute SQL statements.

HTTP 500: Getting to the Root of the Error
Reader Level:

Setting out on the path to dynamic web site development can be a daunting task — it really is a whole new ball game. We can make things a little easier on ourselves by making an adjustment within Internet Explorers default settings and do away with the dreaded, and utterly useless, HTTP 500 error.

Random Queries (recordsets) For ASP, ColdFusion and PHP
Reader Level:

While most data displays on web sites are in some predetermined, or even user specified order, such as search results of a products table in product name order, it can be quite beneficial to display data randomly, such as random banners, or even random on sale products on the home page. This article will discuss how you can use ASP VBScript with Access and MS SQL Server; PHP with MySQL; and ColdFusion with Access, MS SQL Server and MySQL to generate random results, be it a single random banner from your advertisers, or five random products that are currently on sale.

Using DTS Packages with ColdFusion: Part 2
Reader Level:

Many people use SQL Server to store their data, however SQL Server is much more involved than simply a server that allows storage for data. Data Transformation Services (DTS) is part of SQL Server and allows you to import and export data, manipulate files on the system, use FTP, among other things. Using some of SQL Server's built-in DTS functionality along with ColdFusion gives your web application access to some of this functionality.

Part 1 of this series showed how to export data from SQL Server to a CSV file triggered by a ColdFusion page. This part will show how to upload a CSV file to the server and import it into a new table. It will show how to pass information (a filename) to a DTS package from a ColdFusion page and use it in the package.

Using DTS Packages with ColdFusion
Reader Level:

Many people use SQL Server to store their data, however SQL Server is much more involved than simply a server that allows storage for data. Data Transformation Services (DTS) is part of SQL Server and allows you to import and export data, manipulate files on the system, use FTP, among other things. Using some of SQL Server's built-in DTS functionality along with ColdFusion gives your web application access to some of this functionality.

Understanding SQL - Part 1: The Select Statement
Reader Level:

In this, the first in the series on understanding SQL statements, we will look at the basics of the SELECT statement. The aim of this series is to bring a good understanding of various SQL statements to the beginner or novice database user. As the series progresses we will work through the Insert, Update and Delete statements.

This series will give you the ability to go beyond the default Dreamweaver server behaviours, and make you familar enough with the SQL syntax to fix errors in your Dreamweaver recordsets as they occur.

This series will also provide you with the ability to go beyond what Dreamweaver provides in its default server behaviours and build your own custom SQL statements.

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

Learning PHP - Part 1: A Gentle Introduction
Reader Level:

A basic introduction to PHP, placing the language in context vis-a-vis its competitors, and providing syntax and usage examples — including connecting to, and displaying data from, a MySQL database.

The Learning PHP Series:
Learning PHP - Part 1: A Gentle Introduction
Learning PHP - Part 2: All About Arrays
Learning PHP - Part 3: Basic Content Management
Learning PHP - Part 4: Focus on Functions

Do-It-Yourself Database Administration: 10 Biggest Mistakes
Reader Level:

Web developers are frequently called upon to wear many hats. Mom-and-pop shops, small development companies, and small Internet service providers are frequently called upon to perform the task of database administrator.

This article will show ten of the biggest mistakes that inexperienced database administrators make. Database administrators make good money because the job is not an easy one and requires knowledge and experience. This article shows ten common mistakes when managing your own database.

SQL Mail - Sending Email Using SQL Server
Reader Level:

Many web applications use components or scripting to send mail. SQL Server has mail functionality built-in and can sometimes be the best solution. Many times you want an email to be automated as a task -- for example, sending a list of store orders once a day or a list of files that have been uploaded. Other times, you want an email to be sent when a specific thing occurs -- an order table is updated, or a document is printed. This article will show how SQL Mail can be set up and automated within SQL Server without involving your web application.

Using Conditional Parameters in SQL Server
Reader Level:

Most dynamic sites have a database search of some variety. Stored procedures are much more desirable in a web application for many reasons, yet when it comes to performing searches, the stored procedure is not as easily implemented. In a scripted web application, you can create dynamic SQL statements on the fly, putting conditional logic into your WHERE clauses and making the SQL statement fit the situtation. In a stored procedure, it's not so easy. This article will show one way to pass parameters conditionally to your SQL stored procedure. The technique applies to SQL Server, with code provided for ASP and ColdFusion.

This article assumes you know how to create stored procedures, work with the Query Analyzer, and set up your own DSNs.

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 Server: Part 2
Reader Level:

This article shows how to deal with a many-to-many relationship in SQL by using CSV strings to insert, update, and display data. This is useful for batch updates, multiple categories for products, and other situations where you have several items that need to be related to another item.

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.

Simple Dynamic Recordset Sorting Using Stored Procedures
Reader Level:

A stored procedure gives you many benefits when building applications, including web applications using ColdFusion, ASP, and other technologies. They are fast because they are precompiled and they are secure because the database tables are not being accessed directly. One thing that is a little tricky in working with stored procedures is building dynamic queries. You can build a SQL statement as a string within a stored procedure and execute it using the EXEC statement (or xp_executeSQL), but this also defeats one of the purposes of building your functionality into a stored procedure: the statement is not compiled when it is built dynamically. This article shows one way of creating dynamic SQL on the fly while still allowing the stored procedure to be precompiled.

Working with SQL Server Triggers
Reader Level:

If you are building a web application, many of the common database scenarios can be managed more easily through the use of triggers. This can reduce the amount of web scripting you have to do in your application. A trigger is a stored procedure, but it is a special kind of stored procedure. You don’t call a trigger explicitly, like you do a regular stored procedure. A trigger is executed as a result of some other action within your database.

Handling External Media in SQL Server
Reader Level:

So you want to create a SQL Server database, and you already have some data; how do you get that data into SQL Server? CSV or text-based data is one type of media that you may want to bring into your SQL Server database. Images, Flash files, MP3s, and other types of binary data are media that you might want to store in your SQL Server database. What's the best way to handle these types of data? Lastly, how are the SQL Server database files physically stored on the server and how can you move them?

SQL Injection Attacks
Reader Level:

Security is the current favorite buzzword. Are your sites secure? Read on to learn about a popular, easy-to-learn, and extremely dangerous, security loophole - SQL injection attacks - used by hackers to compromise your database.

Searching Unrelated Database Tables
Reader Level:

Most data-driven sites include a search option, where a user is able to search a database table or a related table for a specific value. An example of this is a site like Amazon.com, where you might want to search on a book subject, or a book author. Most likely, the author name is stored in a separate table. You can easily search on title, author, subject, or any of the above by writing a SQL statement that joins the related data. What if you want the user to be able to search multiple tables that do not relate, however? This article will address joining unrelated tables for a database search.

Intro to Database Design
Reader Level:

Learn the basics of setting up your first database.

Combatting the Invalid Column Name SQL Error
Reader Level:

Structured Query Language (SQL) is used to access a database, but is not always used to it's fullest potential. Also, frequently you come across an error in a statement that looks like it "should" work, but doesn't. One such error is the "Invalid Column Alias" error. This article shows a possible solution and workaround for the problem.

Moving to Stored Procedures - Part 3
Reader Level:

In this final part of the series, we will look at using your Stored Procedures in Dreamweaver applications.

The Moving to Stored Prodedures Series:
Moving to Stored Procedures - An Introduction
Moving to Stored Procedures - Part 2
Moving to Stored Procedures - Part 3

Moving to Stored Procedures - Part 2
Reader Level:

In Part 2 of this series, we look at the SQL Server Enterprise Manager you will use to create and manage your Stored Procedures, as well as some more advanced T-SQL commands.

The Moving to Stored Prodedures Series:
Moving to Stored Procedures - An Introduction
Moving to Stored Procedures - Part 2
Moving to Stored Procedures - Part 3

Moving to Stored Procedures – An Introduction
Reader Level:

You can realize tremendous performance increases by allowing your database to do what it does best. In this series you will learn to write Stored Procedures in SQL Server and use them in Dreamweaver. We will cover simple queries, SQL Command, advanced T-SQL concepts, and creating recordsets and commands from Stored Procedures in Dreamweaver.

The Moving to Stored Prodedures Series:
Moving to Stored Procedures - An Introduction
Moving to Stored Procedures - Part 2
Moving to Stored Procedures - Part 3

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.

Writing Readable SQL
Reader Level:

SQL is the language of the database. Many developers have a tendency to allow the visual tool that comes with the database to automatically write the SQL statements for your application. Many web developers even use the rudimentary SQL building tools of Dreamweaver MX to create the SQL for a web page. These tools are not bad, but frequently the SQL becomes hard to read because it has not been formatted or optimized. Writing a readable SQL statement can be an art in itself. This article will show you a few techniques for creating readable SQL. This will help you read your own SQL statements to make sense out of them, and will allow others to read your SQL and understand it.

Stored Procedures Basics
Reader Level:

Stored procedures offer some compelling benefits over embedded SQL statements. In addition to the performance benefits you gain, code centralization can really help your development efforts. Despite what you may think, developing with stored procedures is very easy.

Learn to:

• Create basic stored procedures.
• Improve database security.
• Centralize your business logic to promote code reuse.

This article uses Microsoft's SQL Server, and the t-sql language in its stored procedures.

A Quick Introduction to the Structured Query Language (SQL)
Reader Level:

When you make the move from building static HTML pages to developing dynamic sites with ColdFusion, .NET, PHP or JSP you will need to start learning to work with relational databases. This article provides a quick introduction to the basic essentials of the Structured Query Language (SQL) which is the language you use to work with databases.