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

Question

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?

Hi, I inserted some newlines to the query and the error message for readability. — biziclop
– biziclop, Commented Jul 7, 2012 at 5:03
Try to use " instead of \'. I know it's not different. But just try maybe works. — Hamidreza
– Hamidreza, Commented Jul 13, 2012 at 14:16

Hausen Zheng · Accepted Answer · 2012-07-14 02:27:50Z

2

+50

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;

edited Jul 14, 2012 at 2:27

answered Jul 14, 2012 at 2:00

Hausen Zheng

2041 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hausen Zheng Over a year ago

@bretterer the second parameter of rawQuery is simply an array of all used parameters

No'am Newman · Accepted Answer · 2012-07-07 05:05:45Z

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)

answered Jul 7, 2012 at 5:05

No'am Newman

6,4975 gold badges42 silver badges54 bronze badges

6 Comments

bretterer Over a year ago

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

No'am Newman Over a year ago

It seems then that you can't use CONCAT as one of the parameters of the ON statement

bretterer Over a year ago

Is that a limitation of the class or mysqli? This works just fine with my old MySQL class.

No'am Newman Over a year ago

@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.

bretterer Over a year ago

@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

|

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related