Archive of posts filed under the Web Development category.

Deleting similar records in MySQL

Posted by on

If you have a table in MySQL with duplicate records, you can easily get a list without the duplicates by using the DISTINCT keyword in the SELECT-statement:

SELECT DISTINCT * FROM table

If you want to delete the duplicate records, you can create a new table without the duplicates, by combining the CREATE TABLE statement with SELECT DISTINCT:

CREATE TABLE newtable SELECT DISTINCT * FROM table

However, if you have similar records, where some, but not all fields are the same, the DISTINCT approach does not work. This is an excerpt of a table with songs:

[TABLE=2]

I’d like to delete the similar records, keeping just one entry for each artist/title combination. The SELECT DISTINCT statement will not work, as none of the records are exact duplicates. In other words,

SELECT DISTINCT * FROM table

will return the same table.

I could do a

SELECT DISTINCT title,artist FROM table

which results in

[TABLE=3]

but I want to keep the information in the other columns.

The solution in MySQL is to use the multiple-table syntax for the DELETE statement using the same table twice:

DELETE T1
FROM table T1, table T2
WHERE T1.duplicateField = T2.duplicateField
AND M1.uniqueField > M2.uniqueField

For the example above, I could use

DELETE M1
FROM music M1, music M2
WHERE M1.title = M2.title AND M1.artist = M2.artist
AND M1.cd*100+M1.track > M2.cd*100+M2.track

which results in

[TABLE=4]

Experimenting with Adobe AIR and jQuery – Part 3

Posted by on

This is step 3 for my Top100-application. If you haven’t read step 1 and step 2, you probably want to do that first.

In the current state, the application looked like this:

I now wanted to change the number, artist and title when I pressed a key. The idea was to show the next song in the list, when pressing the DOWN arrow.

I first focused on just changing some text in the application, without worrying about acting on a keypress. I could have played around with plain javascript, but I was intrigued by the jQuery library, which seemed to make things a lot easier. To make it simple, I just wanted to change the number from 47 to 46 when I clicked on it.

To start using jQuery, you need to download it and include it in the header of your html-document

<script type="text/javascript" src="jquery-1.2.6.min.js"></script>

Then you to insert the actual code, but that code should only run when the DOM of the document has been loaded. jQuery has a simple statement that checks the document and waits until it’s ready to be manipulated, known as the ready event. Inside that event, I needed to define what was suppose to happen when I clicked the number. The code was pretty simple:

$(document).ready(function() {
	$("#number").click(function() {
		$("#number").text("46");
		});
	});

I first previewed this in FireFox (using F12 in Dreamweaver) and it worked just fine. Clicking on the number 47 changed it into 46. Pretty cool! I then previewed it in AIR (using CRTL+SHIFT+F12 in Dreamweaver) and that worked as well. Super!

But then I created the .air file itself, and tried to run it in AIR. It failed! I could click 47 as much as I wanted, it did not get changed in 46 🙁

I spend a lot of time trying to find out what was wrong… until I realized it was something very simple. The Adobe AIR extension for Dreamweaver is not smart enough to realize that the jQuery library is an external file that needs to be loaded. So the .air file does not contain that library, and thus the code doesn’t work. There’s no error message, so it’s not obvious what’s wrong, but the solution was simple: I needed to add jquery-1.2.6.min.js to the Included files section:

Once I did that, recreated the .air file and installed it, it worked like a charm! 🙂

jQuery also includes event handlers for keypresses, so expanding this to react on the DOWN key instead of clicking on the number, was not that difficult. I extended it to also react on the UP key, and to change the background color, all using jQuery functions. The final code looked like this:

<script type="text/javascript" src="jquery-1.2.6.min.js"></script>
<script type="text/javascript">
$(document).ready(function() {
    $(document).keyup(function(e) {
        switch ( e.keyCode ) {
            case 38: // up
                $("body").css("background-color","#00AA00");
                $("#number").text("48");
                $("#artist").text("VENGABOYS");
                $("#title").text("WE LIKE TO PARTY");
                break;
            case 40: // down
                $("body").css("background-color","#FF0000");
                $("#number").text("46");
                $("#artist").text("SASH");
                $("#title").text("ECUADOR");
                break;
            }
        });
    });
</script>

Pressing the UP or DOWN key resulted in the changes I wanted:

♦ Related files: step3.html

Experimenting with Adobe AIR and jQuery – Part 2

Posted by on

The next step for my Top100-application was to make the same screen appear in an Adobe AIR application.

When you install the Adobe AIR extension for Dreamweaver, the Site menu in Dreamweaver has an additional option, called Air Application Settings. I selected that option and got the following screen:

I entered 0.1 as Version, browsed for step1.html as Initial content, and clicked the Set button for Digital Signature. That gave me the following dialog box:

Every AIR package needs to have a digital certificate. Luckily, you can create one yourself, by clicking the Create button. (Go to the Adobe AIR Developer Center for more details on signing an AIR package)

Clicking the Create AIR file gave me an error message about timestamp issues, but I could dismiss that dialog box by clicking Disable (which disables the timestamp check, but I trust myself, so I don’t need that type of security). This finally resulted in a top100.air file.

Double-clicking that file in Windows Explorer resulted in the following warning dialog box:
which did not come as a surprise, because I signed the package myself. I clicked Install, and the application showed on my desktop:

So it kinda worked… although I did not want the window border, the title, the buttons or the scrollbar.

So I needed some tweaking, which was easy enough in the Air Application Setting dialog box:

I selected Custom Chrome (opaque) for Window style and 1024×768 for Window size, changed Version to 0.1.1, created a new AIR file, double-clicked to install it, and clicked Replace to install the new version:

This finally resulted in what I wanted:

The next step was to have the application show the next song, when pressing a key. That’s for part 3 :-).