Truncating a Database Table with a Foreign Key

Server Administration

To remove all data from a table and reset its primary key you can normally run the following SQL command:

TRUNCATE TABLE MyTable;

However, if you try this on a table with one or more foreign keys, you’re going to get an error message. Normally, the way around this is to drop the constraints, truncate the table, and recreate the constraints – which is a lot of fuss.

The following does the same job, but without the fuss.

DELETE FROM MyTable;
DBCC CHECKIDENT (MyTable, RESEED, 0);

 

Identifying Custom Templates in WordPress

WordPress

Sometimes, particularly when you’ve taken over the development of an existing WordPress-based website, its handy to be able to tell which of the custom templates in the theme folder are actively in use and by which pages. The following SQL query gives you that information.

SELECT p.ID, p.post_title AS Page, p.post_status AS Status, m.meta_value AS Template
FROM wp_posts p
LEFT JOIN wp_postmeta m ON p.ID = m.post_id
WHERE m.meta_key = '_wp_page_template'
ORDER BY p.post_title

MS SQL and MD5

Server Administration

SQL Server doesn’t appear to have a nice MD5 function like MySQL does (shame), but there is still a way to do it.

DECLARE @value VARCHAR(255)
SET @value = "This is an MD5 encoded string'
SELECT SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', @value)), 3, 32)

This was useful for resetting the admin password within an instance of Moodle that had recently been migrated to SQL Server.

UPDATE [moodle].[dbo].[mdl_user] SET [password] = SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', 'newpassword')), 3, 32) WHERE [username] = 'admin'

Querying WordPress Taxonomies

Development

To produce a mega-menu for a recent WordPress project, I had a requirement to show all the terms within one taxonomy that were associated with Posts that a specific term from a second taxonomy was also associated with. Tricky. It took a little while to get my head around the problem, but I came up with a solution that did just what was needed.

if ( !function_exists( 'get_associated_terms' ) ) {
	function get_associated_terms( $taxonomy_slug, $term_id = 0, $post_type = 'post' ) {
		global $wpdb;

		$sql = "SELECT DISTINCT $wpdb->terms.term_id, $wpdb->terms.name, $wpdb->terms.slug, $wpdb->terms.term_group, $wpdb->term_taxonomy.term_taxonomy_id, $wpdb->term_taxonomy.taxonomy, $wpdb->term_taxonomy.description, $wpdb->term_taxonomy.parent, $wpdb->term_taxonomy.count
			FROM $wpdb->terms
			INNER JOIN $wpdb->term_taxonomy ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id
			INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
			INNER JOIN $wpdb->posts ON $wpdb->term_relationships.object_id = $wpdb->posts.ID
			INNER JOIN $wpdb->term_relationships tr2 ON $wpdb->posts.ID = tr2.object_id
			INNER JOIN $wpdb->term_taxonomy tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
			WHERE $wpdb->posts.post_status = 'publish' AND $wpdb->posts.post_type = %s AND $wpdb->term_taxonomy.taxonomy = %s AND tt2.term_id = %d
			ORDER BY $wpdb->terms.name";

		$safe_sql = $wpdb->prepare( $sql, $post_type, $taxonomy_slug, $term_id );
		$results = $wpdb->get_results( $safe_sql, OBJECT );

		return $results;
	}
}

Once added to the WordPress theme functions.php file or a plugin, its used in the same way you’d use one of the built-in functions, like get_terms(), except my function accepts fewer parameters. It returns an Object that can be looped through in the normal way.

$terms = get_associated_terms( 'tax1', 256 );

foreach($terms as $term) {
	echo '<a href="/tax1/' . $term->slug . '" class="' . $term->taxonomy . '">' . $term->name . '</a>';
}

The first parameter in the function is the name of the taxonomy you wish to return. The second parameter is the ID of a term within your starting taxonomy. Running my example would return an Object containing all terms within the ‘tax1’ taxonomy which are assigned to Posts that also have term 256 assigned to them.