PHP MySQLi prepared statement giving same result when binding different parameter

Question

I'm trying to make a class that I can use for showing my 5 most recent and 4 most popular posts. I'm doing this using a prepared statement, leaving the ORDER BY statement open for including two different parameters.

The problem is, when I run this, I get the same result for both (the posts come in the same order). The code I run in the main page is this:

<?php
    $test = new SideBar();
    echo $test->recent();
    echo $test->popular(); // returns the same as recent() for some reason
?>

And this is the class:

class SideBar
{
    // Storing the names of the database table columns
    private $id = 'n';
    private $rating = 'mean';
    private $stmt;
    private $result;

    public function __construct()
    {
        global $mysqli;
        $this->stmt = $mysqli->prepare("SELECT n,title,date
                                        FROM claims
                                        WHERE active = 1 
                                        ORDER BY ? DESC
                                        LIMIT 5");
    }

    public function recent()
    {
        $this->result = "";
        return $this->build($this->id);
    }

    public function popular()
    {   
        $this->result = "";
        return $this->build($this->rating);
    }

    private function build($order)
    {
        $this->stmt->bind_param('s',$order);
        $this->stmt->execute();
        $this->stmt->bind_result($n, $title, $date);
        while($this->stmt->fetch())
        {
        $this->result .= '<a href="[mydomain]?id='.$n.'">';
        $this->result .= $title.' '.$date;
        $this->result .= "</a>\n";
        }
        return $this->result;
    }

    public function __destruct()
    {
        $this->stmt->close();
    }
}

http://stackoverflow.com/questions/12021018/php-mysql-mysqli-prepared-statements-possible-to-use-bind-param-for-order-by — skmail, Mar 21 '15 at 20:59
You can't bind column or table names, such as used in an ORDER BY clause — Mark Baker, Mar 21 '15 at 21:22

score 0 · Accepted Answer · answered Mar 21 '15 at 23:19

Obviously you can't bind a column name to a an ORDER BY clause. Instead you can form the necessary query in the recent and popular functions and move the prepare pdo statement into the build function. Here are the modifications:

public function __construct()
    {
    }

    public function recent()
    {
        $this->result = "";
        $q="SELECT n,title,date
                                        FROM claims
                                        WHERE active = 1 
                                        ORDER BY id DESC
                                        LIMIT 5"
        return $this->build($q);
    }

    public function popular()
    {   
        $this->result = "";
        $q="SELECT n,title,date
                                        FROM claims
                                        WHERE active = 1 
                                        ORDER BY mean DESC
                                        LIMIT 5"
        return $this->build($q);
    }

    private function build($query)
    {
        global $mysqli;
        $stmt = $mysqli->prepare($query)
        $stmt->execute();
        $stmt->bind_result($n, $title, $date);
        while($stmt->fetch())
        {
        $this->result .= '<a href="[mydomain]?id='.$n.'">';
        $this->result .= $title.' '.$date;
        $this->result .= "</a>\n";
        }
        return $this->result;
    }

PHP MySQLi prepared statement giving same result when binding different parameter

1 Answers1