SQL
Ordering Tricks in SQL
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.
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.
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.
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.
Expiring Time Sensitive Records in MySQL Driven Content Management Systems
Reader Level:
Have you ever left a voicemail and their outgoing message referred to some specific event last Wednesday? You wanted to let them know about the outdated message, but you didn't want to embarrass them? Ever pull up a web page and see a similarly embarrassingly outdated notice which now makes the web page look neglected and untrustworthy? Now ask yourself, "How many of your colleagues or friends would tell you that you had this problem?"
This tutorial is all about avoiding that embarrassing and compromising situation. It all started when I was recently working on a training catalog which I constructed as a dynamic content management system. Over the year of building these I've come to realize that just because anyone technically can keep the content updated doesn't mean they will. While the CMS contained a field to write upcoming schedule information, I knew that updating that information when it was no longer current wasn't going to be at the top of anyone's calendar. To prevent this scenario, I added an expiration date to the content table so that a date could be set after which the content would no longer be displayed.
In this tutorial, you will learn how to add an expiration date field to any content table. You will also see how to make record selections which which not include expired records (and thus eliminating the need for server-side scripting logic to not display expired records.) I've already mentioned one use case, training catalog schedule information, but this approach is great for many other uses as well. These might include time-sensitive blog posts, employment offers, news scroller entries, service notices or time-limited coupon code and sale offers.
Reader Level:
Have you ever left a voicemail and their outgoing message referred to some specific event last Wednesday? You wanted to let them know about the outdated message, but you didn't want to embarrass them? Ever pull up a web page and see a similarly embarrassingly outdated notice which now makes the web page look neglected and untrustworthy? Now ask yourself, "How many of your colleagues or friends would tell you that you had this problem?"
This tutorial is all about avoiding that embarrassing and compromising situation. It all started when I was recently working on a training catalog which I constructed as a dynamic content management system. Over the year of building these I've come to realize that just because anyone technically can keep the content updated doesn't mean they will. While the CMS contained a field to write upcoming schedule information, I knew that updating that information when it was no longer current wasn't going to be at the top of anyone's calendar. To prevent this scenario, I added an expiration date to the content table so that a date could be set after which the content would no longer be displayed.
In this tutorial, you will learn how to add an expiration date field to any content table. You will also see how to make record selections which which not include expired records (and thus eliminating the need for server-side scripting logic to not display expired records.) I've already mentioned one use case, training catalog schedule information, but this approach is great for many other uses as well. These might include time-sensitive blog posts, employment offers, news scroller entries, service notices or time-limited coupon code and sale offers.
How to Report Non-Calendar Years in PHP and MySQL
Reader Level:
Calendar years make reporting easy. The calendar year 2010 is simply distinguished from 2009. What could be easier than selecting all records from the current calendar year?
SELECT * FROM `records` WHERE YEAR(`record_date`) = YEAR(NOW())
Some organizations, however, maintain reporting calendars which do not start on January 1 and end on December 31. Businesses maintain a fiscal year, educational institutions keep an academic year and even sports leagues keep seasons which begin in one calendar year and end in the next. In these cases, assessing the current year is not as simple as looking at the calendar. For example, the fiscal year for many countries around the world begins July 1 and ends on the following June 30. The Government of the US has a fiscal year which starts on October 1. In the UK, the fiscal year for personal taxation reporting begins on April 6.
This article will show you how to use both PHP and MySQL to define a custom reporting year and determine the current reporting year at any date. These functions will allow you to easily include the start and end points of the year as well as create reporting queries based on these defined temporal end points.
Reader Level:
Calendar years make reporting easy. The calendar year 2010 is simply distinguished from 2009. What could be easier than selecting all records from the current calendar year?
SELECT * FROM `records` WHERE YEAR(`record_date`) = YEAR(NOW())
Some organizations, however, maintain reporting calendars which do not start on January 1 and end on December 31. Businesses maintain a fiscal year, educational institutions keep an academic year and even sports leagues keep seasons which begin in one calendar year and end in the next. In these cases, assessing the current year is not as simple as looking at the calendar. For example, the fiscal year for many countries around the world begins July 1 and ends on the following June 30. The Government of the US has a fiscal year which starts on October 1. In the UK, the fiscal year for personal taxation reporting begins on April 6.
This article will show you how to use both PHP and MySQL to define a custom reporting year and determine the current reporting year at any date. These functions will allow you to easily include the start and end points of the year as well as create reporting queries based on these defined temporal end points.
What If MySQL Could Eliminate PHP?
Reader Level:
The hallmark of a successful programmer can be measured in economy of code. Being proficient means doing more work with less code. In working with MySQL data in PHP scripts, one can eliminate a lot of heavy lifting in PHP with some simple MySQL functions. In today's example, we'll look at MySQL's IF() function as a way to supply data already interpreted so we don't have to do the work in PHP.
Reader Level:
The hallmark of a successful programmer can be measured in economy of code. Being proficient means doing more work with less code. In working with MySQL data in PHP scripts, one can eliminate a lot of heavy lifting in PHP with some simple MySQL functions. In today's example, we'll look at MySQL's IF() function as a way to supply data already interpreted so we don't have to do the work in PHP.
Quickshot - Working with NULL and Empty Values in MySQL
Reader Level:
You have a database with NULL values in a column and empty values in a column and you need to test against both conditions and return records that have neither null or empty values or that replace these values with something.
Reader Level:
You have a database with NULL values in a column and empty values in a column and you need to test against both conditions and return records that have neither null or empty values or that replace these values with something.
Audit a MySQL Data Table - Part 3
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This first two parts of the series showed a simple way to create an audit table and related functionality in MySQL without needing to modify your web application much at all. The third and final part will show a simple report on the web of the audit results. For the article, I'll assume you know how to set up PHP sites, work with basic database operations in PHP (listing, inserting, updating data), and working with session variables.
The Audit a MySQL Data Table Series:
Audit a MySQL Data Table - Part 1
Audit a MySQL Data Table - Part 2
Audit a MySQL Data Table - Part 3
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This first two parts of the series showed a simple way to create an audit table and related functionality in MySQL without needing to modify your web application much at all. The third and final part will show a simple report on the web of the audit results. For the article, I'll assume you know how to set up PHP sites, work with basic database operations in PHP (listing, inserting, updating data), and working with session variables.
The Audit a MySQL Data Table Series:
Audit a MySQL Data Table - Part 1
Audit a MySQL Data Table - Part 2
Audit a MySQL Data Table - Part 3
Using Cartesian SQL Queries
Reader Level:
What is a Cartesian query? It is a query that returns every row in two (or more) tables. Generally, these are to be avoided since the require a lot of system resources and don't seem to return useful information, however, the returned information can be manipulated to have some nice results.
Reader Level:
What is a Cartesian query? It is a query that returns every row in two (or more) tables. Generally, these are to be avoided since the require a lot of system resources and don't seem to return useful information, however, the returned information can be manipulated to have some nice results.
Get the Time of Day as a MySQL Developer - Part 7: Date and Time Cookbook
Reader Level:
Throughout this series, you have learned over 50 date and time functions which allow you to get the time and date, format its appearance, convert to another unit of measure or to perform calculations on elapsed time. Now that you know how to manage dates and times, it is time to put that knowledge into practice.
Included in this article is list of recipes for your MySQL cookbook ready to use in your projects. These recipes also serve as examples of the power of MySQL- especially when compated to the programmatic effort required, in some cases, by PHP or another scripting language.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 5: Conversion Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 6: Date and Time Math Functions
Get the Time of Day as a MySQL Developer - Part 7: Date and Time Cookbook
Reader Level:
Throughout this series, you have learned over 50 date and time functions which allow you to get the time and date, format its appearance, convert to another unit of measure or to perform calculations on elapsed time. Now that you know how to manage dates and times, it is time to put that knowledge into practice.
Included in this article is list of recipes for your MySQL cookbook ready to use in your projects. These recipes also serve as examples of the power of MySQL- especially when compated to the programmatic effort required, in some cases, by PHP or another scripting language.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 5: Conversion Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 6: Date and Time Math Functions
Get the Time of Day as a MySQL Developer - Part 7: Date and Time Cookbook
Get the Time of Day as a MySQL Developer, Part 6 - Date and Time Math Functions
Reader Level:
Reader Level:
As of version 5, MySQL has over 50 date and time functions which allow you to get the time and date, format its appearance, convert to another unit of measure or to perform calculations on elapsed time. While the MySQL manual details the functions altogether on one page, I will cover these functions by their specific purpose.
There are four types of date and time functions within MySQL. By covering these numerous functions within these four categories, I hope to make learning easier for you. I have divided these into the following categories:
- Queries – Get a time or date value from the server.
- Formatting – Change the appearance of a date or time value.
- Conversions – Change the date or time from one unit of measure to another.
- Math – Calculate the elapsed time between two dates or calculate the time to or from a specific date.
In this part, we will cover the MySQL functions for performing calculations on dates and times. These functions will provide you with a date and time before or after a certain amount of time as passed from a specified value or will calculate the elapsed
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 5: Conversion Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 6: Date and Time Math Functions
Get the Time of Day as a MySQL Developer - Part 7: Date and Time Cookbook
Audit a MySQL Data Table - Part 1
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This first two parts of the series showed a simple way to create an audit table and related functionality in MySQL without needing to modify your web application much at all. The third and final part will show a simple report on the web of the audit results. For the article, I'll assume you know how to set up PHP sites, work with basic database operations in PHP (listing, inserting, updating data), and working with session variables.
The Audit a MySQL Data Table Series:
Audit a MySQL Data Table - Part 1
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This first two parts of the series showed a simple way to create an audit table and related functionality in MySQL without needing to modify your web application much at all. The third and final part will show a simple report on the web of the audit results. For the article, I'll assume you know how to set up PHP sites, work with basic database operations in PHP (listing, inserting, updating data), and working with session variables.
The Audit a MySQL Data Table Series:
Audit a MySQL Data Table - Part 1
Get the Time of Day as a MySQL Developer, Part 5 - Conversion Functions for Dates and Times
Reader Level:
As of version 5, MySQL has over 50 date and time functions which allow you to get the time and date, format its appearance, convert to another unit of measure or to perform calculations on elapsed time. While the MySQL manual details the functions altogether on one page, I will cover these functions by their specific purpose.
There are four types of date and time functions within MySQL. By covering these numerous functions within these four categories, I hope to make learning easier for you. I have divided these into the following categories:
Queries -- Get a time or date value from the server.
Formatting -- Change the appearance of a date or time value.
Conversions -- Change the date or time from one unit of measure to another.
Math -- Calculate the elapsed time between two dates or calculate the time to or from a specific date.
This installment will cover conversion functions which are used for converting a temporal value of one unit of measure to another.
Reader Level:
As of version 5, MySQL has over 50 date and time functions which allow you to get the time and date, format its appearance, convert to another unit of measure or to perform calculations on elapsed time. While the MySQL manual details the functions altogether on one page, I will cover these functions by their specific purpose.
There are four types of date and time functions within MySQL. By covering these numerous functions within these four categories, I hope to make learning easier for you. I have divided these into the following categories:
Queries -- Get a time or date value from the server.
Formatting -- Change the appearance of a date or time value.
Conversions -- Change the date or time from one unit of measure to another.
Math -- Calculate the elapsed time between two dates or calculate the time to or from a specific date.
This installment will cover conversion functions which are used for converting a temporal value of one unit of measure to another.
Audit a SQL Server Data Table - Part 3
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This first two parts of the series showed a simple way to create an audit table and related functionality in SQL Server without needing to modify your web application much at all. The third and final part will show a simple report on the web of the audit results. For the article, I'll assume you know how to set up ColdFusion sites, work with basic database operations in ColdFusion (listing, inserting, updating data), and working with session variables.
The Auditing a SQL Server Data Table Series:
Audit a SQL Server Data Table - Part 1
Audit a SQL Server Data Table - Part 2
Audit a SQL Server Data Table - Part 3
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This first two parts of the series showed a simple way to create an audit table and related functionality in SQL Server without needing to modify your web application much at all. The third and final part will show a simple report on the web of the audit results. For the article, I'll assume you know how to set up ColdFusion sites, work with basic database operations in ColdFusion (listing, inserting, updating data), and working with session variables.
The Auditing a SQL Server Data Table Series:
Audit a SQL Server Data Table - Part 1
Audit a SQL Server Data Table - Part 2
Audit a SQL Server Data Table - Part 3
Get the Time of Day as a MySQL Developer - Part 4 - Formatting Functions for Dates and Times
Reader Level:
As of version 5, MySQL has over 50 date and time functions which allow you to get the time and date, format its appearance, convert to another unit of measure or to perform calculations on elapsed time. While the MySQL manual details the functions altogether on one page, I will cover these functions by their specific purpose.
There are four types of date and time functions within MySQL. By covering these numerous functions within these four categories, I hope to make learning easier for you. I have divided these into the following categories:
Queries -- Get a time or date value from the server.
Formatting -- Change the appearance of a date or time value.
Conversions -- Change the date or time from one unit of measure to another.
Math -- Calculate the elapsed time between two dates or calculate the time to or from a specific date.
This installment will cover functions for formatting dates and times to give them the appearance that you wish to display.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Reader Level:
As of version 5, MySQL has over 50 date and time functions which allow you to get the time and date, format its appearance, convert to another unit of measure or to perform calculations on elapsed time. While the MySQL manual details the functions altogether on one page, I will cover these functions by their specific purpose.
There are four types of date and time functions within MySQL. By covering these numerous functions within these four categories, I hope to make learning easier for you. I have divided these into the following categories:
Queries -- Get a time or date value from the server.
Formatting -- Change the appearance of a date or time value.
Conversions -- Change the date or time from one unit of measure to another.
Math -- Calculate the elapsed time between two dates or calculate the time to or from a specific date.
This installment will cover functions for formatting dates and times to give them the appearance that you wish to display.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Audit a SQL Server Data Table - Part 2
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This article will show one simple way to create an audit table and related functionality in SQL Server without needing to modify your web application much at all. This first part of the series set up a sample application. This part will create the SQL Server functionality to handle the audit. For the article, I'll assume you know how to set up ColdFusion sites, work with basic database operations in ColdFusion (listing, inserting, updating data), and working with session variables. The series will focus on the auditing of the table.
The Auditing a SQL Server Data Table Series:
Audit a SQL Server Data Table - Part 1
Audit a SQL Server Data Table - Part 2
Audit a SQL Server Data Table - Part 3
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This article will show one simple way to create an audit table and related functionality in SQL Server without needing to modify your web application much at all. This first part of the series set up a sample application. This part will create the SQL Server functionality to handle the audit. For the article, I'll assume you know how to set up ColdFusion sites, work with basic database operations in ColdFusion (listing, inserting, updating data), and working with session variables. The series will focus on the auditing of the table.
The Auditing a SQL Server Data Table Series:
Audit a SQL Server Data Table - Part 1
Audit a SQL Server Data Table - Part 2
Audit a SQL Server Data Table - Part 3
Get the Time of Day as a MySQL Developer, Part 3 - Query Functions for Dates and Times
Reader Level:
As of version 5, MySQL has over 50 date and time functions which allow you to get the time and date, format its appearance, convert to another unit of measure or to perform calculations on elapsed time. While the MySQL manual details the functions altogether on one page, I will cover these functions by their specific purpose.
There are four types of date and time functions within MySQL. By covering these numerous functions within these four categories, I hope to make learning easier for you. I have divided these into the following categories:
Queries -- Get a time or date value from the server.
Formatting -- Change the appearance of a date or time value.
Conversions -- Change the date or time from one unit of measure to another.
Math -- Calculate the elapsed time between two dates or calculate the time to or from a specific date.
This installment will cover query functions which are use to access the current date and time.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Reader Level:
As of version 5, MySQL has over 50 date and time functions which allow you to get the time and date, format its appearance, convert to another unit of measure or to perform calculations on elapsed time. While the MySQL manual details the functions altogether on one page, I will cover these functions by their specific purpose.
There are four types of date and time functions within MySQL. By covering these numerous functions within these four categories, I hope to make learning easier for you. I have divided these into the following categories:
Queries -- Get a time or date value from the server.
Formatting -- Change the appearance of a date or time value.
Conversions -- Change the date or time from one unit of measure to another.
Math -- Calculate the elapsed time between two dates or calculate the time to or from a specific date.
This installment will cover query functions which are use to access the current date and time.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Audit a SQL Server Data Table - Part 1
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This article will show one simple way to create an audit table and related functionality in SQL Server without needing to modify your web application much at all. This first part of the series will set up a sample table and give a little background preparation for the functionality. For the article, I'll assume you know how to set up ColdFusion sites, work with basic database operations in ColdFusion (listing, inserting, updating data), and working with session variables. The series will focus on the auditing of the table.
The Auditing a SQL Server Data Table Series:
Audit a SQL Server Data Table - Part 1
Audit a SQL Server Data Table - Part 2
Audit a SQL Server Data Table - Part 3
Reader Level:
When you have a database table that gets updated or changed, it makes sense to keep track of those updates. A relational database table is like a snapshot in time. It contains your data in its most recent form. When you update it, the previous data is lost. For example, a product might have a price. When you change that price, the previous price is lost. Many times it can be retrieved from a log, but another way to make sure you have an audit trail of your data is to create dedicated audit tables for each table that you want to keep track of.
This article will show one simple way to create an audit table and related functionality in SQL Server without needing to modify your web application much at all. This first part of the series will set up a sample table and give a little background preparation for the functionality. For the article, I'll assume you know how to set up ColdFusion sites, work with basic database operations in ColdFusion (listing, inserting, updating data), and working with session variables. The series will focus on the auditing of the table.
The Auditing a SQL Server Data Table Series:
Audit a SQL Server Data Table - Part 1
Audit a SQL Server Data Table - Part 2
Audit a SQL Server Data Table - Part 3
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Reader Level:
The first part of this article covered MySQL date and time data types and their usage. Introduced was the TIMESTAMP type which, like the DATETIME type stores the date and time, but is used primarily for the purpose of tracking table modifications. Timestamps can be configured such that they automatically update themselves when a table record is modified.
This part of the series will go into more detail on the utilization of TIMESTAMPs in your tables. We'll then explore a bit of advanced configuration which in effect makes it possible to have two auto_updating TIMESTAMPs: one updating on creation and one updated on update. This will allow you convenience in automatically tracking additions to your tables.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Reader Level:
The first part of this article covered MySQL date and time data types and their usage. Introduced was the TIMESTAMP type which, like the DATETIME type stores the date and time, but is used primarily for the purpose of tracking table modifications. Timestamps can be configured such that they automatically update themselves when a table record is modified.
This part of the series will go into more detail on the utilization of TIMESTAMPs in your tables. We'll then explore a bit of advanced configuration which in effect makes it possible to have two auto_updating TIMESTAMPs: one updating on creation and one updated on update. This will allow you convenience in automatically tracking additions to your tables.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Reader Level:
You won't have to wait long, as a web developer, before needing to store and retrieve dates and times in MySQL. A mastery of the date and time data types as well as familiarity with the many date and time functions is essential for any MySQL developer in order to maintain integrity in the temporal values that you manage.
This article will cover MySQL date and time data types and their usage. Firstly, we'll look at the options for storing temporal data and later we'll look at important functions for retrieving and using this data. This series is designed to provide the fundamental working knowledge necessary for managing temporal data in MySQL and is designed for beginners as well as experienced users who did not receive formal training in MySQL.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
Reader Level:
You won't have to wait long, as a web developer, before needing to store and retrieve dates and times in MySQL. A mastery of the date and time data types as well as familiarity with the many date and time functions is essential for any MySQL developer in order to maintain integrity in the temporal values that you manage.
This article will cover MySQL date and time data types and their usage. Firstly, we'll look at the options for storing temporal data and later we'll look at important functions for retrieving and using this data. This series is designed to provide the fundamental working knowledge necessary for managing temporal data in MySQL and is designed for beginners as well as experienced users who did not receive formal training in MySQL.
The Get the Time of Day using MySQL Series:
Get the Time of Day as a MySQL Developer - Part 1: Data Types
Get the Time of Day as a MySQL Developer - Part 2: Automatically Updated Timestamp
Get the Time of Day as a MySQL Developer - Part 3: Query Functions for Dates and Times
Get the Time of Day as a MySQL Developer - Part 4: Formatting Functions for Dates and Times
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.
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.
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?
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.
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.
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.
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
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.
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
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
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
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
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
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
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
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
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
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).
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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
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!
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.
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
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
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
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.
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
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!
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
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.
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
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.
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
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.
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
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!
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
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.
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.
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
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.
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
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!
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?
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...
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
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
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
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.
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
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
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
Reader Level:
In this article we will look at how to build and schedule a successful backup and archiving index for our development work.
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
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
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
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
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
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.
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.
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.
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
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
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
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.
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
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
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
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.
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.
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
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.
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
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.
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
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!
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
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.
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
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.
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
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.
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
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
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
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
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
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.
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
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
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.
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
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
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.
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
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
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
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
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
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
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.
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
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.
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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
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.
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.
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
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.
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
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.
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
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.
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.
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
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.
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
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.
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?
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.
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
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.
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
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.
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
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.
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
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.
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.
Working with MySQL
Reader Level:
Learn some helpful pointers for working with MySQL, including creating databases, managing data, and migrating from server to server.
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
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.
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
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…
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
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.
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
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
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
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
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.
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.
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:
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.
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.
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
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.
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.
Searching CMX
Get familiar with the CMX search facility & learn how to find the tutorials you need easily & quickly.
Latest Free Content
-
Create a Stone Effect for Text -
Smart Objects for Photographers: Part 1 - Neon Text Effects
- Inked Stone
-
The TwentyEleven WordPress Series: Introduction
View All Free Content
Advertise with CommunityMX
Copyright © 2010 - CommunityMX - All rights reserved. Conforms to W3C Standard XHTML & CSS