Solved

Auto MYSQL query ORDER BY using URL Parameter

Posted on 2010-08-24
7
371 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

829 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