Explore the Premium Content

Beginner's SQL: Cross Joins

A BAZILLION ROWS OF DATA! Has this ever happened to you? You have a working SQL query to retrieve data from a database table. Now you want to add a second table into the query, for related or more detailed data, and all of a sudden you have a bazillion pages of output. You have discovered the dreaded cross join effect. Despair not.

Whenever there are two tables in an SQL query, they will be joined. This means the rows of one table are combined with the rows of the other table. You must always pay attention to how you want them to be joined. With a bazillion rows, maybe you don't want a cross join. Other times, a cross join is exactly what you want. So let's look at this cross join effect more closely.

 

Read More

SQL Server Stored Procedures: the Whys and the Hows

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

Database performance tuning and optimisation

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

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

Introduction to Database Design - Normalisation

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

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

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

Read More

Database design and optimisation made easy

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

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

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

Read More

Tips for Building A Project

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

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

Read More

Using Stored Procedures and Commands in DWMX

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

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

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

Read More

Shiny New MySQL - Current and forthcoming

MySQL version 4.0 has been suitable for production use since March 2003.  The previous version is no longer under development. In today's article we will be looking at the shiny new features that you have to play with in version 4.0. Allan doesn't just give you a big list of the changes with no explanation; he explains why the major changes will make your job easier, make you richer and more popular with members of the opposite sex.

Then Allan examines the proposed changes in MySQL 4.1 and 5.0

This article explains

• why MySQL 4 is an 'ACID' database
•  FULLTEXT searches
• SQL_CALC_FOUND_ROWS and FOUND_ROWS() functions
• Sub-queries
• The UNION statement
• how MySQL becomes faster
  

Read More

The Right Index for the right Job

Along with database design, Indexes are the backbone of optimal database performance. It requires specialist knowledge to ensure that you have the right indexes for the right job and this article will provide you with enough knowledge to make the right choices - and to know when to use an index and when not.

We will also take a look at the different types of indexes that exist (clustered, non-clustered, covering indexes and indexing views), what each does and any restrictions on the use of that index.

Read More

Preventing SQL Injection Hacks

How can you guard against hacks if you host with an ISP? What are the most common hacks? What is SQL Injection - and why is it even more damaging if you use SQL Server? How can you avoid paying $99 for the Macromedia PHP User Authentification Behavior, yet get the same level of security?

Allan points out that, even if you're not running a huge e-commerce site, there are still hackers who will simply hack into your site, deface it, and leave their mark, like a dog that needs to get neutered. By deliberately building a bad PHP log-in script, deconstructing it, then repairing it, this article shows you how to neuter the dog and protect your work. Read More

Creating a Custom Database Class

The DMX Server Behaviors make it easy for new users to connect to a database and perform various operations, without them having to know how to create the code themselves. However, they are general purpose as they have to work in a range of different situations. The code created is complex, inefficient, and makes it extremely difficult to perform a range of different database operations on a single page.

In this 11 page article, Gareth shows you how to create a custom Database class, which makes it easy to perform Database Operations from your own code, and which can be used to replace the Dreamweaver MX Server Behaviors. Read More

MySQL Security

Most people think about security as an afterthought - or not at all. Allan shows how to configure your MySQL database so that the hackers move on to softer targets - like those used by developers who don't read this article.

Allans tells you why, and shows you how to
1. Change the root password
2. Remove the anonymous users
3. Remove anonymous access to the test database
4. Disable TCP connections
5. Create secure users that only have rights within single databases Read More