0

Hi, I'm new to PHP and I'm having trouble getting a prepared statement to work properly using a "dynamic" sort clause. I'm trying to create a generic function that accepts sortOrder and sortDirection as parameters. It works fine if I only use a single parameter (order by ?) but when I add the second, it breaks.

How do you specify the sort direction using a prepared statement in mysqi/php?

Here's what I'm trying to do:

public function __construct()
    {
    
		$this->init();
    }
	
	private function init() {
		

		$this->conn = new mysqli("localhost","my_user","my_pass","my_db");	

		if($this->conn) {

			$this->sqlGetAllCmds = "select id,url,type,name,label from commands order by ? ?";
			$this->getCmdsStmt = $this->conn->prepare($this->sqlGetAllCmds);

		}
		else {
			echo "Error: Database connection failed: " . mysql_error();
		}
	}
	
	public function getAllCommands($sortOrder = "id", $sortDirection = "asc") {
		
		$cmds = array();

		if($this->getCmdsStmt != FALSE) {

$this->getCmdsStmt->bind_param('ss',$sortOrder,$sortDirection);  // <= Error occurs here!
			
			....
			
		return $cmds;
		
	}

When I try to run this, I get:

Call to a member function bind_param() on a non-object

If I remove the second parameter ($sortDirection) and manually set it to either ASC or DESC, it works fine. If I make the direction the sole parameter, that fails with the same error so apparently that's the part that can't be parameterized.

How do you make the direction dynamic?


Thanks!

2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by remoh71
0

Although I don't know the oop equivalent the fgllowing is using the url for which column to sort by.

$this->sqlGetAllCmds = "select id,url,type,name,label from commands order by ".mysql_real_escape_string($_GET['orderby']);

Or if you want to order by multiple columns in case of duplicate rows then the following

$this->sqlGetAllCmds = "select id,url,type,name,label from commands order by `id`, `from`, `label`";
0

Although I don't know the oop equivalent the fgllowing is using the url for which column to sort by.

$this->sqlGetAllCmds = "select id,url,type,name,label from commands order by ".mysql_real_escape_string($_GET['orderby']);

Or if you want to order by multiple columns in case of duplicate rows then the following

$this->sqlGetAllCmds = "select id,url,type,name,label from commands order by `id`, `from`, `label`";

yeah. I can do it that way but I'm trying to use a prepared statement instead. For some strange reason it won't let me bind a parameter to the DEC part. The order by clause works fine but the direction fails no matter what.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.