User-defined sort order for a query

Question

I'm storing user preferences for sort order in a table:

$queryHistory = "SELECT orderby,ascdesc FROM user WHERE UserID= ".$id;

$resultHistory = doQuery($connection, $queryHistory);
$numHistory = mysql_num_rows($resultHistory);
if ($numHistory > 0) {
  while($rowHistory = fetchRow($resultHistory, $queryHistory)) {
    $orderBy = getResult($rowHistory, $resultHistory, 1);
    $sortBy = getResult($rowHistory, $resultHistory, 2);
  }
} 

And constructing a query based on that which looks like:

$otherQuery = "SELECT *
               FROM  user
               WHERE user.UserID = $id
               ORDER BY ".$orderBy." ".$sortBy;

Is it possible to do this with a single query, rather than first reading the preferences then running the actual query?

Answer 1

There is a way to generate the separate commands, but you will have to run those commands

$queryHistory = "SELECT CONCAT('SELECT * FROM user WHERE user.UserID=" . $id . " ORDER BY ',orderBy,' ',sortBy) sqlcmd FROM user WHERE UserID=" . $id;

$resultHistory = doQuery($connection, $queryHistory);
$numHistory = mysql_num_rows($resultHistory);
if ($numHistory > 0) {
  while($rowHistory = fetchRow($resultHistory, $queryHistory)) {
    $SQLCommand = getResult($rowHistory, $resultHistory, 1);
    .
    .
    .
  }
} 

Answer 2

How about using a prepared statement to do it? Something like the below:

SELECT @orderBy := orderby, @ascDesc := ascdesc FROM user WHERE UserId = @id;
PREPARE cmd 
FROM 'SELECT * FROM user WHERE user.UserID = ? ORDER BY ? ?';
EXECUTE cmd USING @id, @orderBy, @ascDesc;
DEALLOCATE PREPARE cmd;

EDIT

Apparently it is not possible to use parameters in the way that I have above (see comment below) in which case you should do the following:

SELECT @orderBy := 'Name', @ascDesc := 'ASC' FROM user WHERE Id = @id;
SET @s := 
CONCAT('SELECT * FROM user WHERE user.UserID = ? ORDER BY ',@orderBy,' ',@ascDesc);
PREPARE cmd FROM @s
EXECUTE cmd USING @id;
DEALLOCATE PREPARE cmd;

I hope this helps you.