Solved

Auto MYSQL query ORDER BY using URL Parameter

Posted on 2010-08-24
7
368 Views
Last Modified: 2013-12-13
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

0
Comment
Question by:James_Avery
  • 4
  • 2
7 Comments
 
LVL 27

Expert Comment

by:yodercm
ID: 33514847
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
 

Author Comment

by:James_Avery
ID: 33515848
I tried your code out and I was receiving a syntax error on the line: $orderby = $orderfieldname[$sortorder];

0
 
LVL 27

Expert Comment

by:yodercm
ID: 33516282
Is it certain that you passed in one of those 3 values?

Check by

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

and make sure.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 27

Expert Comment

by:yodercm
ID: 33516339
Oops, I might have done this.  With a URL input you need to use $_GET.

$sortorder = $_GET['order'];
echo $sortorder;
0
 
LVL 3

Accepted Solution

by:
yudohartono earned 500 total points
ID: 33517343
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
 
LVL 27

Expert Comment

by:yodercm
ID: 33517807
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
 

Author Comment

by:James_Avery
ID: 33524818
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now