douxunchen3498
douxunchen3498
2012-11-10 12:55

在MySQL COUNT()创建的JSON对象上使用$ .each

I have been using JSON objects encoded in PHP from my MySQL database in many places with no problems. I am now trying to create one from a resultset using COUNT(column) in the syntax and I think it is causing a the problem but I have no idea how to fix it.

My JS looks like this...

createTeacherStatsTab = function(){
    $('#main').append('<div id="teacherStatsTab></div>"');
    $.getJSON("php/countMarkingPerTeacher.php", function(data) {
        $.each(data, function(key, val) {
            $('#teacherStatsTab').append(val.teacher + ' : ' + val.count(teacher));
        });
    });
}

And my PHP looks like this...

$result = mysql_query("
SELECT teacher, COUNT(teacher) 
FROM Assessment
GROUP BY teacher
ORDER BY COUNT(teacher) DESC
");

$rows = array();
while($r = mysql_fetch_assoc($result)) {
$rows[] = $r;
}
print json_encode($rows);

The data that come back looks like this...

[
{
teacher: "SDe",
COUNT(teacher): "413"
},
{
teacher: "OJe",
COUNT(teacher): "297"
},
{
teacher: "AMi",
COUNT(teacher): "257"
},
{
teacher: "ASt",
COUNT(teacher): "218"
},
{
teacher: "VJa",
COUNT(teacher): "194"
},
{
teacher: "SLa",
COUNT(teacher): "125"
},
{
teacher: "MCr",
COUNT(teacher): "99"
}
]

It feels like this is probably something to do with the GROUP BY stripping ID's or something?

Can someone please help?

Thanks!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

4条回答

  • dongxing1853 dongxing1853 9年前

    This is because JS interprets your property access as a function call because of the parenthesis:

    val.count(teacher)
    

    The most simple solution i guess is to give your COUNT() result an alias name:

    $result = mysql_query("
    SELECT 
      teacher, 
      COUNT(teacher) AS teacher_count
    FROM Assessment
    GROUP BY teacher
    ORDER BY COUNT(teacher) DESC
    ");
    

    And access it like:

    $('#teacherStatsTab').append(val.teacher + ' : ' + val.teacher_count);
    
    点赞 评论 复制链接分享
  • douchao1879 douchao1879 9年前

    Overall, the problem is that json_encode appears to be returning invalid JSON. Your object keys contain ( and ) which are invalid characters for object keys (unless they're contained in a string like in the example below).

    [{
        "teacher": "SDe",
        "COUNT(teacher)": "413"
    }, {
        "teacher": "OJe",
        "COUNT(teacher)": "297"
    }, {
        "teacher": "AMi",
        "COUNT(teacher)": "257"
    }, {
        "teacher": "ASt",
        "COUNT(teacher)": "218"
    }, {
        "teacher": "VJa",
        "COUNT(teacher)": "194"
    }, {
        "teacher": "SLa",
        "COUNT(teacher)": "125"
    }, {
        "teacher": "MCr",
        "COUNT(teacher)": "99"
    }]
    

    The easiest fix is to alias your COUNT(teacher) column:

    $result = mysql_query("
    SELECT teacher, COUNT(teacher) AS count
    FROM Assessment
    GROUP BY teacher
    ORDER BY COUNT(teacher) DESC
    ");
    

    This should render your JSON as

    [{
        teacher: "SDe",
        count: "413"
    }, {
        teacher: "OJe",
        count: "297"
    }, {
        teacher: "AMi",
        count: "257"
    }, {
        teacher: "ASt",
        count: "218"
    }, {
        teacher: "VJa",
        count: "194"
    }, {
        teacher: "SLa",
        count: "125"
    }, {
        teacher: "MCr",
        count: "99"
    }]
    

    which is valid JSON (without having to stringificate the keys) and your existing function should work fine (after changing val.count(teacher) to val.count):

    createTeacherStatsTab = function () {
        $('#main').append('<div id="teacherStatsTab></div>"');
        $.getJSON("php/countMarkingPerTeacher.php", function (data) {
            $.each(data, function (key, val) {
                $('#teacherStatsTab').append(val.teacher + ' : ' + val.count);
            });
        });
    }
    
    点赞 评论 复制链接分享
  • duanci6484 duanci6484 9年前

    I think that the object does not allow parenthesis on its properties, so when you call val.count(teacher) you are trying to call the inexisting count method of the val object. Try calling val["count(teacher)"] instead, or just changing the property name.

    点赞 评论 复制链接分享
  • douxian7808 douxian7808 9年前

    you are getting array of json.so you have to get zeroth element and than you have to iterate over it so you have to do in this way

    $.getJSON("php/countMarkingPerTeacher.php", function(data) {
        var myData = data[0];
        $.each(myData, function(key, val) {
            $('#teacherStatsTab').append(val.teacher + ' : ' + val.count(teacher));
        });
    });
    

    if your json was in this format than your code would have worked

    {
    teacher: "SDe",
    COUNT(teacher): "413"
    },
    {
    teacher: "OJe",
    COUNT(teacher): "297"
    },
    {
    teacher: "AMi",
    COUNT(teacher): "257"
    },
    {
    teacher: "ASt",
    COUNT(teacher): "218"
    },
    {
    teacher: "VJa",
    COUNT(teacher): "194"
    }
    
    点赞 评论 复制链接分享

相关推荐