Western European language mysql query problem

Question

Hey

I am programming a webpage where the user can upload galleries of his own. When doing so the system creates a subfolder for the images with the same name as the gallery, a reference to the gallery is added as a row in a table and a separate table is also made for the gallery, containing a list of images.

When I try to create a new gallery everything works fine as long as there are no Western European signs. If I for instance name the gallery "beär" I will get an error, "Table 'databasename.beär...' doesn't exist.

The table does however exist with the correct name, I have tried printing out the variable $sub (That represents the gallery name in the query) right before the mysql query that returns an error, it prints correctly.

Also tried mysql_query("SET NAMES 'latin1'", $connection))) and the same with UTF-8 latin1 does nothing, UTF-8 screws everything over even more (The gallery name will actually then be beär).

The text files are encoded in ISO 8859-1, the charset definition is also ISO 8859-1

this is the query:

if(!($result = @ mysql_query("SELECT * FROM {$database}.`{$sub}`
            ORDER BY item_id DESC", $connection)))
        {showerror();}

Any help is greatly appreciated

Answer 1

Table 'databasename.beär...

You are creating a database table by the name of your gallery. That will not play nicely with special characters in any encoding!

Correction: According to the docs, it is indeed possible to use any alphanumeric character from the current character set! In that case, you need to change your input. Looking at the error message, the ä in Beär is being served as a UTF-8 character. You need to fix that. Is this taking place in an Ajax request? Those are encoded as UTF-8 by default.

In any case, I would feel uneasy using special characters in table names. I wouldn't do it.

Also, creating per-user tables is usually a symptom of a fundamental design mistake. Why not keep all records in one table, identifying the user through a column?