mysqli bind_param() function not working

Question

I'm quering a database using mysqli. The only variables involved in the query are used for the order, limit and offset, and are named accordingly. When I include the $order variable directly into the prepare() function and attach the $offset and $limit variables with the bind_param() function, the query runs just fine. When I try to attach the $order variable with bind_param(), the query runs, but the items returned always show up in the same (incorrect) order.

Here is the (relevant) code that works:

// run query
    if (!$stmt = $db->prepare('SELECT id, content_text, up_votes, down_votes, date FROM content ORDER BY ' . $order . ' LIMIT ? OFFSET ?;')) {
        die('prepare');
    }
    if (!$stmt->bind_param('ii', $limit, $offset)) {
        die('bind');
    }
    if (!$stmt->execute()) {
        die('execute');
    }
    $stmt->store_result();
    $stmt->bind_result($id, $content_text, $up_votes, $down_votes, $date);
    $all_posts = Array();
    while ($stmt->fetch()) {
        $individual_post = Array(
            'id' => $id,
            'content_text' => $content_text,
            'up_votes' => $up_votes,
            'down_votes' => $down_votes,
            'date' => $date
        );
        array_push($all_posts, $individual_post);
    }

And here is the code that doesn't work. Note that the only difference is that I'm trying to use bind_param() to get $order into the query, rather than including it into the prepareed query:

// run query
    if (!$stmt = $db->prepare('SELECT id, content_text, up_votes, down_votes, date FROM content ORDER BY ? LIMIT ? OFFSET ?;')) {
        die('prepare');
    }
    if (!$stmt->bind_param('sii', $order, $limit, $offset)) {
        die('bind');
    }
    if (!$stmt->execute()) {
        die('execute');
    }
    $stmt->store_result();
    $stmt->bind_result($id, $content_text, $up_votes, $down_votes, $date);
    $all_posts = Array();
    while ($stmt->fetch()) {
        $individual_post = Array(
            'id' => $id,
            'content_text' => $content_text,
            'up_votes' => $up_votes,
            'down_votes' => $down_votes,
            'date' => $date
        );
        array_push($all_posts, $individual_post);
    }

Why doesn't the second example work? I don't get any error messages. The query seems to run just fine, it's just that I get the items back in the wrong order, and the order is always the same, even when I change the value of $order explicitly. The $order, $offset and $limit variables all have values set earlier in the code, and I know, after extensive testing, that their values are correct. I know that I can just use the working code I have above, but I don't like that I don't know why the second example doesn't work. Any help will be greatly appreciated. Thank you!

Answer 1 · 2014-06-14T12:56:26Z

June 14, 2014 12:56pm

Hey! I haven't seen this kind of method before.. Is it good practice to create lots of die statements because if it is.. I need to change a lot of my code! :p Just general interest - How come you're not using a try catch block with PDO?.

From what you've said, the bug is somewhere between binding the param with the statement. Maybe you could try and alternative method like multiple bind params and asign a value to each occurrence?

it's something like:

$results = $db->prepare('SELECT id, content_text, up_votes, down_votes, date FROM content ORDER BY ? LIMIT ? OFFSET ?;')
$results->bindParam(1,$order);
$results->bindParam(2,$limit)
$results->bindParam(3,$offset)

again, just general interest, how come you've bound the apram with an 's' instead of an 'i' when adding a third param?

    if (!$stmt->bind_param('ii', $limit, $offset)) {
//vs
   if (!$stmt->bind_param('sii', $order, $limit, $offset))

Tom

Welcome to the Treehouse Community

Looking to learn something new?

Coco Jackowski

Coco Jackowski

mysqli bind_param() function not working

1 Answer

thomascawthorn

thomascawthorn