James_Avery
asked on
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?orde r=name' = ORDER BY 'name'
'project-listings.php?orde r=date' = ORDER BY 'dateadded'
'project-listings.php?orde r=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?
Something like this:
'project-listings.php?orde
'project-listings.php?orde
'project-listings.php?orde
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);
?>
ASKER
I tried your code out and I was receiving a syntax error on the line: $orderby = $orderfieldname[$sortorder ];
Is it certain that you passed in one of those 3 values?
Check by
$sortorder = $_POST['order'];
echo $sortorder;
and make sure.
Check by
$sortorder = $_POST['order'];
echo $sortorder;
and make sure.
Oops, I might have done this. With a URL input you need to use $_GET.
$sortorder = $_GET['order'];
echo $sortorder;
$sortorder = $_GET['order'];
echo $sortorder;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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!
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!
$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 ....