EllisLab text mark
Advanced Search
     
Database error 1064 and charset utf-8
Posted: 14 November 2011 04:39 PM
Joined: 2011-05-26
22 posts

Hi,

I’m getting this error database error: 1064, I’ve found a “solution” but I think it’s not necessary.

The model function:

function update_favorite($data
{
 $this
->db->where('id'$data['id']);
 
$this->db->where('user_id'$data['user_id']);
 
$this->db->update('favorites'$data);
 return;

Call this function from the controler:

$this->favorites_model->update_favorite(array(
 
'id' => $id
 
'rss_last' => $rss_last,
 
'user_id' => $this->session->userdata('user_id')
)); 

When $rss_last is a “normal” value like: “test” (without quotes) it works fine.
When it’s a value with more length like (in Dutch): F-Secure vindt malware met certificaat van Maleisische overheid

I get this error:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘vindt malware met certificaat van Maleisische overheid, `user_id` = ‘1’ WHERE `i’ at line 1

UPDATE `favorites` SET `id` = ‘15’, `rss_last` = F-Secure vindt malware met certificaat van Maleisische overheid, `user_id` = ‘1’ WHERE `id` = ‘15’ AND `user_id` = ‘1’

Filename: /home/***/domains/***.nl/public_html/new/models/favorites_model.php

Line Number: 35

When I change the line

'rss_last' => $rss_last

To this:

'rss_last' => $this->db->escape_str($rss_last), 

It works fine, but now I have sometimes a ugly backslash in my database…..

What to do?

PS. I thought that CI escapes values automatically when using the Active Record class?

 
Posted: 15 November 2011 01:57 PM   [ # 1 ]   [ Rating: 0 ]
Joined: 2011-05-26
22 posts

Anybody?

Addition, running:
- CI 2.0.3
- PHP 5.2.17
- MySQL 5.1.58

 
Posted: 15 November 2011 03:20 PM   [ # 2 ]   [ Rating: 0 ]
Avatar
Joined: 2009-02-19
4322 posts

I assume your db is set to UTF-8 as well as your page encoding.

It’s possible you also might need to:

setlocale(LC_ALL'nl_NL'); 

Also might try:

'rss_last' => iconv("UTF-8""UTF-8//TRANSLIT"$rss_last
 Signature 
 
Posted: 15 November 2011 03:32 PM   [ # 3 ]   [ Rating: 0 ]
Joined: 2011-05-26
22 posts

In the database config:

$db['default']['char_set''utf8';
$db['default']['dbcollat''utf8_general_ci'

Also the collation in the database: utf8_general_ci

In the head of de pages:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 

Just tried

'rss_last' => iconv("UTF-8""UTF-8//TRANSLIT"$rss_last

Works fine! Thanks! Is this everywhere necessary or recommended?

Where I’ll have to place the setlocale? If I use the setlocale I don’t have to use the iconv?

 
Posted: 15 November 2011 03:39 PM   [ # 4 ]   [ Rating: 0 ]
Avatar
Joined: 2009-02-19
4322 posts

setlocale makes things like iconv translate into your local language, which you stated is Dutch.  You can try without using iconv and see if it works, but I don’t think it would on its own, but you never know until you try.  I’d put it as the first line of index.php.

It seems using iconv should be unnecessary if you have everything else set correctly, but I don’t use non-english languages in my apps so I haven’t really tried too much with other languages.

 Signature 
 
Posted: 15 November 2011 03:51 PM   [ # 5 ]   [ Rating: 0 ]
Joined: 2011-05-26
22 posts

Thanks again for your reply!

setlocale don’t change anything (still need iconv), just set some timezone etc right, but I keep it in my index.php, you never know where it’s good for!

Is there a alternative method for iconv? I don’t think I’ve to place iconv everywhere? Any global function to set everythink to utf-8?


Addition:
What’s the best way to set the charset global? And what to set?
- In the head:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 

- In config/config.php

$config['charset''UTF-8'

- In config/database.php

$db['default']['char_set''utf8';
$db['default']['dbcollat''utf8_general_ci'

- In .htaccess, my rewrite rules and

php_value magic_quotes_gpc Off
AddDefaultCharset UTF
-

- Also need send a header? Where to place? Something like?

header('Content-Type: text/html; charset=UTF-8'); 

- In my editor (Notepad++) save files as UTF-8? Or UTF-8 (without BOM)? Or is ANSI good (this is what I’m using now)?

- Use utf8_unicode_ci or utf8_general_ci for the MySQL database? And why?

- How about reading RSS feeds, how to handle multiple charsets? Where I’m working on I’ve two feeds, one with UTF-8 encoding and the other with ISO-8859-1. This will be stored in the database and will be compared sometimes to see if there are new items. It fails on special chars.


It’s a labyrinth for me, I hope somebody can tell me the right things.