[mysql+php] Efficiënt vorige/volgende links maken bij nieuws

[mahi]

Weer eens aan het spelen met PHP en MySQL. Ik ben al jaren bezig aan een eenvoudig content management system (nee, niet jaren aan een stuk maar af en toe nieuwe features toevoegen ). Nu heb ik al een tijd m'n zinnen gezet op het systeem dat tweakers.net gebruikt om z'n nieuwsberichten weer te geven. Onderaan elke nieuwspost staan 2 links; eentje naar het volgende nieuwsbericht, en eentje naar het vorige. Dat maakt het navigeren meer eenvoudiger omdat je niet telkens terug naar de index pagina moet gaan.

Nu heb ik eens liggen denken om dit zelf ook te implementeren, maar dan liefst wel effeciënt (ik ben op oplossingen gekomen die elke SQL-guru zou doen groen uitslaan ).

Het simpelste is natuurlijk in de query waar je het nieuwsbericht uit de database ophaalt uitbreiden via de WHERE zodat ie ook -1 en +1 kijkt. Bijvoorbeeld (vereenvoudigde query):

SELECT * FROM news WHERE news.id >= ($id - 1) AND news.id <= ($id + 1)

Waarbij $id het op te halen artikel is. Maar dit schept wat problemen. Je krijgt nu 3 rijen terug. Maar de database bevat ook verschillende categorieën van nieuws en wat als je het laatste of eerste bericht bekijkt? Dan krijg je geen drie rijen. Of wat wanneer een bericht gedelete werd uit de database?

Even voor de duidelijkheid een zeer simplistisch model van de nieuwsdatabase schetsen. Veld titel is natuurlijk de titel van het nieuwsbericht en bericht de inhoud. Veld categorie duidt aan tot welke categorie het bericht behoort, bijvoorbeeld zoals op Tweakers.net softwarenieuws, hardwarenieuws, gamesnieuws,... Maar bij mijn CMS worden ze in aparte lijsten weergegeven. Er horen dus geen softwarelinks bij hardwarenieuws te komen.

code:

SELECT * FROM news;
+----+--------------+-------------------+-----------+
| id | titel        | bericht           | categorie |
+----+--------------+-------------------+-----------+
|  1 | titel1       | bericht1          |         1 |
|  2 | titel2       | bericht2          |         1 |
|  3 | titel3       | bericht3          |         1 |
|  4 | titel4       | bericht4          |         2 |
|  5 | titel5       | bericht5          |         1 |
|  6 | titel6       | bericht6          |         2 |
|  7 | titel7       | bericht7          |         2 |
|  8 | titel8       | bericht8          |         1 |
|  9 | titel9       | bericht9          |         3 |
| 10 | titel10      | bericht10         |         1 |
+----+--------------+-------------------+-----------+

Stel dat ik het bericht met $id = 7 wil ophalen. Rij 7 behoort tot categorie 2. Dat betekent dat enkel volgende/vorige links naar berichten uit categorie 2 mogen weergegeven worden. Een hogere id dan 7 in categorie 2 is er niet, dus de volgende link vervalt al. Een lagere is er wel, namelijk id 6. Nu valt die toevallig 1 id lager (ivm het eerder vernoemde -1/+1), maar dat is lang niet altijd zo. Wat bijvoorbeeld als je id = 5 wil ophalen?

Het model dat ik probeer te schetsen zou nu toch verstaanbaar moeten zijn. Dus nu de vraag... Hoe giet ik dit in een MySQL query? Bestaat er een opdracht om de eerst dichtstbijzijnde rij te zoeken (ik vond niet meteen iets in de MySQL manual)? Je kunt toch moeilijk alle rijen inlezen en dan manueel de dichtstbijzijnde rijen bij de gevraagde id zoeken. Dat moet toch efficiënter kunnen?

[NMe]

Controleren of iets al dan niet het eerste/laatste artikel in een categorie is, moet geen probleem zijn van je (gewoon het id van het huidige id vergelijken met min/max en dergelijke).

Je andere probleem is ook niet zo lastig. Je kan bijvoorbeeld bij artikel 7 de "vorige" link zo maken:

code:

<a href="jouwpagina.php?id=7&vorige=1">

Zo kun je op die pagina controleren of vorige is aangegeven, en zo ja, dan doe je met SQL het volgende:

code:

SELECT *
FROM artikel
WHERE cat_id = $jouwcatid
AND id < 7
ORDER BY id DESC
LIMIT 1

Zo krijg je altijd het vorige artikel. Bij "volgende" hetzelfde verhaal, maar dan met > ipv < en ASC in plaats van DESC.

[mahi]

Aaah... LIMIT! Het magische woord
Dat gebruik ik dan al heel frequent en nog viel m'n eurocent niet om dat hier ook toe te passen. Damn, ik moet echt eens een cursus logisch redeneren volgen.

