Category Archives: Databases

April 23, 2008

INSERT or UPDATE in one statement with MySQL– also posted in ColdFusion

A little while ago I mentioned combining multiple INSERT statements with MySQL, here is another helpful approach to a common problem.
In many systems you will give the user a chance to ADD and EDIT a record, a News post for example. As usual there are many ways to handle writing any changes to the database, [...]

April 3, 2008

Combining multiple INSERT statements with MySQL– also posted in ColdFusion

As all developers know, one of the most common bottlenecks in a web application is the database. Database optimisation is therefore a huge part of building a streamlined application capable of handling more concurrent users.
Often you might have the need to perform multiple INSERT’s against a database from a single user form submission.
Let’s say you [...]

March 21, 2008

Random database results with ColdFusion– also posted in ColdFusion

Today I was looking at returning random records purely from MySQL but was limited with large tables in getting all random results (not just a random starting seed).
I tried a ColdFusion solution instead.
First get all the primary keys from the table in question and create an array of the values (this works for numeric [...]

March 21, 2008

Returning random results with MySQL

Selecting random query results is something we often need to do, whether it be a single row or a small record set. You may want to display a random list of “tips” or “facts”, a random “user” or “related product” etc.
The simplest and most common solution you find uses the MySQL rand() function (which can [...]

February 21, 2008

Using MySQL Administrator to view real time stats

I was working on piece of code today which was sending an Ajax request to the server. The result of this request could have been 0 – 5 database UPDATE statements.
What I needed was a way to test my code to make sure the correct number of database statements were being executed based on the [...]