eenvoudig 'forum': ophalen aantal reacties en tijdstip laatste reactie per topic
Relevante velden in mijn database:
id
topic_id
plaatsingstijd
Elke post krijgt een eigen id.
Als het om een topic gaat, dan is topic-id 0.
Wordt er gereageerd op een topic dan wordt in topic_id het id van het betreffende topic opgeslagen.
Wat ik zou willen weten is of het mogelijk is om in 1 query alle topics op te halen uit de database (dat lukt wel) en daarnaast, als er reacties zijn, het aantal reacties op een topic en de datum van de laatste reactie.
Zelf zou ik het in 2 of 3 query's doen, maar als ik met name de creatieve (geneste) query´s van o.a. Ger zie op het forum, ben ik benieuwd of het ook in 1x kan.
Obelix en Idefix op 09/06/2013 11:13:32:
Relevante velden in mijn database:
id
topic_id
plaatsingstijd
id
topic_id
plaatsingstijd
een forum bestaat in ieder geval al uit minimaal drie belangrijke objecten.
1. users
2. topics
3. berichten
lijkt mij dat je daar ook minimaal je database op gaat inrichten.
een user kan meerdere topics starten, maar een topic kan maar één topic-starter hebben: one -> many
een user kan meerdere berichten plaatsen. een bericht heeft maar één schrijver. one -> many
een topic kan meerdere berichten hebben. een bericht hoort in één topic. one -> many
drie tabellen dus:
user:
user_id
name
password
...
topic:
topic_id
user_id (topic starter)
created (datetime)
title
...
bericht:
bericht_id
topic_id
user_id
created (datetime)
message (het bericht)
pak eens pen en papier want je database is veel belangrijker dan het vinden van de juiste queries
Gewijzigd op 09/06/2013 11:29:38 door Frank Nietbelangrijk
Wat betreft een aparte tabel voor topic / bericht: veel velden in de tabellen zijn gelijk. Waarom dan niet in 1 tabel? Ook bij een menu met submenu's heb ik dat gezien; gewoon in 1 tabel. Of maak je daar ook 2 tabellen voor aan?
@Erwin H
Heb drie queries en dat werkt.
Heb, met zoeken op internet, geprobeerd om te gaan joinen.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
SELECT
tabel1.id,
tabel1.topic_id,
DATE_FORMAT(tabel1.plaatsingstijd, '%d-%m-%Y') as topic_datum,
DATE_FORMAT(tabel1.plaatsingstijd, '%H:%i:%S') as topic_tijd,
tabel2.aantal_reacties
FROM
topics tabel1
JOIN
(SELECT
COUNT(topic_id) AS aantal_reacties,
id,
topic_id
FROM
topics
WHERE
topic_id=12) as tabel2
ON
tabel2.topic_id = tabel1.id
WHERE
tabel1.topic_id = 0
ORDER BY
tabel1.plaatsingstijd DESC
?>
SELECT
tabel1.id,
tabel1.topic_id,
DATE_FORMAT(tabel1.plaatsingstijd, '%d-%m-%Y') as topic_datum,
DATE_FORMAT(tabel1.plaatsingstijd, '%H:%i:%S') as topic_tijd,
tabel2.aantal_reacties
FROM
topics tabel1
JOIN
(SELECT
COUNT(topic_id) AS aantal_reacties,
id,
topic_id
FROM
topics
WHERE
topic_id=12) as tabel2
ON
tabel2.topic_id = tabel1.id
WHERE
tabel1.topic_id = 0
ORDER BY
tabel1.plaatsingstijd DESC
?>
In phpadmin geeft dit wat ik zou verwachten, maar zit ik nog met een probleem.
Ik geef nu ´hard´ in dat topic_id 12 moet zijn, maar dan krijg ik maar voor 1 topic het aantal reacties te zien. Dat moet variabel zijn. Heb met Google nog niet kunnen vinden hoe ik een veld uit de SELECT kan gebruiken in een query. Als ik de WHERE in de join weglaat, wordt niets getoond.
Iemand een tip om me verder te helpen?
(ik heb overigens id weggelaten in de subquery, want dat is een nietszeggend veld in dit geval)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
$topicid = 12;
$query = "SELECT
tabel1.id,
tabel1.topic_id,
DATE_FORMAT(tabel1.plaatsingstijd, '%d-%m-%Y') as topic_datum,
DATE_FORMAT(tabel1.plaatsingstijd, '%H:%i:%S') as topic_tijd,
tabel2.aantal_reacties
FROM
topics tabel1
JOIN
(SELECT
COUNT(topic_id) AS aantal_reacties,
topic_id
FROM
topics
WHERE
topic_id=".$topicid.") as tabel2
ON
tabel2.topic_id = tabel1.id
WHERE
tabel1.topic_id = 0
ORDER BY
tabel1.plaatsingstijd DESC"
?>
$topicid = 12;
$query = "SELECT
tabel1.id,
tabel1.topic_id,
DATE_FORMAT(tabel1.plaatsingstijd, '%d-%m-%Y') as topic_datum,
DATE_FORMAT(tabel1.plaatsingstijd, '%H:%i:%S') as topic_tijd,
tabel2.aantal_reacties
FROM
topics tabel1
JOIN
(SELECT
COUNT(topic_id) AS aantal_reacties,
topic_id
FROM
topics
WHERE
topic_id=".$topicid.") as tabel2
ON
tabel2.topic_id = tabel1.id
WHERE
tabel1.topic_id = 0
ORDER BY
tabel1.plaatsingstijd DESC"
?>
Een andere optie is om de helemaal niet te selecteren daarop in je subquery, maar om die over alle topics te draaien. Dan heb je alleen wel een GROUP BY nodig in die subquery:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT
tabel1.id,
tabel1.topic_id,
DATE_FORMAT(tabel1.plaatsingstijd, '%d-%m-%Y') as topic_datum,
DATE_FORMAT(tabel1.plaatsingstijd, '%H:%i:%S') as topic_tijd,
tabel2.aantal_reacties
FROM
topics tabel1
JOIN
(SELECT
COUNT(*) AS aantal_reacties,
topic_id
FROM
topics
GROUP BY
topic_id) as tabel2
ON
tabel2.topic_id = tabel1.id
WHERE
tabel1.topic_id = 0
ORDER BY
tabel1.plaatsingstijd DESC
tabel1.id,
tabel1.topic_id,
DATE_FORMAT(tabel1.plaatsingstijd, '%d-%m-%Y') as topic_datum,
DATE_FORMAT(tabel1.plaatsingstijd, '%H:%i:%S') as topic_tijd,
tabel2.aantal_reacties
FROM
topics tabel1
JOIN
(SELECT
COUNT(*) AS aantal_reacties,
topic_id
FROM
topics
GROUP BY
topic_id) as tabel2
ON
tabel2.topic_id = tabel1.id
WHERE
tabel1.topic_id = 0
ORDER BY
tabel1.plaatsingstijd DESC
Nu kan je in de where van de algemene query eventueel gaan selecteren op topics.
Gewijzigd op 09/06/2013 15:34:35 door Erwin H
query voor de laatste 10 reacties:
Code (php)
1
SELECT * FROM `message` ORDER BY `message_id` DESC LIMIT 10
query voor de laatste 10 topics:
Code (php)
1
SELECT * FROM `topic` ORDER BY `topic_id` DESC LIMIT 10
query voor benodigde topic info:
Code (php)
1
2
3
4
2
3
4
SELECT `name`, `created`, `title`
FROM `topic`
LEFT JOIN `user` ON `user`.`user_id`=`topic`.`user_id`
WHERE `topic_id`=2
FROM `topic`
LEFT JOIN `user` ON `user`.`user_id`=`topic`.`user_id`
WHERE `topic_id`=2
query voor alle berichten uit topic nr 2 met de username:
Code (php)
1
2
3
4
5
2
3
4
5
SELECT `name`, `created`, `message`
FROM `message`
LEFT JOIN `user` ON `user`.`user_id`=`message`.`user_id`
WHERE `topic_id`=2
ORDER BY `message_id`
FROM `message`
LEFT JOIN `user` ON `user`.`user_id`=`message`.`user_id`
WHERE `topic_id`=2
ORDER BY `message_id`
Toevoeging op 09/06/2013 16:06:44:
Dit is gewoon de basis en zoals je kunt zien veel logischer en de queries zijn eenvoudig. Hierop kun je verder bouwen en ook eventueel de topics in een categorie plaatsen. Je krijgt dan exact de zelfde opzet als dit forum.
De group by was wat ik miste voor een juiste werking. Dank.
Toevoeging op 10/06/2013 18:56:40:
Toch nog wat hulp nodig.
Probeer nu ook het tijdstip van de laatste reactie er nog bij te krijgen (bij meerdere reacties op een topic):
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?php
SELECT
tabel1.id,
DATE_FORMAT(tabel1.plaatsingstijd, '') as topic_datum,
DATE_FORMAT(tabel1.plaatsingstijd, '%H:%i:%S') as topic_tijd,
tabel1.plaatsingstijd,
tabel2.aantal_reacties,
tabel3.laatste_reactie
FROM
topics tabel1
LEFT JOIN
(SELECT
COUNT(topic_id) AS aantal_reacties,
id,
topic_id
FROM
topics
GROUP BY
topic_id) as tabel2
ON
tabel2.topic_id = tabel1.id
LEFT JOIN
(SELECT
DATE_FORMAT(plaatsingstijd, '%H:%i:%S') as laatste_reactie,
DATE_FORMAT(plaatsingstijd, '%d-%m-%Y %H:%i:%S') as sorteer_tijdstip,
id,
topic_id
FROM
topics
GROUP BY
topic_id
ORDER BY
sorteer_tijdstip DESC) as tabel3
ON
tabel3.topic_id = tabel1.id
WHERE
tabel1.topic_id = 0
ORDER BY
tabel1.plaatsingstijd DESC
?>
SELECT
tabel1.id,
DATE_FORMAT(tabel1.plaatsingstijd, '') as topic_datum,
DATE_FORMAT(tabel1.plaatsingstijd, '%H:%i:%S') as topic_tijd,
tabel1.plaatsingstijd,
tabel2.aantal_reacties,
tabel3.laatste_reactie
FROM
topics tabel1
LEFT JOIN
(SELECT
COUNT(topic_id) AS aantal_reacties,
id,
topic_id
FROM
topics
GROUP BY
topic_id) as tabel2
ON
tabel2.topic_id = tabel1.id
LEFT JOIN
(SELECT
DATE_FORMAT(plaatsingstijd, '%H:%i:%S') as laatste_reactie,
DATE_FORMAT(plaatsingstijd, '%d-%m-%Y %H:%i:%S') as sorteer_tijdstip,
id,
topic_id
FROM
topics
GROUP BY
topic_id
ORDER BY
sorteer_tijdstip DESC) as tabel3
ON
tabel3.topic_id = tabel1.id
WHERE
tabel1.topic_id = 0
ORDER BY
tabel1.plaatsingstijd DESC
?>
Als ik dit doe, wordt echter het tijdstip van de 1e reactie getoond en niet van de laatste. Heb diverse varianten geprobeerd met de ON van de laatste JOIN, maar zonder succes.
Gewijzigd op 10/06/2013 18:56:26 door Obelix Idefix