Explore the Premium Content

MySQL and Multiple Tables: Part 2

Last time we introduced designing a MySQL database with multiple tables in mind. We discussed the basics of how to put together some SQL to JOIN our tables together in useful ways. This tutorial follows on, looking a bit closer at using the database we created in Dreamweaver.

This time we're going to look at using some JOIN queries in a web application, using Dreamweaver MX 2004 and its server behaviours. Again we'll be using our CD database as an example. We'll also look at some of the functions we can use to group rows returned by our queries together.

Read More

MySQL and Multiple Tables: Part 1

When you're creating your PHP applications with Dreamweaver, it's necessary to have a good basic knowledge of how SQL works and how to get the information you want, from the tables you've got.

In this article, we're going to explore the mysteries of working with multiple tables, and the relationships they have between them. We'll start off by talking about how to define your data so each table has a logical relationship to each other table, then move on to using those relationships to create useful queries. It's aimed squarely at people who only have the basics of how to work with MySQL, and want to delve a bit further.

We're going to use a simple database about a CD collection to show off the concepts.

Know your Data

When you first start out, it's very easy to just dump bits of data about a concept into one table.

A typical table that somebody new to database might create would be:

CDs

Id (BigInt)

Name(Varchar 60)

Artist(Varchar 60)

Owner(Varchar 60)

Publisher(Varchar 60)

1

Regular Urban Survivors

Terrorvision

Matt

EMI

2

Appetite For Destruction

Guns and Roses

Matt

Geffen

Now, the problem with this is that you get a lot of repetition. Every time I add a new CD I re-type the Band, the Owner and the Publisher, if somebody different owns the CD I have to add a new CD with a different owner. This isn't very good for storage purposes (I'm storing the names multiple times), results in very big tables and just isn't very efficient. With just 5 columns this isn't too noticeable, but when we start adding more details like owner contact number, owner addresses, publisher addresses, the duplication will start to become really unwieldy.

Read More

SQL: Strings

Handling strings in SQL requires an understanding of basic string syntax, from declaring tables with character columns, to entering strings in SQL for inserting, updating, or searching for rows. 

But that's just the start. Databases also have a huge assortment of string functions, from simple concatenation to SUBSTRING, LENGTH, and a variety of even more powerful functions.

In this article, Rudy reviews the basic syntax and gives several tips for working successfully with strings, and then provides a number of examples of string functions in action, examples which you may think might only ever be solved with application scripting logic but which, upon closer inspection, can be accomplished with string functions in single queries.

Read More

SQL: NULL

Have you ever run across the suggestion that you should always use NOT NULL when creating a database table? What is NULL, anyway? Why would you want to use it?  What are the advantages to using NULL? How does NULL figure in to LEFT OUTER joins?

Rudy answers these questions with plenty of examples in this article about NULL.  The advantage to using NULL is described in the context of designing database tables where a column's value can be unknown or not appropriate. NULL is also discussed in the use of aggregate functions. Finally, an illustration of NULL in LEFT OUTER joins is provided.

Should you use NULL? This article answers the question positively.

Read More

SQL: Tips

This week's SQL article is a collection of tips and techniques for writing SQL.

 

The tips are divided into these categories:

  • Syntax
  • More Syntax
  • Performance
  • Design

The syntax tips cover reserved words, NULLs, strings, parentheses, and IN lists. You'll also learn what's wrong with "select star", why JOIN syntax is better, and when to write RIGHT OUTER JOINs (hint: never).

The performance tips barely scratch the surface (database performance is a career unto itself), but they include the importance of indexes and how to make sure you don't sabotage your own indexes.

The design tips include advice about first normal form, using simple datatypes, and autonumbers.

If you like these tips (or not), if you want to see more, or if you have any feedback on them at all, please post a comment in the discussion forum for this article.

Read More

SQL: Paging

In this tutorial, Rudy explores the SQL to accomplish paging of content drawn from a database and displayed on a Web site.  Paging can be done one item per page, such as for large articles or stories, or multiple items per page, such as for search results, when it is common to see, say, 10 per page.


By analyzing the performance dynamics of paging in general, and then exploring the specific SQL to carry it off, Rudy's tutorial will give you the confidence to implement paging on your own site.

Read More

Beginner's SQL: CASE expressions

There's more to SQL than SELECT columns FROM tables, just as there's more to life than Go, Dog. Go!

SQL can, to someone learning it, seem incredibly difficult beyond the simplest statements. There's nothing wrong with simple SQL, though. Part of the art of SQL is getting complex results with simple statements. And as parents who have read this book many times can confirm, even Go, Dog. Go! is filled with subtlety, intrigue, beauty, power, wonder, infuriation, surprise, and joy, all while being written in simple language. Just like SQL.

This article describes CASE expressions and what you can do with them. The syntax is covered briefly, mainly because there's little to explain, since it's rather straightforward. At least, so it appears on the surface. Then come the examples, which reveal how comprehensive, powerful, yet simple your SQL can be with CASE expressions.

Read More

Beginner's SQL: Duplicates

As a member of various database discussion forums over the last few years, I've seen thousands of SQL problems, many of which seem to show up again and again. Based on how frequently it is asked, the question of deleting duplicates is among the most common.

In this article, we'll look at the nature of the duplicates problem, how it arises, and then examine several approaches to resolving the issues.

During the course of the tutorial we'll also see how to write queries that address such other common situations, such as how to select the supplier with the lowest price for a product (from several offering the same item).

Read More

Interacting with the Firebird Database: Using PHP to Insert, Update and Delete Records

In the third of the series of articles on the Firebird database, we're going to start by creating a user for the database before investigating how to insert, update and delete records by using PHP.

 

Here Allan will be building directly on, and making use of descriptions contained in the previous article Connecting to the Firebird Database.

Read More

Connecting to the Firebird Database

In this second article on the Firebird database, Allan shows us how to use triggers in the database to set-up fields that auto-increment (as we saw in the first article Firebird doesn't let you do this straight off - you have to use internal counters called generators).

 

Moving on from there, Allan introduces us to an open source project (ibWebAdmin) that consist of a set of PHP scripts that allow you to easily administer your Firebird database through a web browser.

Read More

Installing MySQL on a Windows PC

In this article we are going to look at installing MySQL on a Windows PC. MySQL is a widely used database system, for both Windows and Linux, and offers much more power than a desktop database such as Microsoft Access!

 

Since it can cope with a large number of concurrent users it's used on a huge number of Internet sites.

 

As MySql is so popular, and well supported both by Dreamweaver and hosting companies, it's ideal to grab for your home PC and experiment with. In this article we'll be seeing how to install and start MySQL, set up some basic administration and then back-up a database. We'll finish up by having a quick look at some of the tools available for administering MySQL.

Read More

Beginner's SQL: UNION

Many SQL tutorials will show you how to combine tables using joins before they discuss unions. This is a shame, because unions are easier. Children learning arithmetic are always taught first to add, and only then to multiply. Unions and joins are just like that. Unions combine tables by "adding" them, while joins combine tables by "multiplying" them.

In this article, the humble UNION operator is presented in all its simple glory. Its purpose, to combine tables, is illustrated with several useful examples.

Read More
Older articles