Archive for the ‘MySQL’ Category
Windows 7 + IIS 7.5 + php 5.3.1 + MySQL 5.1: no good combination
I recently installed Windows 7 on my laptop. I also configured IIS 7.5 on it, installed php 5.3.1 and MySQL 5.1.
Everything seemed to be installed correctly: I could see the IIS 7 Welcome screen, I could look at the php configuration with phpinfo() and I could see the MySQL tables using the MySQL Administrator GUI.
But when I tried to use phpMyAdmin, I got stuck: after trying to load the homepage of phpMyAdmin for 20-30 seconds, I got an HTTP Error 500. I searched the internet for hours, trying to find somebody with a similar experience, but I did not find any valuable resources.
Even a very simple test-script like
<?php
echo "#1. Try to connect to mysql server...";
mysql_connect("localhost", "username", "password") or die(mysql_error());
echo "Connected to MySQL<br />";
?>
generated a HTTP 500-error, after a 20-30 seconds wait.
Eventually, I decided to look at what I had installed when I was still using Vista. The difference seemed to be php: on my previous system, php 5.2.10 was installed.
I finally uninstalled MySQL and php 5.3.1, and then reinstalled php 5.2.11 and MySQL 5.1.41. That combination seemed to work without issues.
Conclusion: IIS 7.5 + php 5.3.1 + MySQL 5.1 does not seem to be a workable solution (for now). Stick to the 5.2-branch of php if you want to use it on Windows 7/IIS 7.5.
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.
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:
| title | artist | cd | track |
|---|---|---|---|
| Amazing | Seal | 317 | 3 |
| Crazy | Seal | 128 | 12 |
| Crazy | Seal | 287 | 16 |
| Don't You | Simple Minds | 152 | 5 |
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
| title | artist |
|---|---|
| Amazing | Seal |
| Crazy | Seal |
| Don't You | Simple Minds |
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
| title | artist | cd | track |
|---|---|---|---|
| Amazing | Seal | 317 | 3 |
| Crazy | Seal | 128 | 12 |
| Don't You | Simple Minds | 152 | 5 |