?
Solved

Auto MYSQL query ORDER BY using URL Parameter

Posted on 2010-08-24
7
Medium Priority
?
376 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
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:Cornelia Yoder
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 27

Expert Comment

by:Cornelia Yoder
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 2000 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:Cornelia Yoder
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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 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