[SOLVED] Query does not work with Mysqli php class but works in MySQL workbench

Query does not work with Mysqli php class but works in MySQL workbench

I am using the mysqli db class found at github

my query looks like

$db->query('
  SELECT
    memberID,zoneCode,state,zone,countyName,
    CONCAT(state,\'Z\',zone) as fullZoneCode
  FROM members_zonesToWatch
  LEFT JOIN (
    SELECT state,zone,countyName
    FROM countyPublicForcastZoneCorrelation
  ) as zones
    ON zoneCode = CONCAT(state,\'Z\',zone)');

The issue im getting is

Fatal error: Problem preparing query 
(SELECT
  memberID,zoneCode,state,zone,countyName,
   CONCAT(state,'Z',zone) as fullZoneCode
 FROM members_zonesToWatch
 LEFT JOIN (
   SELECT state,zone,countyName
   FROM countyPublicForcastZoneCorrelation
 ) as zones
   ON zoneCode = CONCAT(state,'Z',zone)) 
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
''Z',zone) as fullZoneCode FROM members_zonesToWatch LEFT' at line 1

This query as it is works in mysql workbench just fine. What is wrong with it using the mysqli class?

Solution

After testing on your case, the problem is the 'Z' and $db->query

It is because the MysqliDB internally uses mysqli and mysqli throws the exception when prepare your statement (from the debugger, it seems the quote ' turns to ').

Instead, MysqliDB provide rawQuery for cases like yours. So use the below :

$result = $db->rawQuery('
    SELECT m.memberID, CONCAT(m.state, ? ,m.zone) as fullZoneCode
    FROM members_zonesToWatch m
    LEFT JOIN countyPublicForcastZoneCorrelation c
    ON c.zoneCode = CONCAT(m.state, ? ,m.zone)',
    array('Z','Z'));

Reference: PHP-MySQLi-Database-Class / MysqliDb.php;