SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Order by syntax

  1. #1
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order by syntax

    Hi everyone,

    A while ago I asked a question on this forum about how to construct an ORDER BY clause and I was given the following code:

    Code:
    $order	= "ORDER 
    		   BY CASE WHEN supplier <> 'Supplier Name'
    		   THEN 'humpty'
    		   ELSE 'dumpty' END, itemTitle";
    This worked really well but I now need to extend the clause so that it is also ordering by an additional column. I revised the above code by adding the column name as follows:

    Code:
    $order	= "ORDER 
    		   BY CASE WHEN supplier <> 'Supplier Name'
    		   THEN 'humpty'
    		   ELSE 'dumpty' END, itemTitle, category DESC";
    This didn't work though so I just wondered if anyone knew what the problem might be. Is it incorrect syntax or could there be another reason?

    Appreciate any advice.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "this didn't work" is not a valid mysql error message

    perhaps you could explain exactly what did happen

    you did add the 3rd column correctly, so the difficulty must lie in crafting an ORDER BY clause to actually do whatever it is you want it to do, which isn't clear at this point

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply,

    Quote Originally Posted by r937 View Post
    "this didn't work" is not a valid mysql error message
    perhaps you could explain exactly what did happen
    Nothing happened really. I mean there was no adverse reaction - the ordering just stayed as it was.

    The following is my code in the controller file where I've brought down the category and included it in the order by clause:

    Code:
    <?php
    define ('__ROOT__', $_SERVER['DOCUMENT_ROOT']);
    
    include_once(__ROOT__ . "/includes/magicquotes.inc.php");
    
    if (isset($_GET['subcatID'])) {
    	include_once(__ROOT__ . "/includes/db.inc.php");
    	
    	$subcatID =  mysqli_real_escape_string($link, $_GET['subcatID']);
    	$itemSubtypeID = "";
    	
    	$select = "SELECT
                items.itemID, 
                itemTitle, 
                categories.category,
                itemSKULadies, 
                itemSKUMen, 
                itemDescLadies, 
                itemDescMen,  
                itemPriceBoth,
                itemPriceFemale,
                itemPriceMale,
                itemColoursBoth,
                itemColoursFemale,
                itemColoursMale,
    	    suppliers.supplier,
                itemTypes.itemType,
    	    itemSubtypes.itemSubtype,
    	    subcategories.subcategory,
                sizesMen.size AS Msize, 
                sizesLadies.size AS Lsize, 
                itemSwatchBoth,
                itemSwatchFemale,
                itemSwatchMale,
                itemImage";
    	$from	= " FROM items
    				LEFT JOIN categories ON categories.catID=items.catID
    				LEFT JOIN sizesMen ON sizesMen.sizeMenID=items.sizeMenID 
              		LEFT JOIN sizesLadies ON sizesLadies.sizeLadiesID=items.sizeLadiesID
    				LEFT JOIN suppliers ON suppliers.supplierID=items.supplierID
                	LEFT JOIN itemTypes ON itemTypes.itemTypeID=items.itemTypeID
    				LEFT JOIN itemSubtypes ON itemSubtypes.itemSubtypeID=items.itemSubtypeID
    				LEFT JOIN item_to_subcat ON item_to_subcat.itemID = items.itemID
    				LEFT JOIN subcategories ON subcategories.subcatID = item_to_subcat.subcatID";
    	$where 	= " WHERE item_to_subcat.subcatID='$subcatID'";
    	if (isset ($_GET['itemTypeID'])) {
    		$itemTypeID =  mysqli_real_escape_string($link, $_GET['itemTypeID']);
            $where = $where . " AND itemTypes.itemTypeID='$itemTypeID'";
        }
    	
    		if (isset ($_GET['itemSubtypeID'])) {
    		$itemSubtypeID = mysqli_real_escape_string($link, $_GET['itemSubtypeID']);
            $where = $where . " AND itemSubtypes.itemSubtypeID='$itemSubtypeID'";
        }
    	
    $order	= "ORDER 
    		   BY CASE WHEN supplier <> 'Supplier'
    		   THEN 'humpty'
    		   ELSE 'dumpty' END, itemTitle, category DESC";
    	
    	$current = $itemSubtypeID;
    		
    	$result = mysqli_query($link, $select . $from . $where . $order);
    	if (!$result)
    	{
    		$error = 'Error fetching items: ' . mysqli_error($link);
    		include 'error.html.php';
    		exit();
    	}
    
    	
        $items = array();
    	
    	
    	while ($row = mysqli_fetch_array($result))
    	{
    	 $items[] = array('itemTitle' => $row['itemTitle'], 'category' => $row['category'], 'itemSKULadies' => $row['itemSKULadies'], 'itemSKUMen' => $row['itemSKUMen'], 'itemDescLadies' => $row['itemDescLadies'], 'itemDescMen' => $row['itemDescMen'], 'itemPriceBoth' => $row['itemPriceBoth'], 'itemPriceFemale' => $row['itemPriceFemale'], 'itemPriceMale' => $row['itemPriceMale'], 'itemColoursBoth' => $row['itemColoursBoth'], 'itemColoursFemale' => $row['itemColoursFemale'], 'itemColoursMale' => $row['itemColoursMale'], 'Lsize' => $row['Lsize'], 'Msize' => $row['Msize'], 'supplier' => $row['supplier'], 'itemType' => $row['itemType'], 'itemSubtype' => $row['itemSubtype'], 'itemSwatchBoth' => $row['itemSwatchBoth'], 'itemSwatchFemale' => $row['itemSwatchFemale'], 'itemSwatchMale' => $row['itemSwatchMale'], 'itemImage' => $row['itemImage'], 'subcategory' => $row['subcategory']);
    	}
    
    include 'catalogue.php';
    	exit();
    
    }
    
    ?>
    Can you see where I may have gone wrong?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by gwh View Post
    Can you see where I may have gone wrong?
    not in your php code, no (i don't do php)

    i suspect you aren't seeing any changes in the order because an item belongs to only one category

    your sort sequence is --

    1. a certain supplier's items come first
    2. items sorted by title
    3. categories sorted within items

    in other words, adding the category as the 3rd sort column doesn't really change anything
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jun 2005
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It seems that when I swapped around the category and the itemTitle it started to order correctly, eg.


    $order = "ORDER
    BY CASE WHEN supplier <> 'Supplier Name'
    THEN 'humpty'
    ELSE 'dumpty' END, category DESC, itemTitle";

    Thanks for the advice.


Bookmarks

Posting Permissions

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