ORDER BY clause

PHP
1

Hi everyone,

I have the following code in a controller file:

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, 
            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 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 itemTitle ASC";			

	
	$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'], '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();

}

?>

Currently my ORDER BY clause is as follows:

$order	= "ORDER BY itemTitle ASC";

Since I need to order by supplier also, I’ve brought down the supplier from the suppliers table also and created a left join.

The problem is I don’t know how to add the supplier onto the ORDER BY clause. I tried the following but it didn’t work, ie. it’s still ordering by itemTitle but not by supplier also:

$order	= "ORDER BY itemTitle ASC, supplier=\\"certain supplier\\"";

I wondered if someone could help me out with this?

Appreciate any assistance.

2

if you want to see only those rows from a certain supplier, that condition should be in the WHERE clause

only if you want to see items with the same name from multiple suppliers would it make sense to add supplier to the ORDER BY clause

p.s. all your joins should probably not be LEFT OUTER but INNER instead

3

I havn’t checked over your query, but you want to add the:


supplier=\\'certain supplier\\'

to the where clause like this:


$where     = " WHERE item_to_subcat.subcatID='$subcatID' AND supplier='certain supplier'";
4

Hi,

A list of items is being output with some from one supplier and others from another supplier. I just want the items from supplier1 to go first. Would I put this in the ORDER BY clause in that case? If yes, how would I do that?

5 
ORDER 
    BY CASE WHEN supplier <> 'supplier1' 
            THEN 'humpty'
            ELSE 'dumpty' END
     , itemTitle

:cool:

6

Thanks for the code but I’m a bit lost. What’s the humpty dumpty part? Can you explain the code?

7

each row of the result set will be sorted according to the following rules:

when the supplier is not ‘supplier1’ then it will be sorted by the value ‘humpty’

when the supplier ~is~ ‘supplier1’ then it will be sorted by the value ‘dumpty’

since ‘dumpty’ comes before ‘humpty’ alphabetically, all the rows for ‘supplier1’ will come first, followed by all the rows for suppliers other than ‘supplier1’

within those two groupings of rows, they will be sorted by itemTitle

‘humpty’ and ‘dumpty’ could be any two values, really – some people use 0 and 1 for this purpose, but those values are so pedestrian, aren’t they

:slight_smile:

8

Thanks for that explanation - very helpful. Everything’s working but say I wanted supplier 1 to go first then supplier 3 to go second, what would the code be then?

9 
ORDER 
    BY CASE WHEN supplier = 'supplier1' 
            THEN 'curly'
            WHEN supplier = 'supplier3' 
            THEN 'larry'
            ELSE 'moe' END
     , itemTitle

:cool:

10

Got it now - thanks again.