1

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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
bretterer
  • 5,693
  • 5
  • 32
  • 53

2 Answers2

2

Shouldn't that be

SELECT
m.memberID, m.zoneCode, m.state, m.zone, m.countyName,
CONCAT(m.state,'Z',m.zone) as fullZoneCode,
c.state, c.zone, c.countyName
FROM members_zonesToWatch m
LEFT JOIN countyPublicForcastZoneCorrelation c
ON c.zoneCode = CONCAT(m.state, 'Z', m.zone)
No'am Newman
  • 6,395
  • 5
  • 38
  • 50
  • That gives me `Fatal error: Problem preparing query (SELECT m.memberID, m.zoneCode, m.state, m.zone, m.countyName, CONCAT(m.state,'Z',m.zone) as fullZoneCode, c.state, c.zone, c.countyName FROM members_zonesToWatch m LEFT JOIN countyPublicForcastZoneCorrelation c ON c.zoneCode = CONCAT(m.state, 'Z', m.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',m.zone) as fullZoneCode, c.state, c.zone, c.countyName FROM members_' at line 3` and now does not work in mysql workbench. Says unknown col m.state – bretterer Jul 07 '12 at 17:14
  • It seems then that you can't use CONCAT as one of the parameters of the ON statement – No'am Newman Jul 07 '12 at 18:13
  • Is that a limitation of the class or mysqli? This works just fine with my old MySQL class. – bretterer Jul 08 '12 at 02:09
  • @bretterer: "Unknown col m.state"? Is there a field 'state' in the table 'members_zonestowatch'? I would start anew with a query having one field and one table; add the necessary fields from that table until everything is ok, then add the second query in the same manner. – No'am Newman Jul 09 '12 at 05:05
  • @Noam-newman: That was it... it should be just c.state, c.zone,c.countyName and the concat's should be c's as well... Thanks! HOWEVER, it still does not work within the mysqli class. I still get the same error – bretterer Jul 09 '12 at 14:30
  • @bretterer: Post the query as it stands now and the error message. I don't know anything about mysqli classes but I know SQL. Try putting in the mysqli class a simple query and build that up until the query works. – No'am Newman Jul 13 '12 at 14:22
2

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;

Hausen Zheng
  • 204
  • 1
  • 7