Jump to content


Photo

Different ORDER BY setting for different mySQL columns.


Best Answer strago, 28 April 2013 - 01:31 AM

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;
}

Go to the full post


  • Please log in to reply
9 replies to this topic

#1 strago

strago

    Advanced Member

  • Members
  • PipPipPip
  • 89 posts
  • LocationPlanet Zeekois

Posted 26 April 2013 - 11:41 PM

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";
 

 


.

#2 trq

trq

    Advanced Member

  • Administrators
  • 31,019 posts
  • LocationSydney, Australia.

Posted 27 April 2013 - 12:16 AM

Your issue is as clear as mud.

http://thorpesystems.com | http://proemframework.org | http://github.com/trq

SmtpCatcher - A very simple mock sendmail useful for testing PHP mail scripts.
OPM - My Linux package manager.


#3 strago

strago

    Advanced Member

  • Members
  • PipPipPip
  • 89 posts
  • LocationPlanet Zeekois

Posted 27 April 2013 - 01:19 AM

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";


.

#4 trq

trq

    Advanced Member

  • Administrators
  • 31,019 posts
  • LocationSydney, Australia.

Posted 27 April 2013 - 03:21 AM

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

http://thorpesystems.com | http://proemframework.org | http://github.com/trq

SmtpCatcher - A very simple mock sendmail useful for testing PHP mail scripts.
OPM - My Linux package manager.


#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,370 posts
  • LocationCheshire, UK

Posted 27 April 2013 - 05:20 AM

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

On the same column?


moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#6 trq

trq

    Advanced Member

  • Administrators
  • 31,019 posts
  • LocationSydney, Australia.

Posted 27 April 2013 - 06:22 AM

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

http://thorpesystems.com | http://proemframework.org | http://github.com/trq

SmtpCatcher - A very simple mock sendmail useful for testing PHP mail scripts.
OPM - My Linux package manager.


#7 strago

strago

    Advanced Member

  • Members
  • PipPipPip
  • 89 posts
  • LocationPlanet Zeekois

Posted 27 April 2013 - 10:55 PM

$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;
}

.

#8 trq

trq

    Advanced Member

  • Administrators
  • 31,019 posts
  • LocationSydney, Australia.

Posted 27 April 2013 - 11:11 PM

No kidding.

http://thorpesystems.com | http://proemframework.org | http://github.com/trq

SmtpCatcher - A very simple mock sendmail useful for testing PHP mail scripts.
OPM - My Linux package manager.


#9 strago

strago

    Advanced Member

  • Members
  • PipPipPip
  • 89 posts
  • LocationPlanet Zeekois

Posted 28 April 2013 - 01:31 AM   Best Answer

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;
}


Edited by strago, 28 April 2013 - 01:31 AM.

.

#10 trq

trq

    Advanced Member

  • Administrators
  • 31,019 posts
  • LocationSydney, Australia.

Posted 28 April 2013 - 04:07 AM

Cool. Fancy marking your thread resolved then?

http://thorpesystems.com | http://proemframework.org | http://github.com/trq

SmtpCatcher - A very simple mock sendmail useful for testing PHP mail scripts.
OPM - My Linux package manager.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com