Auto MYSQL query ORDER BY using URL Parameter

I have a MYSQL query that I am running on a page to display a recordset with a repeat region.  I need to be able to offer to set the ORDER BY to fieldnames of 'name', 'dateadded' or  'designer1' depending on a url parameter.

Something like this:
'project-listings.php?order=name' = ORDER BY 'name'
'project-listings.php?order=date' = ORDER BY 'dateadded'
'project-listings.php?order=designer' = ORDER BY 'designer1'

Line 34 in the code below contains the current static ORDER BY, I need this to change depending on the URL param.

Any ideas?
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_testconnection, $testconnection);
$query_projects = "SELECT *,IF((SELECT activeflag FROM designers WHERE fullname = p.designer1)=2,designer1,NULL) as designer1
,IF((SELECT activeflag FROM designers WHERE fullname = p.designer2)=2,designer2,NULL) as designer2
,IF((SELECT activeflag FROM designers WHERE fullname = p.designer3)=2,designer3,NULL) as designer3 FROM projects p WHERE activeflag = '2' ORDER BY dateadded DESC";
$projects = mysql_query($query_projects, $testconnection) or die(mysql_error());
$row_projects = mysql_fetch_assoc($projects);
$totalRows_projects = mysql_num_rows($projects);
?>

Open in new window

James_AveryAsked:
Who is Participating?
 
yudohartonoConnect With a Mentor Commented:
try like this
$orderby = $_GET['order'];
switch($orderby){
case 'name':
        $order ='name';
        break;
    case 'date':
        $order ='dateadd';
        break;
    case 'designer:
        $order ='designer1';
        break;
        default:
        $order ='dateadd';
        break;
}
// on your sql 
...ORDER BY ".$order." DESC";

Open in new window

0
 
Cornelia YoderArtistCommented:
Retrieve the URL parameter into a variable, something like:

$sortorder = $_POST['order'];

Then adjust that to the desired field name (an array allows for easy expansion):

$orderfieldname = array(
'name'=>'name',
'date'=>'dateadded',
'designer'=>'designer1'
)

$orderby = $orderfieldname[$sortorder];

Then set up your MySQL query:

... SELECT * FROM Table ORDER BY $orderby ....
0
 
James_AveryAuthor Commented:
I tried your code out and I was receiving a syntax error on the line: $orderby = $orderfieldname[$sortorder];

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Cornelia YoderArtistCommented:
Is it certain that you passed in one of those 3 values?

Check by

$sortorder = $_POST['order'];
echo $sortorder;

and make sure.
0
 
Cornelia YoderArtistCommented:
Oops, I might have done this.  With a URL input you need to use $_GET.

$sortorder = $_GET['order'];
echo $sortorder;
0
 
Cornelia YoderArtistCommented:
The CASE statement works, also a series of IF statements would work, but using the array method is more efficient and easier to manage changes.
0
 
James_AveryAuthor Commented:
Thanks yudohartono! One more question:

When sorting my recordset, I have replaced 'designer1' field with'showroom'.  The showroom field contains a city name.  Is it possible to print the city name when it goes from one to the other.  For example, if I have 3 records in my DB with a showroom field of 'Dallas', and 3 more with a showroom field of 'Atlanta', is it possible to print the showroom field above the record set?

EXAMPLE:

DALLAS
Record 1
Record 2
Record 3

ATLANTA
Record 1
Record 2
Record 3

TULSA
Record 1
Record 2
Record 3

Please let me know your thoughts. Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.