Sql

Quick Merge in MySQL

22 Aug 2006

This is just a quick tip. If you have a bunch of data that you need to concatenate, take a look at MySQL's GROUP_CONCAT function. For example, way back when, I wrote about how to add tagging (and subsequently weighting those tags) to an application. It was a very simple approach with each record having a tags field. It was just a simple text field and I stored all the tags with spaces. However, t...

Read it all »

Minimize Your Database Connection Time

15 Aug 2006

One of the techniques that I learned and used way back when I did mostly ASP development was to: connect to the database only when I needed to, retreive only (and all of) what I needed, and then disconnect from the database as soon as possible. In working with ASP and MS SQL Server, following the three steps was easy. I'd open my connection and then build my query to return one or more records...

Read it all »

Selecting Fields With the Same Name

12 Jul 2006

I had two people ask me the same question today so I thought I'd share this. The dilemma occurs when joining two or more tables where a field name is the same in those tables. For example, you have a blog table with an id field and a comments table with an id field. If you just did a SELECT * while joining those two tables, you can't use the table identifier to grab the field name (E.g: $rs['blog....

Read it all »

What's the best MySQL admin tool?

13 Jan 2006

I've used a few different ones and as great as phpMySQL is, it's not always the most practical option. Wish list: runs on Windows can run multiple commands at once be able to do schema or data import and export be able to return a table and edit in place code colouring and code completion are nice to have's but definitely not necessary My demands aren't much but I've been disappointed by the s...

Read it all »

Database Joins

21 Aug 2005

As somewhat of a follow-up to my previous post, Introduction to Developing a Relational Database, I’ll touch on the exciting subject of joining tables together. This should help tie in some of the “relational” aspects of the relational database. Let’s start off with a quick example using the blog I used in the previous article. In this example, you need to display a list o...

Read it all »

Introduction to Developing a Relational Database

02 Aug 2005

Creating database tables and fields can be a fairly straightforward process and even intuitive with a few key steps. Technical database developers tend to use terms like entities (of which come acronyms like ERD which is an Entity Relationship Diagram). I prefer using tables and fields. A table is your entity or your object and a field is a property of that object. When first thinking of what t...

Read it all »

ADO error 800a01fb

23 Aug 2004

I ran into this error when working on an ASP/SQL Server application. It was intermittent and would occur at random parts of the code. The part that was consistent was that it always occurred when trying to connect to the database. A reboot of the server seems to have solved the problem. Trying to google for a solution proved that everybody else hadn't a clue what was going on either. In testing a...

Read it all »

Doing SQL searches on accented characters

19 Jul 2004

Here's how to force a collation type, in this particular case, it'll ignore case and accents when doing a search in MS SQL Server: SELECT column1 FROM Table1 WHERE column1 = 'user' COLLATE SQL_Latin1_General_Cp850_CI_AI ORDER BY column1 COLLATE SQL_Latin1_General_Cp850_CI_AI An article on DBAzine.com goes into detail on this and has all the info you need. Also, be sure to check out the TSQL refe...

Read it all »

Using NEWID to Randomly Sort Records

16 Feb 2004

There's an easy way to Randomly Sort Records in SQL7 (on Windows 2000) and SQL2000. Using the NEWID() function will automatically generate a random UID. To use it to randomly sort records: SELECT * FROM mytable ORDER BY NEWID(); To randomly select a record from a table: SELECT TOP 1 * FROM mytable ORDER BY NEWID(); A very handy trick indeed. ...

Read it all »

ON DELETE CASCADE in Transact-SQL

28 Oct 2003

Most of the database programming I've done is in MS SQL 2000. When working in SQL 7, though, the ON DELETE CASCADE feature of creating a table doesn't exist. I suspect you could make a trigger to handle this or you could try out a stored procedure to do the trick. I haven't tried it myself and I'd think a trigger would be more intuitive but it's there if I need it. ...

Read it all »