Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf32_general_ci,IMPLICIT) for operation 'UNION'

Question

I have code for search in dbase by multiselect list when I select one choice I have this error:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf32_general_ci,IMPLICIT) for operation 'UNION'

Where is the problem?

This is my code:

<?php
$expertsearch = trim($_GET['nm']);

include("connect.php");

mysql_query("SET NAMES utf8");

$expertdata = mysql_fetch_object(mysql_query("SELECT * FROM experts WHERE ID = $expertsearch"));
$expertname = $expertdata->expert_name;

$sql = "SELECT * FROM eridb WHERE 
eridb.expert1 = $expertsearch OR eridb.expert2 = $expertsearch OR eridb.expert3 =  $expertsearch UNION

SELECT * FROM kacaredb WHERE 
kacaredb.expert1 = $expertsearch OR kacaredb.expert2 = $expertsearch OR kacaredb.expert3     = $expertsearch UNION

SELECT * FROM mwedb WHERE 
mwedb.expert1 = $expertsearch OR mwedb.expert2 = $expertsearch OR mwedb.expert3 =     $expertsearch UNION

SELECT * FROM secdb WHERE 
secdb.expert1 = $expertsearch OR secdb.expert2 = $expertsearch OR secdb.expert3 =     $expertsearch UNION

SELECT * FROM seecdb WHERE 
seecdb.expert1 = $expertsearch OR seecdb.expert2 = $expertsearch OR seecdb.expert3 =  $expertsearch ";

$res = mysql_query($sql);

echo "<table border='1'>

$count = 0;
while($row=mysql_fetch_object($res))
{
$count = $count+1;
echo "<tr>";
echo "<td>" . $count . "</td>";
echo "<td>" . $row->id . "</td>";
echo "<td>" . $row->nameofresearch . "</td>";
echo "<td>" . $row->dateofstart . "</td>";
echo "<td>" . $row->dateofend . "</td>";
echo "<td>" . $row->budget . "</td>";
echo "<td>" . $row->requestedby . "</td>";
echo "<td>" . $row->projectstatus . "</td>";
echo "<td>" . $expertname . "</td>";
echo "<td>" . $row->projectstatus . "</td>";
echo "</tr>";
}

if($res){
echo "Successful";
echo "<BR>";
}
else {
echo "ERROR";
echo "<br>";
echo mysql_error();
echo "<a href='main1.php'><br>Please try again </a>";
}
echo "</table>";
?>

Devart Devart · Accepted Answer · 2014-01-22T06:50:34

There are columns with different charset/collation that cannot be selected together, utf8 and utf32 in your case. Find these columns in your tables (run SHOW CREATE TABLE statement) and try to cast them using CONVERT function, e.g. -

SELECT CONVERT(column_name USING utf8) FROM experts
  UNION
SELECT CONVERT(column_name USING utf8) FROM eridb

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf32_general_ci,IMPLICIT) for operation 'UNION'

2 Answers