sorting arrays

[cleibesouza]

Hi all, here's my question.

I have some data coming from a view. It's a simple select.

"SELECT * FROM viewName where id = $id ORDER BY session_begin_ts DESC"

 

This view gives me future and past events that are held online and future and past events that are held on site.

 

What I'm trying to accomplish (whithout running the same query more than once) is to have online events on the top and here's the twist. The online events need to be ORDERED BY registration_ts which isn't the same ORDER field in the query(session_begin_ts DESC).

 

The query is written this way because when it's time to display on site events it will give me future events above past events, and that's the order it should be for on site events.

 

So, I'm trying to dump all online events into arrays and them sort them from newer to older  but no success until now. There are 14 fields that I need to grab. 

 

Any help is appreciated.

[Mchl]

(SELECT * FROM viewName WHERE id = $id AND (condition to select online events) ORDER BY registration_ts)
UNION
(SELECT * FROM viewName WHERE id = $id AND (condition to select on site events) ORDER BY session_begin_ts)

[cleibesouza]

Thank you for the quick reply. I must be doing something wrong. Here's what I have:

$registrationQuery = "(SELECT * FROM view WHERE person_pk = \"$ID\" AND event_structure_fk <> '4' ORDER BY session_begin_ts DESC)
UNION
(SELECT * FROM view WHERE person_pk = \"$ID\" AND event_structure_fk = '4' ORDER BY registration_ts DESC)";

event_structure_fk is what gives me online or onsite events

---

This is the error I'm getting:

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'ORDER'. (severity 15) in ...

---

This thread got moved from the php forum and I didn't mention that I'm using SQL server and not MySQL.

 

Thank you.

 

[Mchl]

You're using MSSQL not MySQL. Syntax might be different in MSSQL, but unfortunately I've no experience with it

[cleibesouza]

Any other idea on how to solve this other than dealing with the query itself?

Maybe solving with php?

 

Thanks.

[Mchl]

You could divide the result set into two arrays depending on event type, then sort each array using uksort. Then just display one below other.

[cleibesouza]

That's what I've been trying to do for 2 days but no success.

[premiso]

As to why the ORDER BY does not work:

 

You can not perform an ORDER BY on a text, ntext, or image field (those fields are actually pointers).

 

And sorry for moving the topic, I did not know that you were trying to make an array and sort it that way so here goes my attempt to help ya. I am taking that your dates are not in a timestamp form, (this is just a rough design if you want it done better, please post the array data inside of

 tag from a print_r).
 
[code]<?php

function datediff($a, $b) {
$a = strtotime($a);
$b = strtotime($b);

if ($a == $b) $r = 0;
else $r = ($a > $b) ? 1: -1;

return $r;
}

$events = array(1 => array("Some Title", "10/24/2009", "etc", "etc"), 2 => array("Other TItle", "11/25/2009", "etc", "etc"), 3 => array("3rd Title", "6/5/2009", "etc", "Etc"), 4 => array("4th Title", "7/4/2009", "etc", "etc"));
$dates = array();
foreach ($events as $id => $subArr) {
$dates[$id] = $subArr[1];
}

uasort($dates, "datediff");

foreach ($dates as $key => $val) {
echo "Event Title: " . $events[$key][0] . " on date: " . $val . "<Br />";
}
?>

 

Hopefully that helps you some, if you have any questions on how / why I did certain items let me know!

 

[cleibesouza]

I've actually solved the problem by creating 2 CASE statements in the query to figure out event type and if a date is in the future or not.

 

Thank you for all your help.

Greatly appreciated.