mysql

Everyday I use a database called MySQL and here is a list of stuff I've found useful about MySQL.

Select 5 most recent items from some categories

14 September 2011
mysql tips Drupal

Ever needed to build a list which "sub-selects", say, 5 items from a given list of categories? This snippet should help.

Export a single row from a database as MySQL

11 July 2011
mysql tip programming

Nathan Rambeck has a great simple snippet of how to export a single row from a table however it didn't QUITE do what I needed it to. I needed to export a variable from {variable} as I was working on an update script which pulled data from a variable and into a database schema. I got fed up of re-populating the variable data each time.

MySQL: How to upper-case words

30 September 2010
tips programming mysql How to code

 I recently needed to clean up a MySQL Table which contained people's names. Upon searching the MySQL commands, I was surprised to find there was no equivalent of PHP's ucfirst or ucwords. There were commands to convert entire strings into upper or lower case, but not just the first letter.

MySQL: Count occurrences of string

17 February 2010
tip mysql geek Drupal

Today I needed to quickly find out a list of node's which did not contain a certain token (one we use on PPOnline to insert adverts into articles). There were meant to be 2 of these tokens per article but we had noticed some only had one. Some didn't have any at all!

Now, this might already exist as a neat function somewhere, but I couldn't find it. So I wrote this little query to find the number of occurrences of a substring in a larger block of text.

I did look into using the SUBSTRING() and SUBSTRING_INDEX() commands, however I couldn't see a way of efficiently using them to find any number of occurrences. So I settled on the following…

Generate random string in MySQL

10 July 2008
security programming mysql How to geek

Have you ever wanted to generate a random string in MySQL, say for assigning a random password to a list of users? Well here is a useful tip!

How to weigh terms alphabetically

07 August 2007
programming mysql Drupal cool

Following on from my Numbering Rows in MySQL article, today I needed to populate the weight column of the term_data table for about a hundred terms so that they incremented in alphabetical order. This was, on the face of it, a very tedious task; click edit, select weight from drop-down, submit, 'goto 1'.

As I said in my Numbering Rows in MySQL article, I don't like tedious - but I do like over-complicated methods which actually make my life more interesting and less tedious... Enter a nifty MySQL script!

Ordering Menu Items Alphabetically

02 January 2007
programming mysql How to Drupal cool

Faced with a tedious task of ordering a set of rows using a weight column? Don't want to type the numbers out? Would rather find a difficult but more interesting method? I have the answer for you!

Drop all tables in a MySQL database

10 October 2006
programming mysql linux geek free cool

This morning I am faced with a task that will involve repeatedly dropping and reimporting a lot of data. MySQL has DROP TABLE and DROP DATABASE but there is no command to drop all tables or truncate the database.