3

Using this stack question here, I tried to utilize collate and binary and am still getting the following error:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

Here is my MySQL query. Is there anyway to solve this within the query? I do not have access to alter the database or table, only to query it with SELECT.

SELECT SQL_CALC_FOUND_ROWS
    Land.ListingAgentID,
    Land.StreetNumber,
    Land.PostalCode,
    Agent.FirstName,
    Agent.LastName,
    Agent.Email
FROM
    Land
        INNER JOIN
    Agent ON (Land.ListingAgentID = Agent.AgentID)
WHERE
    ListingID = `$MLNumber`;
Community
  • 1
  • 1
Rocco The Taco
  • 3,695
  • 13
  • 46
  • 79

2 Answers2

7

You can cast on the fly, which is slow. On the long view, you should use integers for joins.

INNER JOIN
Agent ON (Land.ListingAgentID = Agent.AgentID COLLATE utf8_general_ci )

Great answer and explanation here: Troubleshooting "Illegal mix of collations" error in mysql

Community
  • 1
  • 1
Michel Feldheim
  • 17,625
  • 5
  • 60
  • 77
1

Don't use backticks `

Use quotes:

WHERE
    ListingID = '$MLNumber';
Naftali
  • 144,921
  • 39
  • 244
  • 303
  • Bummer, I tried with and without quotes and still get the same problem, what do you think? – Rocco The Taco Apr 26 '13 at 12:56
  • 1
    @RoccoTheTaco [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). – Naftali Apr 26 '13 at 13:18
  • Thanks Neal, yes I'm working hard to covert everything...you provided some good links, thanks for the info! – Rocco The Taco Apr 26 '13 at 17:48