JSON not encoding as json or UTF-8?

Question

I am having some headaches with JSON not returning as JSON. I get no errors, just no data. The browser shows the data in the response.

I know it's not returning as JSON because setting the dataType in the AJAX section causes it to display nothing. If I remove the dataType statement, it displays the data.

I've played around with encoding in the connection string, in the queries, as a header, and in the AJAX section all to no avail. My database is in UTF-8, general_ci.

AJAX:

$.ajax({

            contentType: 'application/json; charset=UTF-8',
            data: {'career' : $career},
            dataType: 'json',
            url: 'functions.php',
            success: function(data) {

                $("careerdata").html(data);

            },

PHP:

if (isset($_GET['career'])) {

    require_once 'config.php';

    $query = $dbconnect->prepare("select * from jobs where Category = :category");
    $query->bindParam(':category', $category);
    $category = $_GET['career'];
    $query->execute();

    $result = $query->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($result);

    $dbconnect = null;

    return;

} else {

    echo 'No career data found.'; 

};

Connection file:

try {

$dbconnect = new PDO("mysql:host=$host;dbname=$database", $user, $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

$dbconnect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}

catch(PDOException $e) {

echo "Connection failed: " . $e->getMessage();

};

If any more info is needed, please let me know.

Working page is https://www.shardsmith.com/career.php and the actual query is https://www.shardsmith.com/functions.php (but it won't work independently because of the GET variable).

Soo.... the first obvious question is "did you check what's in `$result` in your PHP file" because if that's empty then `json_encode($result);` is going to be just as empty. And then of course http://stackoverflow.com/questions/279170/utf-8-all-the-way-through is required reading for you in this case. — Mike 'Pomax' Kamermans, Feb 09 '17 at 22:21
It wasn't empty, it printed fine. The issue was the response the AJAX call was getting. I ended up figuring it out myself in any case. — Sarah Schaller, Feb 10 '17 at 21:50

Alex Schenkel · Answer 1 · 2017-02-09T20:35:59.477

0

It seems that the MySQL (php) client does assume another client encoding than UTF-8. You can request the correct encoding using

$dbhconnect->exec("SET NAMES utf8");

directly after you connect to the database.

Alternatively, you can use PDO::MYSQL_ATTR_INIT_COMMAND (see http://php.net/manual/de/ref.pdo-mysql.php) in your connection config:

$dbconnect = new PDO("mysql:host=$host;dbname=$database", $user, 
   $pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

And be careful with unchecked frontend parameters: this is an open door for SQL injections. Either use a prepared statement like this:

$query = $dbconnect->prepare("select * from jobs where Category = :cat");
$query->execute(array(':cat' => $_GET['career']));

or escape the input paramerers first:

$cat = $dbhconn->quote($_GET['career']);
$res = dbhconnect->query("select * form jobs where Category = {$cat}")->fetchAll();

edited Feb 09 '17 at 20:35

answered Feb 09 '17 at 20:20

Alex Schenkel

728
1
7
13

That's one of the things I tried, and just tried again, and neither of those options work. – Sarah Schaller Feb 09 '17 at 20:25
Is it possible that your SQL produces an error? Did you try dumping the PDO error info right after the query? (e.g. var_dump($dbconnect->errorInfo());) – Alex Schenkel Feb 09 '17 at 20:29
And: Why are you using a prepared statement, but not using a single prepared parameter? What if you try $dbhconnect->query(...)->fetchAll() instead? – Alex Schenkel Feb 09 '17 at 20:31
All of my reading said prepared statements were safer. That being said, I changed it to $result = $dbconnect->query("select * from jobs where Category = '" . $_GET['career'] . "'")->fetchAll(PDO::FETCH_ASSOC); json_encode($result); and now I get a parser error. Gonna tinker with it some more.. – Sarah Schaller Feb 09 '17 at 20:41
I think there is a misunderstanding - Please read about prepared statements in PHP here: http://php.net/manual/en/pdo.prepared-statements.php Prepared statements need to use some replacement / placeholder variables instead of the "real" plain string values. - See my edits in the orig text above. – Alex Schenkel Feb 09 '17 at 20:48
Changed it and it still works fine as dataType html, but not as json. I did get Requested JSON parse failed. (parsererror) array(3) { [0]=> string(5) "00000" [1]=> NULL [2]=> NULL } – Sarah Schaller Feb 09 '17 at 20:54
Yeah, now I think you left the "var_dump" line in your code :-) that is just a debugging statement to see what's going on. remove it. – Alex Schenkel Feb 09 '17 at 21:11
So now that I've edited the prepared statement, it's back to working fine as html but not json. Edited first post. – Sarah Schaller Feb 09 '17 at 21:14

score 0 · Accepted Answer · answered Feb 10 '17 at 21:53

I ended up figuring it out myself. The json itself was fine. At some point I completely forgot about each-looping through the key-value pairs in order to actually display them. For example:

$.each(data, function(key,value) {

    var $itemid = value.ItemID;
    var $db = value.DB;
    var $job = value.Job;
    var $recipe = value.Recipe;
    var $level_range = value.Level_Range;
    var $level = value.Level;
    var $grade = value.Grade;
    var $rarity = value.Rarity;
    var $complete = value.Complete;

    $("careerdata table").append("<tr class=\"recipe\" id=\""+$recipe+"\">"

        +"<td>"+$level+$grade+"</td>"

        +"<td class=\"icon\"><a href=\"http://na.finalfantasyxiv.com/lodestone/playguide/db/"+$db+"/"+$itemid+"/\" class=\"eorzeadb_link\"><img style=\"background: url(\'/images/items/"+$job+"/"+$itemid+".png\') 0 1px/42px 42px;\" src=\"/images/items/reflection.png\" /></a></td>"

        +"<td class=\""+$rarity+"\">"+$recipe+"</td>"

        +"<td><input class=\"complete\" type=\"checkbox\" value=\""+$complete+"\"></td>"

    +"</tr>");

});

handy tip: you can use jquery to build you the entire table first, then inject your values where needed: `var row = $(".........); row.id = $recipe; var cols = $("td"); $(cols[0]).text($level + $grade); $(cols[1].......` etc. Although an even nicer solution is to use a small template instead and just fill that in (lots of template solutions for jquery out there) — Mike 'Pomax' Kamermans, Feb 10 '17 at 22:01

JSON not encoding as json or UTF-8?

2 Answers2