DELETE with LEFT JOIN in MySQL

in MySQL using a LEFT JOIN is a simple way of find records that exist in one table but which have no corresponding records in another table. An example might be in a JOIN table that exists between two other tables e.g.

A user can have many notes and notes can themselves have many users – a HABTM relationship.

Tables

If you delete a note or a user you could end up with orphaned records in the notes_users table – to find these orpahned records you might use a query like this:

1
2
 
SELECT notes_users.* FROM notes_users LEFT JOIN notes ON notes_users.note_id = notes.id WHERE notes.id IS NULL

You would imagine that to delete all of these orphaned records you could simply use the following query:

1
2
 
DELETE FROM notes_users LEFT JOIN notes ON notes_users.note_id = notes.id WHERE notes.id IS NULL

However this won’t work – you actually need to indicate which table you want to delete the records from:

1
2
 
DELETE notes_user.* FROM notes_users LEFT JOIN notes ON notes_users.note_id = notes.id WHERE notes.id IS NULL

Yesterday in MySQL

Sometimes the simplest things just won’t stay in your head. Personally I can never remember code for date based calculations, and it easier to find what I want here than look it up on http://dev.mysql.com/doc/.

Here is a very simple way of comparing (or getting) dates:

Example delete all records older than 1 day from the notes.

1
2
 
DELETE FROM notes WHERE created <= DATE_ADD(CURDATE(), INTERVAL -1 DAY);

Read the manual entry for the MySQL DATE_ADD function.

A simple jQuery menu with persistence using cookies

Recently I’ve been making a concerted effort to learn jQuery the JavaScript framework as opposed to just using all the wonderful plugins off the shelf.

Recently I needed a bit of code to show and hide a navigation menu, but with persistence using cookies so as you move from page to page it can remember which sections to show. I was pretty confident, using bog standard JavaScript I knew I could knock it out really quickly, but The whole point of learning something new is to learn something new so I decided to do it using jQuery. I was pretty confident, last week I wrote some quite complex form validation code in a fraction of the time I could’ve done it in without using jQuery (it’s that pretty but it works well and it was my first attempt to do anything at all complex).

Getting the menu to work has been quite a struggle and I had to spend a surprising amount of time getting it to work, and judging by the posts on various blogs and groups a lot of other people have been stumped by this one too.

Anyway here is my solution, if anybody can help me simplify it further, their help would be greatly appreciated. Obviously you need to download jQuery, you will need to include the code and you will need an unordered list to act as the menu, in this example id=”#demo-menu”.

A couple of posts have been really invaluable figuring out how to do this, so credit where it’s due, thanks:

View the working example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
$(document).ready(function() {
 
		$("#demo-menu > li > a").not(":first").find("+ ul").slideUp(1);
 
		$("#demo-menu > li > a > span").text('+'); // add an indicator to the menu items to show there is a child menu
 
		$("#demo-menu > li> a").each(function() {
			toggleMenu(this);
			checkCookie(this);
		});
 
 
 
		function checkCookie(id)
			{
				/*
 
						check if there is a cookie set for a sub menu 
						if there is then show the menu
 
				*/
 
				var cookieName = id.id;
 
				var c = readCookie(cookieName);
 
				if(c === 'show') {
 
					$(id).each(function() {
 
						$(this).children("span").text('-');
						$(this).find("+ ul").slideDown('fast');
 
					});
 
				}
			}
 
		function toggleMenu(id)
			{
				$(id).click(function() {
					/*
							toggle the +/- indicators
					*/
					togglePlusMinus(this);	
 
					/*
						toggle the menu open or closed
					*/
					$(this).find("+ ul").slideToggle("fast");
 
				});
			}
 
		function togglePlusMinus(id)
			{
 
				$(id).each(function() {
 
					if($(this).find("+ ul").is(':visible'))
						{
							$(this).children("span").text('+');
							eraseCookie(this.id);
						}
					else
						{
							$(this).children("span").text('-');
							createCookie(this.id, 'show', 365);
						}
 
				});
			}
 
});
 
// cookie functions http://www.quirksmode.org/js/cookies.html
 
function createCookie(name,value,days)
	{
		if (days)
		{
			var date = new Date();
			date.setTime(date.getTime()+(days*24*60*60*1000));
			var expires = "; expires="+date.toGMTString();
		}
		else var expires = "";
		document.cookie = name+"="+value+expires+"; path=/";
	}
function readCookie(name)
	{
		var nameEQ = name + "=";
		var ca = document.cookie.split(';');
		for(var i=0;i < ca.length;i++)
		{
			var c = ca[i];
			while (c.charAt(0)==' ') c = c.substring(1,c.length);
			if (c.indexOf(nameEQ) == 0) return c.substring(nameEQ.length,c.length);
		}
		return null;
	}
function eraseCookie(name)
	{
		createCookie(name,"",-1);
	}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<ul id="demo-menu">
	<li><a href="#">item one</a></li>
	<li><a href="#">item two</a></li>
	<li><a href="#" id="three" >item three<span></span></a> 
		<ul>
			<li><a href="#">item 3.1</a></li>
			<li><a href="#">item 3.2</a></li>
			<li><a href="#">item 3.3</a>
 
			</li>
		</ul>
	</li>
	<li><a href="#">item four</a></li>
	<li><a href="#" id="five">item five <span></span></a> 
		<ul>
				<li><a href="#">item 5.1</a></li>
				<li><a href="#">item 5.2</a></li>
				<li><a href="#">item 5.3</a></li>
		</ul>
	</li>
	<li><a href="#">item six</a></li>
 
</ul>

Stained :: new work by Nick Kaplony

My friend Nick has new show coming up. It is about time too – I’ve been seeing these Rorschach things hanging about in house and his studio for years now and I’m glad they have finally come together into something exciting. I can’t wait to see them.

image 2 image 1

As its inaugural exhibition Way East presents Stained; new work by artist Nick Kaplony in his first London solo show. Kaplony’s practice is concerned with inheritance and genetic predisposition, examining the extent to which emotional, mental as well as physical traits are carried down the family line: the cause and effect of family history.

Familial relations act as a motivation for Kaplony. He focuses on his parents, using them to create photographic images that invite the viewer to consider similarities between parents and offspring. Stained is the first exhibition of Transmitter/Receiver, a new body of work incorporating photography and drawing in a series of portraits suggesting a psychological correlation between the sitters. Direct reference to the family is abandoned in a new large-scale installation work utilising the iconography of the Rorschach print. Blackness is punctuated by neon dots that seem to flash in an unknown sequence, creating a potential but ambiguous relationship between individual images.

Nick Kaplony is a graduate of Camberwell College of Arts and has shown nationally in Manchester City Gallery and Gallery 1885 in London. He is also a member of the arts collective Slowfall Projects and has worked on a number of site specific projects including Ringing; St Augustine’s Tower, Hackney and at Woodbridge Chapel, London in collaboration with the Barbican.

Way East is a new contemporary art space run by artist and writer Russell Martin.

STAINED
Nick Kaplony

Wed 30 Jan – Sat 23 Feb

Private View: Tue 29 Jan
6pm-8pm

WAY EAST
62 EASTWAY, HACKNEY WICK, LONDON E9 5JH
OPEN: SATURDAYS OR BY APPOINTMENT
Tel: 07855 352 955 Email: hello@russellmartin.org.uk
Web: http://wayeast.russellmartin.org.uk/

Streetmap location:
http://www.streetmap.co.uk/newmap.srf?x=536682&y=184956&z=1&sv=EASTWAY&st=6&
tl=Eastway,+E_9&searchp=newsearch.srf&mapp=newmap.srf