Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    2

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

    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">
    <!--
    function MM_preloadImages() { //v3.0
    var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
    var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
    if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
    }

    function MM_swapImgRestore() { //v3.0
    var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
    }

    function MM_findObj(n, d) { //v4.01
    var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
    if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
    for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
    if(!x && d.getElementById) x=d.getElementById(n); return x;
    }

    function MM_swapImage() { //v3.0
    var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
    if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
    }


    //-->
    </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>
    <!--end of wholeSite-->


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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  3. #3
    Join Date
    Mar 2009
    Posts
    2

    Thanks!

    Thanks - will try it in the morning

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •