Different ORDER BY setting for different mySQL columns.

[strago]

How would I have one query with the ORDER BY settings based on columns??

 

One has +0 DESC so the numbers show up in the correct order and decending, one is just DESC, and one is just ASC.

 

Gravity, InitialEarningsPerSale, and AverageEarningsPerSale having...

$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY $order +0 DESC LIMIT $start_from, 50";

 

ActivateDate having...

$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY $order DESC LIMIT $start_from, 50";

 

Links having...

$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY $order ASC LIMIT $start_from, 50";
 

 

[trq]

Your issue is as clear as mud.

[strago]

How do you have all of these in the same query?

 

Gravity, InitialEarningsPerSale, and AverageEarningsPerSale having...

 

ORDER BY $order +0 DESC LIMIT $start_from, 50";

 

ActivateDate having...

 

ORDER BY $order DESC LIMIT $start_from, 50";

 

Links having...

 

ORDER BY $order ASC LIMIT $start_from, 50";

[trq]

You can have as many ORDER BY clauses as you need, separated by comas.

[Barand]

  On 4/27/2013 at 8:21 AM, trq said:

You can have as many ORDER BY clauses as you need, separated by comas.

On the same column?

[trq]

I still don't understand what the OP is getting at. Just letting him know what can be done.

[strago]

$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY Links $order ASC LIMIT $start_from, ActivateDate $order DESC LIMIT $start_from, Gravity $order +0 DESC LIMIT $start_from, InitialEarningsPerSale $order +0 DESC LIMIT $start_from, AverageEarningsPerSale $order +0 DESC LIMIT $start_from, 50";
 

and even trying out just a simple version...

 

$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY Gravity DESC, InitialEarningsPerSale DESC, AverageEarningsPerSale DESC, Links ASC";
 

spits out...

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/site8/public_html/CB/search.php on line 36

 

 

$default_sort = 'Gravity';
$allowed_order = array ('Link', 'Gravity','ActivateDate', 'InitialEarningsPerSale', 'AverageEarningsPerSale');
$search = $_GET['q'];

/* if order is not set, or it is not in the allowed
 * list, then set it to a default value. Otherwise,
 * set it to what was passed in. */
if (!isset ($_GET['order']) ||
    !in_array ($_GET['order'], $allowed_order)) {
    $order = $default_sort;
} else {
    $order = $_GET['order'];
}

/* construct and run our query */
if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * 50;
$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY Gravity DESC, InitialEarningsPerSale DESC, AverageEarningsPerSale DESC, Links ASC";

$result = mysql_query ($query);

/* make sure data was retrieved '$' */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

[trq]

No kidding.

[strago]

NOW I understand why it made no sense. I was trying to get the order by settings the wrong way. This does it, using an if statement based on the URL...

 

 

if($_GET['order'] == 'Link') {
$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY $order ASC LIMIT $start_from, 50";
}
if($_GET['order'] == 'Gravity') {
$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY $order +0 DESC LIMIT $start_from, 50";
}
if($_GET['order'] == 'ActivateDate') {
$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY $order DESC LIMIT $start_from, 50";
}
if($_GET['order'] == 'InitialEarningsPerSale') {
$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY $order +0 DESC LIMIT $start_from, 50";
}
if($_GET['order'] == 'AverageEarningsPerSale') {
$query = "SELECT Link,Gravity,ActivateDate,CONCAT('$', InitialEarningsPerSale) as InitialEarningsPerSale,CONCAT('$', AverageEarningsPerSale) as AverageEarningsPerSale FROM clickbank WHERE `Title` LIKE '%{$search}%' ORDER BY $order +0 DESC LIMIT $start_from, 50";
}
$result = mysql_query ($query);

/* make sure data was retrieved '$' */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
    echo "No data to display!";
    exit;
}

[trq]

Cool. Fancy marking your thread resolved then?