doumigu9594 2013-12-09 19:01
浏览 89
已采纳

PDO fetchAll,fetch及其在解码后的JSON数组中的用法

I have trouble to make this function run with decoded json array. For example, I tried to fetch an array with PDO prepared statement: *NOTE: $user is passed with the decoded json array.

public function get_pagnated_qs($user){     
        ////////////////////////Actual
        $sth = $this->dbh->prepare("SELECT a.quest_id, a.quest_title, a.quest_desc, b.qcat_name,c.qtype_title FROM eq_question_s AS a INNER JOIN eq_question_category AS b ON a.qcat_id = b.qcat_id INNER JOIN eq_question_type AS c ON a.qtype_id=c.qtype_id ORDER BY quest_id LIMIT ?, ?");
        $sth->execute(array($user->start, $user->per_page));
        $result = json_encode($sth->fetchAll());
        return $result;
}

it will not generate objects (I found out when I echo the object in javasccript side.

On the other hand if I replace the two ? with actual number (i.e. 0,3) then everything will work perfectly.

The function is to return $result as an encoded json back to client to process and format into a table.

I do not know if this piece of code have anything wrong?

Lets assume that I have decode the json array correctly back as object, otherwise there will be way too much code to frustrate with.

Maybe just some insight will help, but I do not want to frustrate anyone.

the client side js which take the actual return and generate form (part of the functiOn) is:

function showListOfPaginatedQuestions(jsonData) {
    alert('pagED RAN!');
    alert(jsonData);
    console.log(jsonData);
    var table = '<table width="600" cellpadding="5" class="table table-hover table-bordered"><thead><tr><th scope="col">Category</th><th scope="col">Type</th><th scope="col">Question</th><th scope="col">Question Description</th><th scope="col"></th></tr></thead><tbody>';

    $.each( jsonData, function( index, user){     
        table += '<tr>';
        table += '<td class="edit" field="qcat_id" user_id="'+user.quest_id+'">'+user.qcat_name+'</td>';
});
$('div#content').html(table);
}

Thank You

  • 写回答

1条回答 默认 最新

  • dongzantai7570 2013-12-09 20:21
    关注

    There's something you have to understand about prepared statements. The character '?' is not simply a placeholder, it refers to an actual parameter of the query, such as a condition or a value. The point is that when MySQL prepares the query, it knows that when the query will actually be executed, the condition will go here, or the value will be updated to something:

    $stmt = $db->prepare("SELECT * FROM table WHERE column=?");
    $stmt = $db->prepare("UPDATE table SET column=? WHERE keyColumn=?");
    

    those are correct statements that can be prepared and understood. but those ? are not simply variables, for exemple, you can't do that:

    $stmt = $db->prepare("SELECT * FROM ? Where column='1726'");
    

    they are parameters, not variable like $whatever in php, they are not simply replaced by whatever value you give them (sql injection would still be a huge issue then) SQL treats them as parameters, and they have specific places where you can use them, and LIMIT ?,? is not one of them, sadly :p

    just assign them to variables before:

    public function get_pagnated_qs($user){     
            ////////////////////////Actual
            $start = $user->start;
            $perPage = $user->per_page;
            $sth = $this->dbh->prepare("SELECT a.quest_id, a.quest_title, a.quest_desc, b.qcat_name,c.qtype_title FROM eq_question_s AS a INNER JOIN eq_question_category AS b ON a.qcat_id = b.qcat_id INNER JOIN eq_question_type AS c ON a.qtype_id=c.qtype_id ORDER BY quest_id LIMIT $user, $perPage");
            $sth->execute();
            $result = json_encode($sth->fetchAll());
            return $result;
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥100 有人会搭建GPT-J-6B框架吗?有偿
  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名