Solved

Auto MYSQL query ORDER BY using URL Parameter

Posted on 2010-08-24
7
375 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 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: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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

688 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