select

Whilst creating a large and complex form in Drupal 6 recently I hit upon a problem that took me a couple of hours to solve so I am posting the solution here in case anyone else gets similarly stuck. I am also writing this down so that I can remember the strategy in case I have to do the same thing again.

What I was doing was creating an event booking form. The form allowed users to select from a number of times slots in a select list, but each time had a limited number of bookings. When the user submitted the form and all allocated slots for their selected time were fully booked they were shown a message asking them to select a different time slot. The original time was removed from the select list so that the user didn't try to select it again. This was fine and it all worked as expected but when this happened I also got the following error message from Drupal.

Whilst creating a view for a Drupal site the other day I created an exposed form but rather than the usual select boxes that get displayed I needed them to be a list of check boxes. Whilst searching about on Google I eventually found this article, which kindly provides a function that will do this. Here is the function in full.

To find duplicate values you need to use the MySQL COUNT() function and then pick out all of the counts that are greater than one.

SELECT value,COUNT(value) AS Count
FROM test
GROUP BY value
HAVING (COUNT(value) > 1)
ORDER BY Count DESC;

Conversely you can also select the rows that only have a single entry.

SELECT value,COUNT(value) AS Count
FROM test
GROUP BY value
HAVING (COUNT(value) = 1)
ORDER BY Count DESC;

However, it is very nice to pick out the duplicate entries in a table, but you might still need to do something with them. Here is a query to delete any duplicate rows from a table. It does a simple self join and deletes the row value with the lowest ID.