Strange characters when importing text file into database?

[adamjblakey]

Hi,

I have a strange issue, i have a txt file on the server with the following text in:

In what ways has 'time-space compression' transformed geographies of consumption and production?

I am using the following to read the document:

    $file = "test.txt"; 
    header('Content-Type: text/html; charset=utf-8');
    $description = file_get_contents($file,0,NULL,0,1500);
 

If i print this out to the page it works fine, however when i do the following:

mysql_query("INSERT INTO `documents` (description) VALUES ('" . $description . "')") or trigger_error(mysql_error(),E_USER_ERROR);

The following is added to the database:

In what ways has â€˜time-space compression’ transformed geographies of consumption and production?

Any ideas why this is happening?

Cheers,
Adam

[JKing]

Try using mysql_real_escape_string() on your $description variable before passing it to the database.

[HaLo2FrEeEk]

Two things, you don't need to use all of the parameters in file_get_contents. If you just want to read the entire file, just do this:

$file = file_get_contents("filename");

Also, this is an issue with a difference between the character set of the text file and the character set of the page printing the mysql output. I had a similar issue where the return from JSON API was printing apostrophe characters as ‘. I figured out that it was because the JSON used the UTF-8 charset and my page wasn't. Simply sending a Content-Type header and setting the charset doesn't change the charset of the text file.

[adamjblakey]

Thank you for all your replies. I tried using mysql_real_escape_string before being inserted but this did not work. Also in regards to the character set do you know how i can resolve this. The txt file is already present on the server, is there a way to open this and read while setting the character set?

[HaLo2FrEeEk]

Can you send me the text file? I'd like to look at it in a hex editor. You could try using file_get_contents() to read the file, then echo it out (without doing any database inserts). See if the odd characters show up. You should have just this code:

<?php
$file = file_get_contents("test.txt");
echo $file;
?>

Try that and see what happens. I'd still like to take a look at the text file though.

[adamjblakey]

Hi Thanks for the fast reply, i have attached the file for you to have a look at. When i print the file to the page it comes out fine. It is just the process of adding the file to the database the problem occurs.

226498549.txt

[HaLo2FrEeEk]

Ok, I work til 10 central time tonight, but I'll take a look when I get home. It might have to do with the character encoding of the actual database table. If you have access to phpMyAdmiin, do the insert then look at the row's contents. I'll see what I can figure out when I get home.

[adamjblakey]

Thanks very much it is much appreciated.

[HaLo2FrEeEk]

Dude, I apologize for how long it took me to get to this. I've been super tired coming home from work lately and haven't gotten much done.

Anyway, the first thing I notice when I look at the file is that it is UTF-8 encoded, all of the text files I went through on my computer are ANSI encoded. UTF-8 should still be what you want though. The next thing is that the apostrophes aren't really apostrophes at all, they're grave and acute accents. an apostrophe is a single, vertical line: ', a accents look similar, but the're slanted: ` and ´. This could very well be what's causing the problems.

I went ahead and set up a test table in my database and inserted just the first few sentences of the text file. I echoed out the text directly from the file_get_contents() call and at first it had the weirdness, but then I added the header to set the charset and it worked. I inserted it into the database and looked at it in phpMyAdmin and the strange characters were there, but when I pulled it out and echoed it again, it was how it should be, with the accents. I think that the character are alwys going to get messed up when you put them in the database, that's just their unicode representation, but ifyou set the charset to utf-8 on any page where you print the output from the database, it should be fine.

[adamjblakey]

Thanks a lot for that, just put the header on the pages that the description was printed and works fine. Thanks again.