Amyst Design Company, web and print design and development services

Just a very quick little snippet that will allow you to SELECT the lowest and highest value from a single field in a database. For example, in a recent project I had to ensure that I was not deleting the first or last record status history record for a given project in a database table. To do this I came up with the following:

SELECT
	MIN(status_history_id) as first_id,
	MAX(status_history_id) as last_id
FROM table_status_history
WHERE ( project_id = "some_unique_id" )
GROUP BY project_id;

Essentially, this selects all status history records from the table for a specific project, then we group them all together and select only the max and min from the ID field and return those as (2) ad-hoc fields.

How many of us have had to increment a MySQL field and done a select query to get the current value, use PHP or another language to increment it, then turn right back around and run an update query.

This little snippet of code will save you two of those steps, and save precious time doing it.

MySQL, being as beautifully simple and functional as it is, will let you increment a field via SQL, no reason to use another language to do it. The snippet below is the query in it its simplest terms.

UPDATE
    table_name
SET
    field_to_increment = field_to_increment + 1
WHERE
   ( id_field =  'some_id' );