-1

All, I have the following code in php where I want to select data from mysql and put into json_encode. If it contains single quotes etc it fails.

// get the notes if any
$notes = array();
$result = mysqli_query($con, "SELECT id, notes FROM tc_ssa_notes WHERE ssa_id = " . mysqli_real_escape_string($con, $ssa_id));
while ($row = mysqli_fetch_assoc($result))
    {
        $notes[] = $row;
}
mysqli_free_result($result);

#en: send data packaged in json array
echo json_encode(array('notes' => $notes));

Output of var_dump( $notes );:

array(4) {
    [0] => array(2) {
        ["id"] => string(2) "26" ["item"] => string(55) "Are electrical panels covered when not being worked on?"
    } [1] => array(2) {
        ["id"] => string(2) "28" ["item"] => string(38) "Are extension cords in good condition?"
    } [2] => array(2) {
        ["id"] => string(2) "27" ["item"] => string(39) "B. Are (GFCI�S) in use (If Applicable)?"
    } [3] => array(2) {
        ["id"] => string(2) "29" ["item"] => string(53) "Is GFCI on the generator operational? (If applicable)"
    }
}
swidmann
  • 2,787
  • 1
  • 18
  • 32
Jason Pate
  • 76
  • 7
  • Can you add an example output of `$notes`? – swidmann Dec 09 '15 at 12:27
  • how does it fail? what error message do you get? – Latheesan Dec 09 '15 at 12:32
  • What is your question? – Matheno Dec 09 '15 at 12:33
  • You mean an example of a row in notes? – Jason Pate Dec 09 '15 at 12:34
  • B. Are (GFCI’S) in use (If Applicable)? – Jason Pate Dec 09 '15 at 12:34
  • No error is generated. I am using ajax to send the ID to php and return data packed in JSON. Nothing is returned in the POST. If I turn on error checking nothing is generated. I used error_reporting(E_ALL); ini_set("display_errors", 1); – Jason Pate Dec 09 '15 at 12:36
  • please add `var_dump( $notes );` to your code, right before you try to echo the json and add this output to your answer – swidmann Dec 09 '15 at 12:38
  • All, when I remove all special characters it works fine. I would like to leave the specials in place. – Jason Pate Dec 09 '15 at 12:41
  • array(4) { [0]=> array(2) { ["id"]=> string(2) "26" ["item"]=> string(55) "Are electrical panels covered when not being worked on?" } [1]=> array(2) { ["id"]=> string(2) "28" ["item"]=> string(38) "Are extension cords in good condition?" } [2]=> array(2) { ["id"]=> string(2) "27" ["item"]=> string(39) "B. Are (GFCI�S) in use (If Applicable)?" } [3]=> array(2) { ["id"]=> string(2) "29" ["item"]=> string(53) "Is GFCI on the generator operational? (If applicable)" } } – Jason Pate Dec 09 '15 at 12:47
  • All query should read $result = mysqli_query($con, "SELECT id, item FROM tc_ssa_notes WHERE ssa_id = " . mysqli_real_escape_string($con, $ssa_id)); while ($row = mysqli_fetch_assoc($result)) – Jason Pate Dec 09 '15 at 12:51

1 Answers1

0

Due to your example output I guess you are writing to your database in the wrong encoding (at least for json_encode) maybe ISO-8859-1 or some other format.

json_encode() only works with UTF-8. From the docs:

All string data must be UTF-8 encoded.

A long term solution would be to use UTF-8 as charset/collation in your database, here is a good answer with some resources to this topic.

A short term (quickfix) could be to convert your string to UTF-8 (utf8_encode()). If your string is ISO-8859-1 encoded, you can replace your while loop with this loop below:

while ( $row = mysqli_fetch_assoc( $result ) ) {
    $row["item"] = utf8_encode( $row["item"] );// added this line
    $notes[] = $row;
}

Additionaly for debugging json errors, you can use the build in functions below (depends on PHP version):

Community
  • 1
  • 1
swidmann
  • 2,787
  • 1
  • 18
  • 32