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

Question

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

Answer 1

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;
}