PDO fetchAll, fetch and its usage with decoded JSON array

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

You can try var_dump($user) when that is a object with start and per_page than it is ok, otherwise there is another problem. — Haneev
– Haneev, Commented Dec 9, 2013 at 19:14
You should NEVER just assume your query works. You should a) verify that $sth actually contains a valid PDOStatement object after prepare() is called b) verify fetchAll() does not return false when you call execute() c) handle all error conditions when code does not go on happy path - log the errors so you can see what the problem is. — Mike Brant
– Mike Brant, Commented Dec 9, 2013 at 19:17
hum... is it really possible to bind '?' to the LIMIT argument of SQL? I thought it was only for variables. If that's true, it's impossible your query will ever send back any data with two ? in place of LIMIT delimiter — Félix Adriyel Gagnon-Grenier
– Félix Adriyel Gagnon-Grenier, Commented Dec 9, 2013 at 19:51

Félix Adriyel Gagnon-Grenier · Accepted Answer · 2013-12-09 20:21:40Z

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

answered Dec 9, 2013 at 20:21

Félix Adriyel Gagnon-Grenier

8,93510 gold badges58 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ey dee ey em Over a year ago

You just solved what I frustrated 6 hours with... Thank you sincerely! Btw, how you get so deep understanding on the PDO? Where should I start to better understand it? Is there any source that is good? I mean, better than pdo document by the php official site?

Félix Adriyel Gagnon-Grenier Over a year ago

I read the PDO tutorial for mysql developpers wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers it really have all the basic guidelines about preparing statements, using them, error management and more. also by trying :)

ey dee ey em Over a year ago

Thanks for the reference! Appreciated :)

Collectives™ on Stack Overflow

PDO fetchAll, fetch and its usage with decoded JSON array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related