Archive of posts tagged MySQL

Windows 7 + IIS 7.5 + php 5.3.1 + MySQL 5.1: no good combination

Posted by on

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.

Emulating ADD_TO_SET and REMOVE_FROM_SET in MySQL

Posted by on

I regularly use the SET data type in MySQL. The SET data type is ideal to store multiple values in a column. As an example, you can have a column in a table that stores possible ways to go to work. The column could be defined as SET(‘car’,’public_transport’,’bike’,’walk’). The nice thing about a SET column is that is can contain multiple values, so somebody that typically goes to work by car, but sometimes comes to work by bike, can have the value ‘car,walk’.

You can read more about the use of the SET data type on the MySQL website. The article contains some hints on how to add and remove values from a SET, but as Beat Vontobel points out in a post on his website, there are some issues with those approaches. He also describes a better solution, which leads to creating stored function for ADD_TO_SET and REMOVE_FROM_SET.

My internet provider does not allow stored functions, but you can mimic the behavior by simply using his proposed solution directly into the UPDATE statement.

ADD_TO_SET:

UPDATE set_test SET myset = 
CONCAT_WS(',', IF(myset = '', NULL, myset), 'value')
WHERE ...

REMOVE_FROM_SET:

UPDATE set_test SET myset =
TRIM(BOTH ',' FROM REPLACE(CONCAT(',', myset, ','),
CONCAT(',', 'value', ','), ','))
WHERE ...

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

Posted by on

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.