JavaScript uses url-encoded UTF-8 strings to perform Ajax POSTs

Today, I had an encoding issue with a website I was working on. I had a

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

tag in the <head>-section, to ensure the page would correctly display and interpret all characters, including accented ones, like à and é.

The page was an administration page, to update texts on a website. Everything worked fine when I used standard <form> and <input>-tags, but I wanted to use Ajax to save any changes – to avoid a full page reload when something simple as a title had to be modified – things started to go wrong.

I used the spectacular jQuery library to do that, using a $.post-statement. At first sight, this seemed to be working fine, until I used some accented characters. When I entered “Soirée Théâtre” as title, the characters “Soirée Théâtre” were stored in the database.

I first thought it was an issue similar to the Bulgarian character set issue in mySQL I encountered a few months ago. But I was just using French in this case, so iso-8859-1 and mySQL character set “latin1_swedish_ci” should suffice in this case.

After some googling, I found this website, that explained that in the case of Ajax POSTs, “JavaScript serializes all the fields and it always uses url-encoded UTF-8 strings for this”. A simple utf8_decode in the PHP program that received the Ajax post-statement solved my issue.

Experimenting with Adobe AIR and jQuery – Part 4

For the next step in my Top100-application, I wanted to introduce some animation. In step 3, the information on the slide and the background color changed when I pressed the UP or DOWN key, but that happened immediately. I wanted to use some PowerPoint-like animation.

The jQuery UI library extends jQuery with interaction and animation options.

The first thing I tried was to make the new number pulsate a few times after it changed. I downloaded the library, added it to the code and added a pulsate effect:

<script type="text/javascript" src="jquery-1.2.6.js"></script>
<script type="text/javascript" src="jquery.ui.all.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");
        $("#number").effect("pulsate", { times:3 }, 1000);
        break;

That worked fine.

I then wanted to have the number, artist and title disappear by sliding to the right, then changing the background color and information, and then have the new information appear from the left. So I tried this:

   case 40: // down
        $("#number").hide("slide", { direction: "right" }, 2000);
        $("#artist").hide("slide", { direction: "right" }, 2000);
        $("#title").hide("slide", { direction: "right" }, 2000);
        $("body").css("background-color","#FF0000");
        $("#number").text("46");
        $("#artist").text("SASH");
        $("#title").text("ECUADOR");
        $("#number").show("slide", { direction: "left" }, 2000);
        $("#artist").show("slide", { direction: "left" }, 2000);
        $("#title").show("slide", { direction: "left" }, 2000);
        break;

And that didn’t work, or at least, not as expected. The background color and in the information changed immediately, at the same time the sliding to the right started. So for a second or so, you could already see the next number, artist and title, on a red background, while it was sliding to the right. It looked like all events happened simultaneously.

It took a while before I understood that that was exactly what was happening. When pressing the DOWN key, all events are happening at the same time. Well, almost all. The .show effect needs to wait for the .hide effect to complete, but the background color and changing of the text is happening at the same time as the .hide effect. We need a way for jQuery operations to wait until a previous event is finished.

This is probably an area that could have get a bit more attention on the jQuery documentation pages. Finally I found the solution at the detached designs website. There I learned how to “queue” animations. You need to use the callback features of jQuery. A callback is a function that is passed as an argument to another function and is executed after its parent function has completed. To make the background and text only change after the .hide effect, I needed to add this as a new function:

     $("#number").hide("slide", { direction: "right" }, 2000);
     $("#artist").hide("slide", { direction: "right" }, 2000);
     $("#title").hide("slide", { direction: "right" }, 2000, function() {
       $("body").css("background-color","#FF0000");
       $("#number").text("46");
       $("#artist").text("SASH");
       $("#title").text("ECUADOR");
     });

This did what I wanted: the text was first sliding to the right, then the background color changed (and the text changed as well, but that was not visible).

I finally added the .show effect and made the number pulsate, again only after the .show effect was finished:

 case 40: // down
    $("#number").hide("slide", { direction: "right" }, 2000);
    $("#artist").hide("slide", { direction: "right" }, 2000);
    $("#title").hide("slide", { direction: "right" }, 2000, function() {
      $("body").css("background-color","#FF0000");
      $("#number").text("46");
      $("#artist").text("SASH");
      $("#title").text("ECUADOR");
      $("#number").show("slide", { direction: "left" }, 2000) ;
      $("#artist").show("slide", { direction: "left" }, 2000);
      $("#title").show("slide", { direction: "left" }, 2000, function(){
      $("#number").effect("pulsate", { times:3 }, 1000);
    });
  });

♦ Related files: step4.html

Deleting similar records in MySQL

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]