De link zelf aanmaken wist ik natuurlijk al. Dat is eenvoudig, maar ik zat nu al 2 dagen te tobben over een efficiënte query en dan is de oplossing zo simpel...

Van harte bedankt!

[Roa]

Wat je ook kan doen, is een timestamp toevoegen.

Dan kan je simpel het vorige newsbericht doen door de volgende query:

code:

SELECT *
FROM artikel
WHERE timestamp < $huidig_bericht_timestamp
AND cat_id = $jouw_cat_id
ORDER BY timestamp DESC
LIMIT 1

Wat ik zelf heb is bij ieder nieuwsbericht links zetten naar de laatste 10 berichten en dan wordt het bericht dat je op dat moment leest uitgesloten.

[NMe]

Dat timestamp voegt niet veel toe voor dit probleem denk ik. De enige plaats waar dat nuttig is, is als je de tijd van een bericht bij elke update verandert, waardoor het weer bovenaan komt te staan. Verder is het gewoon hetzelfde als het gebruiken van een id.

[Orphix]

NMe84 schreef op 04 juli 2004 @ 14:41:
Dat timestamp voegt niet veel toe voor dit probleem denk ik. De enige plaats waar dat nuttig is, is als je de tijd van een bericht bij elke update verandert, waardoor het weer bovenaan komt te staan. Verder is het gewoon hetzelfde als het gebruiken van een id.

Toch zou ik gebruik maken van die timestap. Een id is enkel om de rij uniek te identificeren, niks anders. Zodra je informatie gaat onttrekken aan id's ben je verkeerd bezig. Wat moet er gebeuren als er een nieuwbericht verwijdert? Of als je nieuwsberichten gaat samenvoegen?
De auto_increment column van MySQL gaat als je bv id '3' verwijdert gewoon verder met '4' en vanuit het nieuwsartikel met id '2' heb je dan geen 'volgend' nieuwsartikel.

[NMe]

Orphix schreef op 04 juli 2004 @ 16:14:
[...]

Toch zou ik gebruik maken van die timestap. Een id is enkel om de rij uniek te identificeren, niks anders. Zodra je informatie gaat onttrekken aan id's ben je verkeerd bezig. Wat moet er gebeuren als er een nieuwbericht verwijdert? Of als je nieuwsberichten gaat samenvoegen?
De auto_increment column van MySQL gaat als je bv id '3' verwijdert gewoon verder met '4' en vanuit het nieuwsartikel met id '2' heb je dan geen 'volgend' nieuwsartikel.

Kijk eens goed naar mijn query voordat je dingen roept... Die query slaat gewoon netjes het id 3 over en laat 4 zien.
Het id is hier wel degelijk voor te gebruiken, misschien zelfs beter dan een timestamp, want volgens mij kan er sneller met integers gerekend worden dan met timestamps, omdat een timestamp nog eens intern teruggevoerd moet worden naar een integer. Verder is het puur wat je zelf wilt. Ik zelf zou een id gebruiken als ik niet bij elke update van een artikel dat artikel bovenaan wil zetten (zoals ze op Fok! doen), en een timestamp als ik dat juist wel wil.

[ Voor 6% gewijzigd door NMe op 04-07-2004 16:27 ]

[Soultaker]

De fundamentele vraag is of je een id moet beschouwen als een 'ondoorzichtige' identifier; je weet dat 'ie uniek is voor een bepaalde rij maar verder weet je niets over zijn waarde. Om de queries die gesuggereerd werden correct te laten werken is het vereist dat een bericht een hoger id heeft dan alle berichten die eerder zijn toegevoegd.

Omdat MySQL id's niet hergebruikt en steeds een hoger id genereert, gaat het in dit geval wel goed, maar dan maak je wel gebruik van een specifieke eigenschap van MySQL. Als je een andere database zou gebruiken of je zou je gegevens importeren uit een andere bron (en dus niet door ze achtereenvolgens te inserten) dan zou je die eigenschap van id's misschien wel eens kwijt kunnen zijn.

In dit geval is het wel acceptabel om te zeggen: niet zeuren, lekker profiteren van de eigenschappen van MySQL. Principieel gezien heeft Orphix naar mijn mening echter wel een punt: het is niet zo netjes om zomaar uit te gaan van een ordening van dit soort id's. Aangezien je bij nieuwsberichten toch de datum/tijd van publicatie op wilt slaan (lijkt me) is het niet echt onlogisch om te suggeren om daar dan ook op te sorteren (en daarna pas op id, om berichten met gelijke datum/tijd te onderscheiden).

[nicemister]

Je kunt de vorige en volgende rij pakken uit je database door die tabel 2 keer te selecteren in je query en dan een vergelijking maken met een limit van 1 erbij.