mysql query, display order?

[Paul_Ferrie]

Is it possible to set a query like this

 if(empty($orders))
	$orders = "artists";
else if(empty($dir))
	$dir = "DESC";
else if($dir=="ASC")
	$dir = "DESC";
else 
	$dir ="ASC";


//
SELECT * FROM records4sale WHERE artists LIKE '%$str%' OR titler LIKE '%$str%' OR labeler LIKE '%$str%' OR genre LIKE '%$str%' ORDER BY '$orders' '$dir'

$orders = wich field in the database
$dir = ASC,DESC

Is this possible.
I have been tinkering about but cant get it to work.

[laserlight]

Create a separate variable to toggle the ORDER BY of each column.

[Paul_Ferrie]

Is that not what i have done with $dir?

[laserlight]

Oh, sorry, I mis-read your intention 🙂

You could consider:

SELECT * FROM records4sale WHERE artists LIKE '%$str%' OR titler LIKE '%$str%' OR labeler LIKE '%$str%' OR genre LIKE '%$str%' $orderby

$orderby is then constructed into something like ORDER BY column ASC

if (!empty($orders)) {
	$orderby = ' ORDER BY `' . $orders . '`';
	if (!empty($dir)) {
		$orderby .= ' ' . $dir;
	}
} else {
	$orderby = '';
}

Actually, to be on the safe side (avoid SQL injection), I would double check that $orders contained valid input, and use a $rev variable to toggle reverse order.

[Paul_Ferrie]

Now i am really lost
I cant get it to work, but i do see were your comming from.

[Paul_Ferrie]

I dont seem to be able to toggle between ASC and DESC.

[tsinka]

Where do $orders and $dir come from ? How does your current code look like ? Do you get any SQL errors ?

[Paul_Ferrie]

No errors
Read the first post

[tsinka]

A misunderstanding I think .. I read the first post and the lines
if(empty($orders))
else if(empty($dir))
make me wonder where these variables are set initally or if they come from a form. If these two variables are get or post variables you might need to use e.g. $GET['dir'] or $POST['dir'] instead of $dir.
I think the if/else code should be modified at least to the following.

$validOrders = array('artists','titler','labeler');

if(!isset($orders) || !in_array($orders,$validOrders)) {
    $orders = "artists"; 
}

if (!isset($dir) || empty($dir)) {
  $dir = "DESC";
} else if ($dir != "DESC" && $dir != "ASC") {
  $dir = "DESC";
}

// if using MySQL, use mysql_escape string
// to prevent SQL injection
$str = mysql_escape_string($str);

$sql = "SELECT * FROM records4sale WHERE artists LIKE "
      ."'%$str%' OR titler LIKE '%$str%' OR labeler LIKE "
      ."'%$str%' OR genre LIKE '%$str%' ORDER BY "
      ."$orders $dir"

$res = mysql_query($sql);

Besides that using "standard" single quotes around field names will produce SQL errors you can get if you do something like

$res = mysql_query($sql) or die("SQL error: ".mysql_error());

This is why I asked for any SQL errors. Either use ` or no quotes at all.

Thomas

[laserlight]

Here's an example.

Let's say that I have 2 columns that I want the user to choose from.
For example, username and post_count.
Now, the user can choose to view in ascending order, or to reverse the order.

So I might have 4 links:

Sort by:<br />
<a href="<?php echo $_SERVER['PHP_SELF']; ?>?sortby=username"></a><br />
<a href="<?php echo $_SERVER['PHP_SELF']; ?>?sortby=username&amp;rev=1"></a><br />
<a href="<?php echo $_SERVER['PHP_SELF']; ?>?sortby=post_count"></a><br />
<a href="<?php echo $_SERVER['PHP_SELF']; ?>?sortby=post_count&amp;rev=1"></a><br />

Now here is some of the processing part:

//create the query (assume $str exists is had been validated)
$query = "SELECT * FROM ... LIKE '%$str%'";
//add the sorting mechanism
if (!empty($_GET['sortby'])) {
	//the array of possible column names for sorting
	$sortby_array = array('username', 'post_count');
	//validate the column name requested for sorting
	if (in_array($_GET['sortby'], $sortby_array)) {
		//configure query to be ordered by the column name
		$query .= ' ORDER BY ' . $_GET['sortby'];
		//check if reverse order is requested
		if (!empty($_GET['rev']) && $_GET['rev']) {
			//reverse the sort order
			$query .= ' DESC';
		}
	}
}