sort dynamically using a link to add SortBy=Code etc to a URL.

WaltonCreative · #1 (**permalink**) 03-10-09, 16:00

Hi I am trying to sort dynamically using a link to add SortBy=Code etc to a URL.

I have tried adding a script from your DB SQL Forums script, but keep getting an error with the DECLARE part.

Where EXACTLY do I put the DECLARE statement?

I am using Dreamweaver - below is the complete script for the page.

<?php require_once('Connections/chilstone_connection.php'); ?>
<?php
if (!isset($_SESSION)) {
session_start();
}
$MM_authorizedUsers = "";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) {
// For security, start by assuming the visitor is NOT authorized.
$isValid = False;

// When a visitor has logged into this site, the Session variable MM_Username set equal to their username.
// Therefore, we know that a user is NOT logged in if that Session variable is blank.
if (!empty($UserName)) {
// Besides being logged in, you may restrict access to only certain users based on an ID established when they login.
// Parse the strings into arrays.
$arrUsers = Explode(",", $strUsers);
$arrGroups = Explode(",", $strGroups);
if (in_array($UserName, $arrUsers)) {
$isValid = true;
}
// Or, you may restrict access to only certain users based on their username.
if (in_array($UserGroup, $arrGroups)) {
$isValid = true;
}
if (($strUsers == "") && true) {
$isValid = true;
}
}
return $isValid;
}

$MM_restrictGoTo = "AdminLogInFailed.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {
$MM_qsChar = "?";
$MM_referrer = $_SERVER['PHP_SELF'];
if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
if (isset($QUERY_STRING) && strlen($QUERY_STRING) > 0)
$MM_referrer .= "?" . $QUERY_STRING;
$MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
header("Location: ". $MM_restrictGoTo);
exit;
}
?>
<?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;
}
}
$colname_SectionFromURL_Recordset = "-1";
if (isset($_GET['Section'])) {
$colname_SectionFromURL_Recordset = $_GET['Section'];
}
$colname2_SectionFromURL_Recordset = "-1";
if (isset($_GET['Department'])) {
$colname2_SectionFromURL_Recordset = $_GET['Department'];
}
mysql_select_db($database_chilstone_connection, $chilstone_connection);
$query_SectionFromURL_Recordset = sprintf("DECLARE @SortOrder tinyint
SET @SortOrder = 2
SELECT *
FROM products
WHERE `Section` = colname OR `Department` = colname2
ORDER BY CASE WHEN @SortOrder = 1 THEN Code
WHEN @SortOrder = 2 THEN Name
ELSE Order", GetSQLValueString($colname_SectionFromURL_Recordse t, "text"),GetSQLValueString($colname2_SectionFromURL _Recordset, "text"));
$SectionFromURL_Recordset = mysql_query($query_SectionFromURL_Recordset, $chilstone_connection) or die(mysql_error());
$row_SectionFromURL_Recordset = mysql_fetch_assoc($SectionFromURL_Recordset);
$totalRows_SectionFromURL_Recordset = mysql_num_rows($SectionFromURL_Recordset);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
<title>Chilstone ~ <?php echo $row_SectionFromURL_Recordset['Section']; ?></title>
<link href="css.css" rel="stylesheet" type="text/css" />

<script type="text/javascript">

</script>
</head>
<body onload="MM_preloadImages('images/NavigationTop/navigationTop_01OVER.jpg','images/NavigationTop/navigationTop_02OVER.jpg','images/NavigationTop/navigationTop_03OVER.jpg','images/NavigationTop/navigationTop_04OVER.jpg','images/NavigationTop/navigationTop_05OVER.jpg','images/NavigationTop/navigationTop_06OVER.jpg','images/NavigationTop/navigationTop_07OVER.jpg','images/NavigationTop/navigationTop_08OVER.jpg','images/NavigationTop/navigationTop_09OVER.jpg','images/NavigationTop/navigationTop_10OVER.jpg','images/NavigationTop/navigationTop_11OVER.jpg')">

<div class="wholeSite">

<div id="whiteBackgroundFullWidth"></div>

<div id="sectionThumbs">
<table width="664" border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">

<tr>

<td class="adminShowHiddenProductsOnly"><strong> feature coming soon
>>>> sort order: </strong><a href="?SortBy=2">normal</a> <a href="?SortBy=4">price</a> <a href="?SortBy=Code">code</a> <a href="?SortBy=8">hidden
first</a> <a href="?SortBy=8">hidden last</a></td>

</tr>
<tr>
<td><?php do { ?>

<table width="151" border="0" cellpadding="0" cellspacing="0" class="adminSummaryTable">
<tr>
<td align="center" valign="top">

<table width="151" border="0" cellpadding="0" cellspacing="0" class="adminHoldingTable">
<tr>
<td align="center" valign="top">
<p><a href="AdminProduct.php?Code=<?php echo $row_SectionFromURL_Recordset['Code']; ?>" target="_self"><img src="images/ProductsThumbs/<?php echo $row_SectionFromURL_Recordset['Code']; ?>.jpg" border="0" title="click to edit details" /></a></p></td>
</tr>
</table>

<p><strong><?php echo $row_SectionFromURL_Recordset['Name']; ?></strong></p>
<p>£ <?php echo $row_SectionFromURL_Recordset['Price']; ?></p>
<p>Code <?php echo $row_SectionFromURL_Recordset['Code']; ?></p></td>
</tr>
</table>

<?php } while ($row_SectionFromURL_Recordset = mysql_fetch_assoc($SectionFromURL_Recordset)); ?></td>
</tr>
</table>

<div id="creditsSectionOnly">

</div>

</div>

<div id="adminShadowUnderLogo"></div>

</div>


<?php require_once('AdminNavigationTop.php'); ?>
<?php require_once('AdminNavigationSideArchitectural.php '); ?>
<?php
mysql_free_result($SectionFromURL_Recordset);
?>

mike_bike_kite · #2 (**permalink**) 03-10-09, 16:51

Quote:

Originally Posted by WaltonCreative

I am trying to sort dynamically using a link to add SortBy=Code etc to a URL.

It probably wasn't necessary to post your whole program! The variable names you added suggests the SQL code was for Sybase and not MySQL. The simplest way to change the ordering is in the PHP ie :
Code:
$sql = "select * from MyTable";

if ( $sortBy > '' ) {
    $sql = "$sql order by $sortBy";
}

$results = mysql_query( $sql );
Mike

WaltonCreative · #3 (**permalink**) 03-10-09, 16:58

Thanks - will try it in the morning

Internet Services

Web Development

Digital Marketing

Consumer Tech