Dynamic ORDER BY

michael789
michael789 used Ask the Experts™
on
I need to order mySQL query by dynamic GET parameters however it is not working.

$colname_listmain = "-1";
if (isset($_SESSION['kt_login_id'])) {
  $colname_listmain = (get_magic_quotes_gpc()) ? $_SESSION['kt_login_id'] : addslashes($_SESSION['kt_login_id']);
}
$navletter_listmain = "%";
if (isset($_GET['navletter'])) {
  $navletter_listmain = (get_magic_quotes_gpc()) ? $_GET['navletter'] : addslashes($_GET['navletter']);
}
$neworder = "%";
if (isset($_GET['neworder'])) {
  $neworder = (get_magic_quotes_gpc()) ? $_GET['neworder'] : addslashes($_GET['neworder']);
}
$sorting2 = "%";
if (isset($_GET['neworder'])) {
  $sorting2 = (get_magic_quotes_gpc()) ? $_GET['sorting2'] : addslashes($_GET['sorting2']);
}
mysql_select_db($database_inburn, $inburn);
$query_listmain = sprintf("SELECT * FROM list_asso LEFT JOIN listmain ON listmain.id_list = list_asso.mandantID2 WHERE list_asso.mitarbeiterid2 = %s AND listmain.active = 1 AND listmain.mandantnu2 LIKE %s ORDER BY $neworder $sorting2", GetSQLValueString($colname_listmain, "int"),GetSQLValueString($navletter_listmain . "%", "text"));
$query_limit_listmain = sprintf("%s LIMIT %d, %d", $query_listmain, $startRow_listmain, $maxRows_listmain);
$listmain = mysql_query($query_limit_listmain, $inburn) or die(mysql_error());
$row_listmain = mysql_fetch_assoc($listmain);

Is it not possible to take two dynamic ORDER by parameters?

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
ORDER BY $neworder $sorting2",    <---- I believe the ORDER BY fields needs a comma between

ORDER BY $neworder, $sorting2",

Author

Commented:
but $sorting2 has to be either DESC or ASC
I tried that and the query gives an error

Commented:
Ah sorry, didn't realize it wasn't two sorting fields.


OK, lets try again:  

ORDER BY $neworder $sorting2", GetSQLValueString($coln.....  <------ something is wrong here.  When these variables are resolved, it's not a correct php syntax.

Should it be something like this:

ORDER BY $neworder $sorting2, " . GetSQLValueString($colname_listmain, "int") . ", " . GetSQLValueString($navletter_listmain . "%", "text"));
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Author

Commented:
hi..i tried that but I am getting this error for this line

Warning: sprintf() [function.sprintf]: Too few arguments in /home/content/66/6620566/html/dashboard4.php on line 93
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1

Commented:
That's an entirely different query and error, so apparently my solution solved the first one.

Check your syntax with all the variables set to values and see if they syntax is correct that way.

Author

Commented:
actually it does not because the error refers to the line I applied the changes. Don't worry. I somehow try to figure it out
Commented:
The first query:

$query_listmain = sprintf("SELECT * FROM list_asso LEFT JOIN listmain ON listmain.id_list = list_asso.mandantID2 WHERE list_asso.mitarbeiterid2 = %s AND listmain.active = 1 AND listmain.mandantnu2 LIKE %s ORDER BY $neworder $sorting2", GetSQLValueString($colname_listmain, "int"),GetSQLValueString($navletter_listmain . "%", "text"));

has no LIMIT keyword, so this query was apparently resolved correctly.

The second query:

$query_limit_listmain = sprintf("%s LIMIT %d, %d", $query_listmain, $startRow_listmain, $maxRows_listmain);
$listmain = mysql_query($query_limit_listmain, $inburn) or die(mysql_error());

has the LIMIT keyword that is getting the new error.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial