[PHP] Evenementen in database oplopend sorteren met eerstvolgende datum vanboven
Ik heb met php en mysql een lijst met evenementen gegenereerd, en wil deze graag oplopend sorteren met eerstvolgende datum bovenaan.
MAAR, ik wil ook graag de voorbije evenementen onderaan de lijst zetten.
Is dit mogelijk met één of twee queries en misschien in één while loop?
Lijst voorbeeld:
- vandaag
- morgen
- overmorgen
- daarna
- daarna
- gisteren
- eergisteren
- daarvoor
- daarvoor
Bedankt alvast.
Vandaag
Morgen
Overmorgen
Na overmorgen
Na na overmorgen
Toevoeging op 09/03/2012 15:32:59:
Ik ken je query niet maar ik neem aan dat hij er zo uitziet.
id (ai/primary) date (unix timestamp), event.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php
$query = mysql_query("SELECT date, event FROM events");
$huidige_timestamp = date();
while ($rij = mysql_fetch_row($query))
{
if ($row[0]<=$huidige_timestamp)
{
echo "<s>".$rij[0].$rij[1]."</s>";
}
elseif ($row[0]>=$huidige_timestamp)
{
echo "<b>".$row[0].$row[1]."</b>";
}
}
?>
$query = mysql_query("SELECT date, event FROM events");
$huidige_timestamp = date();
while ($rij = mysql_fetch_row($query))
{
if ($row[0]<=$huidige_timestamp)
{
echo "<s>".$rij[0].$rij[1]."</s>";
}
elseif ($row[0]>=$huidige_timestamp)
{
echo "<b>".$row[0].$row[1]."</b>";
}
}
?>
Gewijzigd op 09/03/2012 15:24:52 door Albert de Wit
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT field1, field2, evdate
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate
UNION
SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate
UNION
SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC
Gewijzigd op 09/03/2012 16:12:34 door Ger van Steenderen
@Ger: Die query zorgt bij mij voor een error: Incorrect usage of UNION and ORDER BY.
EDIT: Als ik de eerste ORDER BY weglaat wordt de query uitgevoerd, maar dan is dus alles gesorteerd op de manier van de laatste ORDER BY.
Gewijzigd op 09/03/2012 20:01:16 door Xaboteur X
@Ger UNION kan inderdaad niet met 2x ORDER BY, oplossen met inline views
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.field1, a.field2, a.evdate
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) a,
(
SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) b
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) a,
(
SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) b
Bedankt voor je query tip!
Alleen heb ik met wat zoekwerk gevonden dat er voor a en b, AS moet staan?
Hoewel ik dan nog steeds een query error krijg met: "Unknown table 'a' in field list"
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT a.field1, a.field2, a.evdate
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) AS a,
(
SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) AS b
[/CODE]
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) AS a,
(
SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) AS b
[/CODE]
Ditmaal deze: "Every derived table must have its own alias"
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.field1, a.field2, a.evdate
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events AS a
WHERE evdate >= DATE(NOW())
ORDER BY evdate ),
(
SELECT field1, field2, evdate
FROM events AS b
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC )
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events AS a
WHERE evdate >= DATE(NOW())
ORDER BY evdate ),
(
SELECT field1, field2, evdate
FROM events AS b
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC )
Gewijzigd op 11/03/2012 13:14:10 door Xaboteur X
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.field1, a.field2, a.evdate
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events AS a
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) a,
(
SELECT field1, field2, evdate
FROM events as b
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) b
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events AS a
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) a,
(
SELECT field1, field2, evdate
FROM events as b
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) b
Volgens mij zijn de AS a en AS b niet nodig en voor de duidelijkheid, er is geen relatie tussen a als derived table en AS a als alias.
Gewijzigd op 13/03/2012 21:10:28 door Aad B
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.field1, a.field2, a.evdate
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) a,
(
SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) b
union
SELECT b.field1, b.field2, b.evdate
FROM (
SELECT field1, field2, evdate
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) a,
(
SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) b
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
mysql> select a.id
from (select id from klant_order where id =200) as a
union
select b.id
from (select id from klant_order where id =201) as b;
+-----+
| id |
+-----+
| 200 |
| 201 |
+-----+
2 rows in set (0.00 sec)
from (select id from klant_order where id =200) as a
union
select b.id
from (select id from klant_order where id =201) as b;
+-----+
| id |
+-----+
| 200 |
| 201 |
+-----+
2 rows in set (0.00 sec)
de syntax volgorde is dus aangepast:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
SELECT a.field1, a.field2, a.evdate
FROM (SELECT field1, field2, evdate
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) as a
UNION
SELECT b.field1, b.field2, b.evdate
FROM (SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) as b
FROM (SELECT field1, field2, evdate
FROM events
WHERE evdate >= DATE(NOW())
ORDER BY evdate ) as a
UNION
SELECT b.field1, b.field2, b.evdate
FROM (SELECT field1, field2, evdate
FROM events
WHERE evdate < DATE(NOW())
ORDER BY evdate DESC ) as b
Gewijzigd op 13/03/2012 21:31:00 door Aad B
Bedankt!