Explore the Premium Content

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

Getting Started with the Firebird Database

One of the first things you need to decide when starting planning and development on a new project is which database you intend using for the project. Do you go big budget with Microsoft SQL Server or Oracle, or towards the open source end of things with MySQL, PostgreSQL and SQLite all of which have different drawbacks?

 

In this series of articles Allan introduces us to another alternative – a completely free open source feature rich database, called Firebird. In future articles he's going to show us how to code support for Firebird into our PHP pages but this article starts with the crucial first steps – installation, how to change the admin password and creating a first database and table.

Read More

Beginner's SQL: Many-to-many relationships

Many-to-many relationships are all around us. Just think of actors and movies – many actors in one movie, individual actors make many movies. Thus the ability to represent and query such relationships is a crucial part of database operation.

 

In this article Rudy guides us clearly through the theory of many-to-many relationships before outlining several very common SQL queries you'll need when working with them.

Read More

MySQL: Exporting Data to Excel II: Going straight to Excel

Last week Allan introduced us to the notion of pulling information out from our database and making it available as in a CSV type format ready for using in Excel. This week Allan extends this idea by firstly showing us how to save our CSV data and then how to really wow our reporting colleagues by using BiffWriter to throw data straight into an Excel document….

 

…. and even if the business analysts don't really appreciate it, you can have an inner glow of satisfaction knowing how much more usable your website is!

Read More

MySQL: Exporting Data to Excel

So, your web site is all running nicely, and suddenly your boss wants a report on how many users in the database are aged between 30-35, or live in France, or visited in the last week. Of course, the site spec never had any reporting facility, and you'll be asked to produce custom SQL queries like this every time they have a marketing meeting to discuss leveraging synergies while thinking out of the box.

In this article, Allan shows you how to make the data easily exportable from a MySQL database into Excel, ready for Powerpoint loveliness, and then shows how you can bag up the code as a Dreamweaver Server Behavior for the next time the boss interrupts your game of Quake.

Read More

Beginner's SQL: How about a Date?

Today we will uncover the secrets to success using dates in SQL, secrets not at all difficult to understand, once you've seen them. In a way, they're like magic tricks. If you know how the tricks are done, they're not really magic, although they may still be delightful.

This article contains explanations, tips, and examples for several different database systems, but makes no attempt to be exhaustive. There's just not enough space or time. My intent is to give you the confidence to deal with your own date situations, by helping you understand how they all work.

Read More

MySQL Data Types

While I've been writing my tutorials for DMXzone, I've often been surprised at how many support queries I get from people who have difficulty because they're choosing the wrong datatype for their database columns. That's one of the negatives about Dreamweaver's power: it can insulate you from the nitty gritty of data too much sometimes. So, following several requests, I've put together a tutorial on the different ways to store data in MySQL. Even if you think you're an intermediate DW/ PHP developer, you might learn something ..  

Read More

SQLite 2: The Killer Features (and the sadly lacking)

As we saw last week, SQLite  is an SQL database engine that will be supported in PHP5 and which can be embedded directly into your applications. 

In this second tutorial, Allan looks at the killer features- temporary "in memory" databases and triggers which can "cascade" one change throughout the database, and looks at ways around the most important feature yet-to-be-implemented feature,  the lack of an ALTER TABLE function.

If you've ever despaired at not being able to include database functionality in your PHP pages because the machine you're using as a web server doesn't have MySQL installed, or if you ever need the features of the SQL language without having to connect to database servers, then this article is for you.

Read More