SQL
Adding Columns to Large Database Tables
by Ray West - 11-Aug-09
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
by Ray West - 03-Aug-09
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
by Ray West - 31-Jul-09
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.
Quickshot: Enforcing Case Sensitivity in SQL Server
by Ray West - 01-Jul-09
Reader Level: 
If you are paying attention to security in your online applications, then you are likely encouraging your users to select strong passwords that contain numbers, special characters and letters that are both upper and lower case. Without special attention to your database settings, however, SQL Server will treat upper and lower case characters as equivalent to one another, causing you to loose a significant part of the strength of your passwords.
In this article, we will show a quick and simple way to have SQL Server enforce case sensitivity.
Dealing with NULL Values in SQL Server - Part 2
by Tom Muck - 21-May-09
Reader Level:
In SQL, there is data, no data, and NULLs. A NULL is a unique concept, and is often misunderstood by programmers. A field can contain data, or it can be blank, but it can also be NULL, which is not the same as blank.
The first part of this series introduced the concept of NULLs. In this part will show how to deal with NULLs for your inserts and updates.
The Dealing with NULL Values in SQL Server Series
Dealing with NULL Values in SQL Server - Part 1
Dealing with NULL Values in SQL Server - Part 2
Dealing with NULL Values in SQL Server - Part 1
by Tom Muck - 08-May-09
Reader Level:
In SQL, there is data, no data, and NULLs. A NULL is a unique concept, and is often misunderstood by programmers. A field can contain data, or it can be blank, but it can also be NULL, which is not the same as blank.
This article will introduce the concept of NULLs. A second part will show how to deal with NULLs for your inserts and updates, and creating tables that do not accept NULL values.
The Dealing with NULL Values in SQL Server Series
Dealing with NULL Values in SQL Server - Part 1
Dealing with NULL Values in SQL Server - Part 2
Doing Calculations with SQL - Part 2
by Tom Muck - 23-Apr-09
Reader Level:
In the last article in this series I talked about calculations on a web page. 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. For displaying data, it is almost always a better idea to create the calculations in your SQL statement, to keep the business logic out of the web page and take advantage of the data processing speed in the database.
This second, and last, part will show a few simple examples of doing calculations directly in INSERT and UPDATE statements, and when not to use the technique. The article is intended as a basic introduction to doing calculations in SQL and to get the reader thinking about alternative ways of doing things.
The Doing Calculations with SQL Series:
Doing Calculations with SQL - Part 1
Doing Calculations with SQL - Part 2
Doing Calculations with SQL - Part 1
by Tom Muck - 06-Apr-09
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 Table Aliases
by Tom Muck - 29-Jan-09
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).
Using Subqueries
by Tom Muck - 15-Jan-09
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.
Win, Lose or Tie - mySQL Scoreboard
by Paul Davis - 24-Oct-08
Reader Level: 
Manipulating data in mySQL can be tricky at times. In this tutorial, we will go over how to use a sport team's win/loss record, entered in to one column, to show how versatile mySQL can be. We will also address handling inconsistent data by introducing a win/lose/tie record and cover the ramifications from that as well.
Creating MySQL Logins for a Web Site
by Tom Muck - 20-Oct-08
Reader Level:
MySQL is a popular database to use for your web site, allowing the power of a server-based database management system to be used for free. With the added power comes added danger—MySQL 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, which is vital knowledge when you are acting as a database administrator. If you have a PHP site using MySQL and you don't have a database administrator—you are it.
Creating SQL Server Logins for a Web Site
by Tom Muck - 08-Oct-08
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.
Setting up a Multi-level Category Table in SQL
by Paul Davis - 03-Oct-08
Reader Level: 
You have a multiple level category list that you need to maintain. You have nested queries, complex deletes and inserts and it all seems to be a mess? Well, this handy little SQL process will make quick work of your category table.
Creating an Address Format Function in SQL Server
by Tom Muck - 28-Jul-08
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.
Comparing Timestamps in MySQL
by Ray West - 23-May-08
Reader Level:
In a recent article, we looked at comparing timestamp columns in SQL Server in order to pull records that occurred on a date or series of dates without regard to the time, and the question came up about how to do the same thing in MYSql.
Comparing Timestamps in SQL
by Ray West - 22-May-08
Reader Level: 
If your web application tracks user registrations, or sales transactions, or anything that you might need to pinpoint to the time it occurred, you probably use timestamps to mark a record as entered or updated in your database table. You might do this using Now() in ColdFusion and pass the date into the database or you might use getdate() in SQL Server as a default field value so that you capture exactly when a record is created or updated at the database level.
Quick Shot: SQL Zip Code Radius Search
by Paul Davis - 06-May-08
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
by Steven Seiller - 29-Apr-08
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.
From Create a Site to Connecting to a Database
by Paul Davis - 27-Mar-08
Reader Level:
If a picture is worth a thousand words - then a video should be worth millions (if, of course, you account for the 15 frames per second)! In my first video tutorial for Community MX, I am going to show how to set up a new Dreamweaver site using the basic site set up, set up a database on a Linux based server utilizing CPanel and then how to connect to the database.
Approximate download size: 26MB
mySQL Groupies and Subcategories
by Paul Davis - 11-Mar-08
Reader Level:
You need to get all of the categories for each product listed in a report - doing the heavy lifting in PHP seems to take the server extra time to get the work done. Learn about using the GROUP_CONCAT command in mySQL to make quick work of this problem.
Copy a Record in PHP Using Dreamweaver
by Paul Davis - 25-Feb-08
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!
Indexing Your Database
by Ray West - 14-Feb-08
Reader Level:
In a previous article we looked at ways to make your SQL Server stored procedures more efficient, thereby contributing to the overall performance of your application. In this article, let's look at another database level optimization: the proper indexing of your tables.
Indexing can be a complex topic, and we will not try to cover it all here, but there are a few things to know about indexing that can vastly improve the performance of your database as it grows. There are three types of indexes that we will consider in this article: Column Indexes, Clustered Indexes and Compound Indexes.
Save Time with MySQL Temporary Tables
by Paul Davis - 13-Feb-08
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 Server Function for Lists That Use the IN Clause
by Tom Muck - 28-Jan-08
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.
Quick Shot - Reset the Root mySQL Password
by Paul Davis - 11-Dec-07
Reader Level:
You have lost the root password, or the password is corrupt, to your mySQL server and you need to get it back as quickly as possible. Read this tutorial to get the instructions on how!
Conditional Inserts Using SQL
by Tom Muck - 26-Oct-07
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
by Tom Muck - 22-Oct-07
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.
Getting the Date In and Out of Your MySQL Database
by Paul Davis - 10-Oct-07
Reader Level:
Very often, it is the case that the date in which the user wishes to enter their information on the form is not the same date format that the database understands. Likewise, getting the date out in the format the database stores it in is not very friendly or readable. We will go over two functions which will assist in getting the date data in the database correctly and displaying it on the web page correctly as well.
Easy Eclipse Database Access, with QuantumDB
by Thomas Pletcher - 02-Oct-07
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!
Is it read yet?
by Paul Davis - 28-Sep-07
Reader Level:
When you browse the Community MX articles, have you noticed the details where it tells you if you've read it before or not? Do you want to do that on your site? Read on...
Create a Simple Database Web Search - Part 1
by Tom Muck - 26-Jul-07
Reader Level:
Putting together a simple database search is a piece of cake using built-in Dreamweaver tools -- no coding required. We'll show a simple Master/Detail Page Set behavior and how to add a simple search form to it. This tutorial will work with any Dreamweaver server model. Part 2 will focus on PHP and show a more advanced search.
The Creating A Simple Database Web Search Series:
Creating a Simple Database Web Search - Part 1
Creating a Simple Database Web Search - Part 2: PHP
Creating a Simple Database Web Search - Part 2: ColdFusion
The SQL CASE Statement
by Tom Muck - 11-Jul-07
Reader Level:
If you are using SQL, you are no doubt familiar with SELECT, INSERT, UPDATE, and DELETE, as well as a few functions that are useful. I've found that not many people are making active use of the CASE statement, although it is one of the most useful SQL keywords in existence. Typically a web application developer will use this type of conditional logic in his page code (using PHP, ColdFusion, or another language) making the business logic part of the presentation of the page. It is more efficient and better programming practice to put this logic in the database using a stored procedure or in the SQL statement so that the presentation of the data can be as clean and unencumbered by business logic as possible.
This article will show a few simple uses of the statement and how you can use it to simply web applications. These examples should work in SQL Server or MySQL. I will be using the Northwind database that comes free with SQL Server. Anyone using mySQL can download a Northwind creation script at my site.
SQL JOINS: The RIGHT JOIN
by Adrian Senior - 28-Jun-07
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
Managing Backup Scheduling and Archiving
by Adrian Senior - 27-Jun-07
Reader Level:
In this article we will look at how to build and schedule a successful backup and archiving index for our development work.
SQL Joins: The LEFT JOIN
by Adrian Senior - 20-Jun-07
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
SQL Joins: The INNER JOIN
by Adrian Senior - 11-Jun-07
Reader Level:
In this article we will begin to look at what is meant by a JOIN in SQL. I'm sure you have all heard of joins but what exactly are they and why do we need them? This article will be the first in a series of articles that look at the mystical world of SQL joins. I'll begin with the INNER JOIN, which is likely the most common of all the joins.
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
by Adrian Senior - 17-May-07
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
by Tom Muck - 08-May-07
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
by Gordon Mackay - 19-Dec-06
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.
Multiple Insert/Update in PHP - Part 2
by Tom Muck - 06-Dec-06
Reader Level:
Dreamweaver has some built-in tools for doing database inserts and updates, but they only handle basic inserts/updates of one record. What if you want to insert multiple records at a time, or update multiple records? Dreamweaver is no help in this case, but using PHP the process is simple. The first part of this tutorial showed two often-used methods for doing multiple inserts. Part 2 will show how to do a multiple update using these two techniques.
The Multiple Insert/Update in PHP Series:
Multiple Insert/Update in PHP - Part 1
Multiple Insert/Update in PHP - Part 2
Generating ATOM feeds with PHP
by Gordon Mackay - 28-Nov-06
Reader Level:
Web site content syndication is a very commonly used method of sending new content updates via desktop software that is able to read and make sense of the XML languages used in RSS and ATOM. These XML files are generically known as "feeds". They are prevalent in most blogging applications and some forums, but recently they have started to appear on almost every site that is updated on a regular basis.
In this tutorial we will look at generating an RSS feed from a very simple MySQL table using PHP.
Multiple Insert/Update in PHP - Part 1
by Tom Muck - 27-Nov-06
Reader Level:
Dreamweaver has some built-in tools for doing database inserts and updates, but they only handle basic inserts/updates of one record. What if you want to insert multiple records at a time, or update multiple records? Dreamweaver is no help in this case, but using PHP the process is simple.
This tutorial will show two often-used methods for doing multiple inserts and multiple update. The files testinsert.php and testinsert2.php are included in the download package showing the final code for both methods. Part 1 of this series will show how to do inserts and Part 2 will show how to do a multiple update using these two techniques.
The Multiple Insert/Update in PHP Series:
Multiple Insert/Update in PHP - Part 1
Multiple Insert/Update in PHP - Part 2
Creating Daily Stats Pages
by Tom Muck - 18-Sep-06
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
by Tom Muck - 08-May-06
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.
Deconstructing MySpace.com Part 1 - Social Networking Database
by Joel Martinez - 05-May-06
Reader Level:
MySpace is the social networking phenomenon that is sweeping the nation. Having recently been sold for US$580 Million, it is plain to see that the format has a value, and indeed, just about everyone I know below the age of 25 has a MySpace account. The reason MySpace works is because it puts the control of the content in the hands of the users, and by catering to the social aspects of the human condition has found itself in an enviable position of mass appeal despite some criticism.
The purpose of this, first of many articles is to deconstruct the application -- in this case, MySpace.com -- and show you how to build your own. I believe that the lessons that can be gleaned from learning how a successful site is constructed are plentiful and valuable. Namely, the ability for the users of your site to connect to each other.
The first thing we will look at is what database structure would be required to implement the social networking aspect of MySpace.com. The examples will be in the context of SQL Server 2005, but the knowledge should be transferable to other RDBMSs.
HTTP 500: Getting to the Root of the Error
by Adrian Senior - 16-Feb-06
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.
Searching for Data Using Wildcards in a Stored Procedure
by Heidi Bautista - 09-Nov-05
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!
SQL Tricks with UNION
by Joel Martinez - 25-Oct-05
Reader Level:
The SQL UNION keyword is one of those features that goes unused for long periods of time. But when the opportunity presents itself you will find that knowing when, how, and why to use it will provide many benefits. This article will present two examples of when this construct might be useful.
- Record Replacement: Substitute records in one table with data from another table. Useful in situations where you don't have a lot of control over the data.
- Record Insertion: Insert custom rows in a resultset to make displaying lists easier
Loading Database Tables Into a Query Dynamically
by Adrian Senior - 30-Sep-05
Reader Level:
In this tutorial we will look at how we can query a database to discover the names of the tables it contains. Once we have gleaned that information we'll loop through the tables and extract the ones that we need to include in our search facility.
Random Queries (recordsets) For ASP, ColdFusion and PHP
by Danilo Celic - 29-Sep-05
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.
Understanding SQL - Part 6: The SELECT Statement - The LIKE Operator and Wildcards
by Adrian Senior - 15-Jul-05
Reader Level:
In this the sixth tutorial in this series we will be taking a look at the LIKE operator and the wildcards that we can use alongside it. The LIKE operator is a very powerful tool, and can be an ideal operator to use when providing users with the ability to search your web site.
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 5: The SELECT Statement - Utilising the IN & BETWEEN Operators
by Adrian Senior - 27-Jun-05
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
Using DTS Packages with ColdFusion: Part 2
by Tom Muck - 16-Jun-05
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.
Understanding SQL - Part 4: The Select Statement - Evaluation Ordering
by Adrian Senior - 15-Jun-05
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
by Sheri German - 08-Jun-05
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
by Adrian Senior - 01-Jun-05
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
Using DTS Packages with ColdFusion
by Tom Muck - 25-May-05
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 2: The Select Statement & Comparison Operators
by Adrian Senior - 13-May-05
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
Understanding SQL - Part 1: The Select Statement
by Adrian Senior - 03-May-05
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
by Thomas Pletcher - 20-Apr-05
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
Working With SQL Server Part 5: Setting Constraints and Defining Diagrams
by Adrian Senior - 21-Feb-05
Reader Level:
In this tutorial, we will begin by looking at how you can set up a database diagram within Enterprise Manager. We will then move on and look at how we can set constraints to manage the data we allow to be inserted into our database - both by good table design and using constraint expressions.
Do-It-Yourself Database Administration: 10 Biggest Mistakes
by Tom Muck - 15-Feb-05
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
by Tom Muck - 21-Dec-04
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.
Previous and Next Links for Details Page Stored Procedure
by Tom Muck - 24-Nov-04
Reader Level:
If you have used Dreamweaver to create dynamic pages, you are probably familiar with the Repeat Region and the concept of Next/Previous records. You are probably familiar with the Master/Detail pageset concept -- the Master page contains a table listing your records with links that allow you to drill down into a Details page. The Details page gives you the details about a particular record. Unfortunately, the Details page does not allow you to create the Previous/Next links easily. This is often desirable in a site, such as in a photo album, real estate listing, or other type of list where the user might want to remain on a Detail page and simply cycle through the records rather than go back to the Master page again. This tutorial will show how to create a SQL stored procedure that will supply Previous/Next links to any ColdFusion or ASP page.
Using Conditional Parameters in SQL Server
by Tom Muck - 09-Nov-04
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.
Indexing a Database Table
by Tom Muck - 29-Sep-04
Reader Level:
Indexes are one of the most important aspects of database design, yet one of the most underused in web applications. A database index can speed up a query by hundreds or thousands of times. Imagine, for example, if the phone book was not alphabetized and you had to find a name. It would takes hours or days to find a name. This is exactly the kind of inefficiency that a database with no index has to contend with. The article will show where to put indexes.
Working With SQL Server - Part 4: Relationships and Foreign Keys
by Adrian Senior - 14-Sep-04
Reader Level:
In this tutorial, we will learn more about the Enterprise Manager interface. We will be looking at setting a foreign key and creating a relationship between two tables. We will also look at maintaining data integrity and checking data is suitable for the relationship. This tutorial also contains two videos to help illustrate the process.
Working With SQL Server - Part 3: Making a Remote Connection and Creating Our First Database Table
by Adrian Senior - 01-Sep-04
Reader Level:
In this tutorial, we will look at how we can create a new SQL Server group and how we can connect to a remote instance of SQL Server.
We'll also design our first database table in Enterprise Manager and investigate the properties we set within that table.
MySQL Command Line Tips
by Thomas Pletcher - 24-Aug-04
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
by Tom Muck - 20-Aug-04
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.
Working With SQL Server Part 2: Exploring the EM and Tables
by Adrian Senior - 18-Aug-04
Reader Level:
The aim of this tutorial is to understand the flow of the dynamic data in our web development projects. We'll begin by looking at where the database fits into the grand scheme of things, and then look into how the data is handled and processed by our applications.
Once we have achieved these goals we'll move on to explore what a database is and look at the tables it contains. We'll also begin to explore the Enterprise Manager GUI.
Working With SQL Server Part 1: Installation and Service Access
by Adrian Senior - 04-Aug-04
Reader Level:
A walk through installing a local instance of SQL Server 2000 for development work. We'll also look at the Properties dialogue box and see what options we have for how we interact with the server instance.
Using CSV Strings in SQL
by Tom Muck - 03-Aug-04
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
by Tom Muck - 09-Jul-04
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.
Dynamic Duo: JSP and MySQL
by Val Tobin - 21-Jun-04
Reader Level:
These days, no one would argue that a dynamic site is far more powerful, flexible, and scalable than a static site. But if you have a lot of static pages in your existing site, you might not know how to convert them in order to take advantage of the benefits of a dynamic site. This tutorial will show you how to do just that, by taking some existing static HTML pages, and converting them to Java Server Pages (JSP) using Dreamweaver MX 2004 and a MySQL database.
Working with SQL Server Triggers
by Tom Muck - 02-Jun-04
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
by Ray West - 24-May-04
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
by Heidi Bautista - 10-May-04
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.
Get That Code Into The Database Where It Belongs
by Tom Muck - 26-Apr-04
Reader Level:
This article will show how you can take some complex scripting logic out of your web pages, and put it into the database. Why would you want to do this? There are several advantages: the code will usually execute faster, the page code will be easier to read and maintain, and there will be a clearer separation between business logic and presentation logic. The concepts in the article will apply equally to all types of server languages.
Searching Unrelated Database Tables
by Tom Muck - 20-Feb-04
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.
Working with Microsoft's Query Analyser
by Adrian Senior - 13-Feb-04
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.
Database Design: Introducing Relationships and Queries
by Adrian Senior - 05-Feb-04
Reader Level:
In this tutorial, we will look at some of the problems we may encounter when designing our database, we will investigate primary keys, foreign keys and how we can avoid NULL values. We will also look at the WHERE clause and see how we can filter our information by using variables.
We will use the repeat region server behavior and look at how we can use and set up dynamic list menus. Further to this we will also look at the ColdFusion isdefined function to ensure that information is only displayed to our end user at the appropriate time.
This tutorial contains written and video instruction.
Intro to Database Design
by Joel Martinez - 17-Dec-03
Reader Level:
Learn the basics of setting up your first database.
Working with MySQL
by Joey Lott - 05-Dec-03
Reader Level:
Learn some helpful pointers for working with MySQL, including creating databases, managing data, and migrating from server to server.
Combatting the Invalid Column Name SQL Error
by Tom Muck - 19-Nov-03
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.
Comparison Shopping, using the IN statement in SQL
by Danilo Celic - 17-Oct-03
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…
Porting Pollster to SQL Server
by Paul Newman - 04-Sep-03
Reader Level:
One of the most popular widgets in Macromedia's DevNet Resource Kit Volume 4 (DRK 4) is Pollster. This tutorial includes everything you need to migrate Pollster to SQL Server, including a SQL script and instructions on revising the application's ColdFusion components.
Moving to Stored Procedures - Part 3
by Ray West - 22-Aug-03
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
by Ray West - 21-Aug-03
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
by Ray West - 14-Aug-03
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
by Ray West - 13-Aug-03
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
by Tom Muck - 25-Jul-03
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
by Jay Oliver - 02-Jul-03
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.
- Create basic stored procedures.
- Improve database security.
- Centralize your business logic to promote code reuse.
A Quick Introduction to the Structured Query Language (SQL)
by Arman Danesh - 19-Jun-03
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.
Check for duplicate database entries
by Bill Horvath - 17-Jun-03
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.
Ordering Tricks in SQL
by Tom Muck - 31-Mar-03
Reader Level:
A database is like a book with no index--you store information in it, but unless you know how to get it out in a particular order, it's not going to be very useful. Ordering is one of the most often used aspects of SQL, yet one of the most under-used parts of SQL language at the same time. You can often do some really cool things through simple ordering of your data, and present the data in a different way.
