Order by syntax

Databases
1

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:

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

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

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

“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

:slight_smile:

3

Thanks for the reply,

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:


<?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, 
            [B]categories.category[/B],
            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
				[B]LEFT JOIN categories ON categories.catID=items.catID[/B]
				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, [B]category DESC[/B]";
	
	$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'], [B]'category' => $row['category'][/B], '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

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

5

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.