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!

Recommended Answers

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

 
Jump to Post

All 2 Replies

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`";

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.