1

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?

2 Answers 2

1

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.

0
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);
    .
    .
    .
  }
} 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.