Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial![](https://ecs-static.teamtreehouse.com/assets/views/marketing/shared/community-banner-white-47072046c51352fe6a69f5e691ff5700b28bb11d45197d7bdf066d9ea3f72d0c.webp)
![Coco Jackowski](https://secure.gravatar.com/avatar/d83135cd43890583d5ebf2bac8893620?s=96&d=https%3A%2F%2Fecs-static.teamtreehouse.com%2Fassets%2Fcontent%2Fdefault_avatar-445fbbabfc8dc9188fb5967fe43322ee0c3e0dd1e10f378bf8343784af5a13eb.webp&r=pg)
Coco Jackowski
12,914 Pointsmysqli bind_param() function not working
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 prepare
ed 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!
1 Answer
![thomascawthorn](https://uploads.teamtreehouse.com/production/profile-photos/654142/micro_11350743_1668650660030200_2146547820_n.jpg)
thomascawthorn
22,986 PointsHey! 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