utf-8 into MySQL and ticket subject

[mrdebian]

Hello,

I modified the class.misc.php as described in the forum to allow utf-8. Tickets are coming fine in utf-8 apart from subject which displays ?????? insteed of proper characters.
Also data into MySQL are stored wrong.

Any suggestions?

Thanks

[mrdebian]

I figure out the mysql problem, added
mysql_query("SET NAMES 'UTF8'");

in functions under mysql.php and worked nicely.

The problem remains with the subject.
Can anyone help please.

Thanks

[Zoidberg]

is this what you did?

include/mysql.php, line 45

// execute sql query
function db_query($query, $database="",$conn=""){
global $cfg;
mysql_query("SET NAMES 'UTF8'");
if($conn){ /* connection is provided*/
$response=($database)?mysql_db_query($database,$qu ery,$conn):mysql_query($query,$conn);
}else{
$response=($database)?mysql_db_query($database,$qu ery):mysql_query($query);
}

if(!$response && (is_object($cfg) && $cfg->alertONSQLError())) { //error reporting
$msg='['.$query.'] - '.db_error();
Misc::alertAdmin('DB Error',$msg);
//echo $msg; #uncomment during debuging or dev.
}
return $response;
}

[mrdebian]

yes along with another function on the same file.

[Zoidberg]

You also changed include/mysql.php, Line 63

function db_squery($query){ //smart db query...utilizing args and sprintf
mysql_query("SET NAMES 'UTF8'");
$args = func_get_args();
$query = array_shift($args);
$query = str_replace("?", "%s", $query);
$args = array_map('db_real_escape', $args);
array_unshift($args,$query);
$query = call_user_func_array('sprintf',$args);
return db_query($query);
}

[Zoidberg]

Thank You mrdebian for supplying a MySQL UTF8 Fix. UTF8 Encoding is a known bug (http://www.osticket.com/forums/project.php?issueid=38).

Tickets are coming fine in utf-8 apart from subject which displays ?????? insteed of proper characters.

You might want to try editing include/class.pop3.php, approx. line 202

function createTicket($mid,$emailid=0){
global $cfg;

$mailinfo=$this->getHeaderInfo($mid);
$var['name']=imap_utf8($mailinfo['from']['name']);
$var['email']=$mailinfo['from']['email'];
$var['subject']=$mailinfo['subject']?imap_utf8($mailinfo['subject']):'[No Subject]';
$var['message']=Format::stripEmptyLines(imap_utf8($this->getBody($mid)));

[mrdebian]

Your suggestion does not work as subject is already utf-8 but not work for some reason. It might be another file.

Thanks

[Zoidberg]

The above fix works for POP3 fetched emails. Do You use piping instead?

[aria_cop]

Hi ,

For complete fix , change the collate and character sets of folowing tables from latin_general_ci to utf8_bin in mysql.

This fix works on all email piping and pop3 fetch and open tickets and all ost features like support sustem title , department, email templates, ... and wants no change in many files.

add below texts to includes/class.misc.php sendmail function after if($xheaders) $headers .= $xheaders;:
$headers .="Content-Type: text/plain; charset=utf-8 \n";
$headers .= "Content-Transfer-Encoding: 8bit\r\n";



I alter tables and it works.

save below text as a .php file e.g(test.php) and upload to your host , then run it. (http://www.your-domain.com/test.php)


<?php
#your database settings , same as osticket databse settings.
mysql_connect(server_name, username, password);
mysql_select_db(database_name);

# Don't forget to change ost_ in below text to the table prefix you select in ost installation

$qt = array("ALTER TABLE `ost_email_template` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_email_template` CHANGE `name` `name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_autoresp_subj` `ticket_autoresp_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_autoresp_body` `ticket_autoresp_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_alert_subj` `ticket_alert_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_alert_body` `ticket_alert_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_autoresp_subj` `message_autoresp_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_autoresp_body` `message_autoresp_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_alert_subj` `message_alert_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `message_alert_body` `message_alert_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `assigned_alert_subj` `assigned_alert_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `assigned_alert_body` `assigned_alert_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overdue_subj` `ticket_overdue_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overdue_body` `ticket_overdue_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overlimit_subj` `ticket_overlimit_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_overlimit_body` `ticket_overlimit_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_reply_subj` `ticket_reply_subj` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `ticket_reply_body` `ticket_reply_body` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_help_topic` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_help_topic` CHANGE `topic` `topic` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket_message` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_ticket_message` CHANGE `message` `message` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_kb_premade` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin","ALTER TABLE `ost_kb_premade` CHANGE `title` `title` VARCHAR( 125 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket` CHANGE `subject` `subject` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '[no subject]'","ALTER TABLE `ost_ticket` CHANGE `name` `name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket_response` CHANGE `staff_name` `staff_name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `response` `response` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_groups` CHANGE `group_name` `group_name` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `dept_access` `dept_access` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_department` CHANGE `dept_name` `dept_name` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `dept_signature` `dept_signature` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_config` CHANGE `helpdesk_title` `helpdesk_title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'osTicket Support Ticket System'","ALTER TABLE `ost_email_banlist` CHANGE `submitter` `submitter` VARCHAR( 126 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_kb_premade` CHANGE `title` `title` VARCHAR( 125 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `answer` `answer` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_staff` CHANGE `username` `username` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `firstname` `firstname` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
CHANGE `lastname` `lastname` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
CHANGE `phone` `phone` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `phone_ext` `phone_ext` VARCHAR( 6 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
CHANGE `mobile` `mobile` VARCHAR( 24 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `signature` `signature` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL","ALTER TABLE `ost_ticket` CHANGE `phone` `phone` VARCHAR( 16 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL","ALTER TABLE `ost_ticket_message` CHANGE `headers` `headers` TEXT CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ","ALTER TABLE `ost_ticket_note` CHANGE `source` `source` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `title` `title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'Generic Intermal Notes',
CHANGE `note` `note` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ","ALTER TABLE `ost_ticket_priority` CHANGE `priority` `priority` VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
CHANGE `priority_desc` `priority_desc` VARCHAR( 30 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ","ALTER TABLE `ost_timezone` CHANGE `timezone` `timezone` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ");

$i=0;
while ($qt[$i]) {
$rep = mysql_query($qt[$i]);
if ($rep) {
echo "<font color='#009900'>" . $qt[$i] . "</font><br /><br />";
} else {
echo "<font color='#990000'>" . $qt[$i] . "</font><br /><br />";
}
$i++;
}
?>

Cool :cool: