Posted by Richard Lynch on 02/23/05 19:24

Reinhart Viane wrote:
> Hey list
>
> I have a mysql table like this:
>
> Act_name Act_type_id Act_date
>
> Heyhey 1 22-06-05
>
> Aloha 2 22-06-05
>
> Tralala 2 22-06-05
>
> Wuhu 1 22-06-05
>
> Hehe 3 22-06-05
>
> Olalal 3 22-06-05
>
> Pompom 1 22-06-05
>
> Wuhu 2 22-06-05
>
> Now I retrieve all activities happening in the future with this query:
>
> $sqlact="select * from activities where act_date >= NOW() order by
> act_type_id";
>
> $getact=mysql_query($sqlact)
>
> What I'm trying to do now is:
>
> From the result array, pick from every different act_type_id the two
> activities that will happen first and put them in 2 variables

Change your "ORDER BY" to : "order by Act_date"

That's the only way you're gonna get them in date order to get the NEXT
two in time.

Then you need to get just two of each kind, which SQL isn't really geared
to do, at least not easily.

Hopefully, you'll never have, like, *THOUSANDS* of upcoming Acts...

$next_two = array();
while (list($name, $type, $date) = mysql_fetch_row($getact)){
if (!isset($next_two[$type]) || count($next_two[$type]) < 2){
$new_two[$type][] = "$name $date";
}
}

You now have an array $new_two with indices of the Act's type, and values
of arrays of at most 2 entries, with "$name $date".

If you need $name and $date separated, you could use: array($name, $date)
instead of "$name $date" inside the loop.

You'll need to use some kind of loop to go through the $next_two array.

--
Like Music?
http://l-i-e.com/artists.htm

[Back to original message]