mySQL syntax issue in PDO script

Question

This PDO script seems to be written correctly, but I keep receiving a syntax error with no json array being generated.

  $stmt = $pdo->query('SELECT `person`,
       sum( `stat` = "Ready" ) as Num1
        from `Table1`
        WHERE `code` = :code AND
        (`stat` = "Ready")
        group by `person`
        Order by `Num1` DESC ');

  $stmt->execute([
      'code' => $_POST['code']
      ]);

  $row = $stmt->fetchAll(PDO::FETCH_ASSOC);

  echo json_encode($row);

This is the error message I get:

Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]

stat = "Ready" or status = "Ready" (different in select and where)? — Manzolo, Aug 23 '19 at 13:22
Try to change `status` = "Ready" in `status` = 'Ready' (single quote) — Manzolo, Aug 23 '19 at 13:24
@scaisEdge Is it correct to do, `$stmt = $pdo->query` for the query and then `$stmt->execute` ? — John, Aug 23 '19 at 15:50
@scaisEdge The entire error message I get is: `Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2031 ' ` — John, Aug 23 '19 at 15:51

ScaisEdge · Accepted Answer · 2019-08-24T05:53:57.747

-1

remember that named param require : so :code and not code in execute array key

$stmt = $pdo->query('SELECT `person`,
       sum( `stat` = "Ready" ) as Num1
        from `Table1`
        WHERE `code` = :code AND
        (`stat` = "Ready")
        group by `person`
        Order by `Num1` DESC ');

  $stmt->execute([
      ':code' => $_POST['code']
      ]);

  $row = $stmt->fetchAll(PDO::FETCH_ASSOC);

and for json you can

  $myJson = json_encode($row);

edited Aug 24 '19 at 05:53

answered Aug 23 '19 at 13:22

ScaisEdge

131,976
10
91
107

Thank you, it worked, really quick question, how can make all values in the json array come out as strings? Is there any criteria I can put at the end of the PDO? – John Aug 23 '19 at 16:51
@JamieMills for json string you can use json_encode($row) and well if my answer is right please (past 15 minutes) mark it as accepted ...see how here http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work .. answer updated for json – ScaisEdge Aug 23 '19 at 16:52
I do json_encode($row); but even if my SELECT query selects numbers I want them to have double quotes around them. **Example:** `" 2 "` not `2` – John Aug 23 '19 at 16:55
why downvote ???' .. json is standard .. so the number have not double quote .. only the string have double quote – ScaisEdge Aug 23 '19 at 16:55
Can I force the numbers to also be double quote? – John Aug 23 '19 at 16:57
is not JSON .. is not a valid JSON format .. anyway you could check this link https://stackoverflow.com/questions/33564078/php-add-double-quotes-before-and-after-numbers-in-json-string – ScaisEdge Aug 23 '19 at 16:58

mySQL syntax issue in PDO script

1 Answers1