Need some help with 'ORDER BY' please!!

[DeMolen]

Dear Forum,

I've been searching for quite a while for something which comes close to my question - and freiheit's thread back in May 2010 entitled 'Sorting data asc and desc using a jump menu' came the closest.

Whilst pointing me in the right direction, I need to take it a step further. He was just sorting by 'date' with a option offering the result ASC or DESC, but I want to display a list of gallery contents, whereby the page visitor can select to view the list by price (high to low, low to high), dimension (smallest to largest & vice versa), and a couple of other field names.

To create a fixed list of items I'm currently using the follow code:

<?
$sql = "SELECT * FROM art ORDER BY id";
$recordset = mysql_query( $sql )or die(mysql_error());
        
while( $row=mysql_fetch_assoc($recordset)) {
            print $row['image'] .'<br>'. $row['title'] .'<br>
            dimension:'. $row['dimension'] .'<br>
            medium:'. $row['medium'] .'<br>
            price: &pound;'. $row['price'] .'<br>
            sale status:'. $row['salestatus'];
            }
?>

... so the 'ORDER BY id' needs to be replaced with something that links it to a pulldown menu offering title, dimension ASC, dimension DESC, medium, price ASC and price DESC - but I haven't a clue where to begin.

Lastly, on an Amazon.com page when viewing results you can also change the order you view things according to price, category, etc - but you always then have to click a 'Go' button to activate the result. Is this always essential or can the page reload automatically after the visitor has changed the selection in the pulldown menu?

I'd be very grateful for any assistance and appreciate your time and trouble.

Thanks in advance

DM

[Kieran Y5]

I hope this answer is Okay because it took me ages to write out :)
Important bit:

<?php
if(isset($_GET['order']))
{
	$order = $_GET['order'];
}
else
{
	$order = 1;
}

if(!is_numeric($order))
{
	// Something went wrong
	$order = 1;
}

/* Try to stop any errors/warnings */
$selected[1] = "";
$selected[2] = "";
$selected[3] = "";
$selected[4] = "";

$selected[$order] = 'selected="selected"';

switch($order)
{
	case 1:
	{
		$sql_order = "price ASC";
		break;
	}
	case 2:
	{
		$sql_order = "price DESC";
		break;
	}
	case 3:
	{
		$sql_order = "dimension ASC";
		break;
	}
	case 4:
	{
		$sql_order = "dimension DESC";
		break;
	}
	default:
	{
		$sql_order = "price ASC";
		break;
	}
}

$sql = "SELECT * FROM art ORDER BY ".$sql_order;
$recordset = mysql_query( $sql )or die(mysql_error());
     
while( $row=mysql_fetch_assoc($recordset))
{
print $row['image'] .'<br />'. $row['title'] .'<br />
dimension:'. $row['dimension'] .'<br />
medium:'. $row['medium'] .'<br />
price: &pound;'. $row['price'] .'<br />
sale status:'. $row['salestatus'];
}
?>

HTML bit:

<form action="" method="get">
<select name="order" onchange='OnChange(this.form.order);'>
  <option value="1" <?php echo $selected[1]; ?>>Price (low to high)</option>
  <option value="2" <?php echo $selected[2]; ?>>Price (high to low)</option>
  <option value="3" <?php echo $selected[3]; ?>>Dimension (smallest to largest)</option>
  <option value="4" <?php echo $selected[4]; ?>>Dimension (largest to smallest)</option>
</select>
<!-- For people not using JavaScript - just in case -->
<noscript>
<input type="submit" value="Go!" />
</noscript>
</form>

I would use numbers for the values to stop people from trying to mess the database up but I will use methods to stop this.

The <?php echo $selected[1]; ?> is so that when the form is submitted, the drop-down box stays the same value.

Now I am going to say what I done :)

In the HTML, I have made a form with a drop-down menu. If the user isn't using JavaScript a submit button is used. When the user changes the item, JavaScript submits the form. You would need to use AJAX to update the content without a submit and that would take lots of code.

The PHP check if the "order" variable has been sent. If it has then it changes the variable $order. An array with 4 indexes is then set to null. The change the array using the index $order to 'selected="selected"'. If the $order isn't 1-4 then it still adds it in but it won't be printed to the page in the HTML.

A switch statement is then used to determine what $order is equal to. It changes the variables $sql_order to whatever is needed. Then the $sql_order is put into the SQL, you run the query and JOY! It works! (I hope).

Kieran :)

[DeMolen]

Hey Kieran

Fantastic, fabulous, great!! - I'm very grateful indeed for your amazingly swift reply and solution.

Only the Go! button didn't appear on the page (have JavaScript enabled!), but on removing the <script></noscript> tags it worked perfectly.

I still need to re-jig the database fields to properly index on height and width dimensions plus add a couple of sort options, but you've helped me with the basis which is fabulous.

Thanks again.

DM