Explore the Premium Content

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

Beginner's SQL: Aggregates and Grouping

When you create a database-driven Web site, you will encounter a number of common data retrieval scenarios. Simple lists, header/detail listings, row difference calculations, and reports with subtotals can all be satisfied using fairly basic SELECT queries. But when you move on to functions that aggregate data - to find sums, averages, counts and the like, there's a few gotchas that can baffle the newbie and trip up even the experienced developer.

Wave goodbye to Aggregate and groupings gotcha misery with Rudy's easy-to-follow tutorial on some of SQL's powerful aggregating functions!

Read More

SQLite: new PHP-bundled database

SQLite  is an SQL database engine that will be supported in PHP5 and which can be embedded directly into your applications. Because the engine works with a single data file, this means that you can use full SQL functionality without having to install an SQL server. 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.

Since most PHP people are familiar with MySQL, we'll show you how it differs from that database. We'll then take a look at how to enable SQLite in the new PHP 5, and with a little bit more effort, PHP 4.3.x. With that out of the way we'll get down to the important stuff of using SQLite.

Read More

Advanced User-Customised Content

In the last article we took a look at how we could make a very simple system to display content in categories that the user is interested in. By default, the user sees everything, but can amend  their preferences to select categories that (s)he does not wish to see.

The only drawback to this system was that we could only classify an article under a single category or topic, thereby having to create over-generalised topics so that we could accurately classify the articles.  So instead of having topics for Linux, Windows and Macintosh OS X, we had to have a catch-all category of Operating Systems, in case an article covered more than one type of operating system.

In today's article we'll alter the structure of our database and change the admin pages so that we can give an article more than one topic.  We'll then take a look at how this affects the code that we wrote to display the articles on the main page.

Read More

Beginner's SQL: Loop The Output

In this second article in the new Beginner's SQL series, a common performance problem is solved. When a database-driven Web page runs a query to retrieve some data, and then uses a value from each row in the result to run an additional query, this "query in a loop" requires excessive and unnecessary database overhead. More importantly, the total time to complete the database retrievals, and thus the resulting Web page download time, balloons. In this article, four examples are shown which "loop the output" and not the query: simple lists, header/detail listings, row differences, and subtotals. Read More

Building content based on user profiles

If you’re building a web site that provides different kinds of dynamic content to the users of your web site, a nice feature is to let your users decide what types of content they want to see. A good example of this is a site like slashdot.org – every day they will post links to articles that cover a wide variety of subjects, and in the preferences you can decide what kinds of articles you want to see.

The way that their system works is an opt-out solution. By default you will get all articles of every topic displayed on the main page. You can then go into a preferences page and there select article topics that you are not interested in.

 

We build up such a system today; it's simple to follow and the code is provided.

Read More