Ian Gilfillan

This tutorial briefly explains the concepts of referential integrity, and looks at how MySQL enforces them with its definition of foreign keys. You should be using a stable version of MySQL 4 to follow all the examples, although some examples may work with earlier versions running InnoDB tables. What is referential integrity? Simply put, referential […]

Caching queries Last month we took a brief look at ADOdb, and saw how to perform simple SELECT, INSERT and UPDATE queries. If you’re new to ADOdb, I suggest you read that article first. ADOdb has numerous features that are more advanced too, and we look at some of them this month. The database is […]

Why use a database class library? A common beginner (and not so beginner) mistake is to develop an application without any consideration of the future. It’s not impossible that one day the database you use today may be inappropriate, and you need to rewrite your application to use another database. But the PHP functions don’t […]

MySQL Roadmap MySQL has come along by leaps and bounds, and the new version 4.0 is barely recognizable when compared with its earlier siblings. I first started using MySQL 3.22, when it was very much a toy, used version 3.23 extensively for websites, but version 4.0 and beyond promises much more. This article is a […]

The problem Something is not right. Your beautifully designed application is behaving oddly. After some investigation, you discover a problem in the code logic, and realise the database table contains duplicate rows. And now you have to delete them. Before I show you how to do this in MySQL, take a step back. Why does […]

Last time we looked at a few of the more commonly used date and time functions. This time we’re going to look at some of the less well-known functions. First, we will need to understand the various date and time types MySQL accepts for passing to many of these functions. SECOND ss MINUTE mm HOUR […]

MySQL Date and Time Types There are a number of useful date and time functions in MySQL. I’ve seen too many applications performing date calculations at the code level when the same can be done using built-in MySQL functions. Before we launch into the functions, however, let’s refresh our memory and look at which date […]

The Error Log The error log, as the name indicates, logs any MySQL errors that occur while the server is running, as well as startup and shutdown data. This includes data about invalid passwords, as well as syntax errors in the configuration file (my.cnf or my.ini) which can help when a startup fails. The error […]

The slow query log is one of the less-used logs, as by default it’s not activated, but it’s a useful log for identifying queries that are not optimal. Often, in an under-performing system, especially with the default MyISAM tables (that make use of table-level locking, not row-level locking), a single query may be the cause […]

Part 1 of this article looked at the fulltext index, and how to search on it using an ordinary MATCH() AGAINST(). Even more powerful, (although only available on the newer MySQL version 4), is the ability to do a boolean search. Part 2 of this article examines the possibilities. You’ll use the same table you […]

One of the more useful MySQL features is the ability to search for text using a FULLTEXT index. Currently this is only available if you use the MyISAM table type (which is the default table type, so if you don’t know what table type you’re using, it’ll most likely be MyISAM). A fulltext index can […]

It’s time to add some dynamic content to your website. The best choice for ease-of-use, price and support is the combination of PHP and MySQL. This article introduces the beginner to using MySQL with PHP.   Download the files used in this tutorial here A beginners guide to using MySQL with PHP This article assumes […]