Log

Interesting PHP MySQL tips.

Some comments:

2. Always prepend the table name to the field. E.g. ProductName, SupplierPostCode.

This refers to field name aliasing, not to actual field names. He doesn't suggest that you actually name your fields like that in the table, but only when you select them: SELECT product.name AS product_name instead of SELECT product.product_name. The only real reason I can think of why you'd want to do this is for easier selecting the values in the calling code. I don't see why it would make joins easier, as it's just as easy to type JOIN order ON order.product_id = product.id, IMHO.

3. Always create a primary id field with the name of the table followed by the id. e.g. ProductID

Two things I don't agree with. First, it's not always necessary to create a primary id field. You should only add an ID field if there's no unique key for that table. Second, I don't think prepending the tablename to the ID field is a good idea for primary id fields. It's not really bad either, but I like a plain 'product.id' field better, because you can instantly recognise the unique identifier field for that table. Foreign key fields, on the other hand, do feel better with their tablename prepended: order.product_id refers to product.id.

5. Use a separate logging table or transactions for logs of table updates, deletes etc.

We usually create a duplicate table for every table in the database, and name it 'tableHistory' or something like that. Then we add some metadata fields to the history table (time, user, mutationtype, etc) and place triggers on each table so that any mutation will insert the old row into the history table for that table, along with some metadata. That way, you always have a complete history of every mutation in your database. Great for auditing, plus you can create generic 'Last changed by foo on bar' functionality very easily.

Here's an interesting use of SQL: SQLChess – A tutorial on thinking in sets.

Amazingly enough, I've never had the need to find duplicate rows in a table before. But in this case, I somehow forgot to add a key to a particular table in a database and, naturally, duplicates where accidentally inserted. Of course you'll want to put a key on the table, but first you'll have to remove the duplicate rows. As is usually the case, it's pretty easy using SQL.

Suppose you have the following data in a table:

firstname    lastname    Age
----------------------------
Jane         Doe         21
John         Doe         25
John         Smith       43
Jane         Doe         55
Jane         Smith       22
John         Doe         19
Jane         Doe         21

The superkey in the above table is (for example) <firstname, lastname>. To find duplicates for this superkey, you can use the following query:

SELECT 
	COUNT(*), 
	firstname, 
	lastname
FROM Address
GROUP BY firstname, lastname
HAVING COUNT(*) > 1

This will give the following results:

COUNT(*)    firstname    lastname
---------------------------------
2           John         Doe
3           Jane         Doe

This means the combination 'John' + 'Doe' appears two times and 'Jane' + 'Doe' appears thrice. People with a little SQL experience will find this straightforward, but I'll explain anyway:

We select a "COUNT(*)" in order to count all the rows, plus we select firstname and lastname fields so we can see which are actually the duplicates. We use the fields firstname and lastname on the GROUP BY clause because that's our superkey. GROUP BY will tell aggregate functions (COUNT(), SUM(), etc) in the SELECT clause that it should count grouped by certain fields, not just all the results returned. Basically, it counts all the unique values of a concatenation of firstname + lastname. The HAVING COUNT(*) > 1 is purely to weed out stuff that's not duplicate.

I'm not quite sure how to automatically delete duplicates while leaving one in place though ;-)

Update: I do now! Here's how to delete duplicates in place: SQL: Remove duplicte rows

Are you suffering from mysteriously failing insert queries due to foreign key constraints errors in MySQL?

Have you noticed spontaneous table storage engine changes from InnoDB to MyISAM, but can't figure out why they're happening?

You may be coming under the influence of the MySQL Command Center which, when altering a table via the Edit table interface, doesn't load the correct table type in the type dropdown, but defaults to MyISAM. This causes each table change you make to revert the table to MyISAM if no foreign key constraints are present in the table. Otherwise, the edit simply fails.

When editing a table, check the 'Table properties' tab before saving your changes. It'll save you a lot of headaches.

For a colleague of mine:

RSS2MySQL v0.1.

A Small script which reads in an RSS feed, takes the news items and then puts them in a MySQL database.

I added a self-made MySQL SQL syntax Quick Reference to the articles section.

It has information, taken from the online MySQL reference manual, on Data definition (CREATE and ALTER TABLE) and Data manipulation (INSERT, UPDATE, DELETE, SELECT) syntaxis.

For now the HTML version is pretty crappy. This is because OpenOffice's HTML export isn't showing background colors for paragraphs and also doesn't format the pages in columns for some reason. Please use the PDF version until I fix the HTML version (which will be about .. oh say.. never).