PHP/MySQL - Kategorisystem

Tjena!

Har ett problem vi brottats med ett tag nu. Det är ganska omfattande, så jag ska försöka göra det så kortfattat som möjligt.

Vi håller på med en hemsida där användare ska kunna välja sina kompetenser. Varje användare kan alltså ha flera kompetenser, och varje kompetens kan ha x antal kategorier - antalet kategorieroch levlar kan variera hos olika kompetenser.

De största kraven är enligt följande:
- Enkelt kunna lägga till/ta bort kompetenser och dess kategorier.
- Möjlighet att få fram alla huvudkategorier.
- Kunna få fram alla kategorier hos en kompetens.
- Kunna ha olika många levlar kategorier hos en kompetens.
- Enkelt att byta namn/plats etc. hos olika kompetenser.

Och så ska förstås systemet vara logiskt och inte ta mer prestanda än nödvändigt. Vi har tänkt igenom 3 olika metoder för att åstadkomma detta, och jag tänkte presentera den jag tycker är bäst.

Då har vi följande tabeller i databasen:
- users(id, username)
- competences(id, parent, name)
- list_competences(user_id, competence_id)

Och så tar vi lite exempeldata...

Olle har följande kompetenser:
Administration->Arbetsledning
Språk->Tolk->Polska
Filosofi

Pelle har följande kompetenser:
Hantverke->Snickeri
Språk->Tolk->Arabiska, Persiska

I det här fallet ser databasen kanske ut så här:

I tabellen competences ligger alltså själva kompetenserna som refereras till i list_competences. Kolumnen parent anger till vilken kategori kompetensen tillhör - om kompetensen inte tillhör till någon kategori eller är en huvudkategori så är detta satt till 0.

Så att lista alla huvudkategorier är inga konstigheter:

SELECT id, name FROM competences WHERE parent = 0;

Men hur gör jag för att lista huvudkategoriernas respektive underkategorier? Och hur listar jag upp användarnas kompetenser insorterat i sina kategorier? Det största problemet är som sagt att olika kompetenser kan ha olika många kategorier/levlar.

Kanske det går att skapa en MySQL-funktion som loopar igenom kompetenserna?

Bara att säga till om något är oklart. Var inte blyg för att komma med tips!

Tack på förhand!

Nu har jag läst igenom hela sidan. Måste säga att det är en smart idé men med sjukt långa queries - är inte det här påfrestande för databasen? Och är metoden "säker", dvs. det finns väl ingen risk för att någon av de många momenten skiter sig och ställer till med problem i hela tabellen?

Okej, hur skulle en sådan kontroll se ut då?
Man vill ju vara säker på att inget kan gå snett.

Nu har jag lekt runt en del med nested sets, och det fungerar väldigt bra. Men en enorm nackdel jag stötte på var att man inte kan sortera kategorierna i bokstavsordning. Kanske du kan ett smart sätt för det? Om det nu inte går så skulle jag vilja veta hur man får in datan i en array så att man kan sortera kategorierna där. Har sökt runt en hel del men utan vettiga resultat.
Sorteringen är ett mycket viktigt moment inom just detta område.

Och så är jag nyfiken på hur man hämtar alla huvudkategorier.

Tack på förhand.

Du har inget att ursäkta, ditt svar var högst uppskattat.

Jo en klass för detta har jag, och det fungerar jättesmidigt; för tillfället har jag $data_comp->add($parent, $node) och $data_comp->tree(). När jag fått upp de funktioner som behövs och sett till att allt fungerar som det ska så ska jag även lägga in de här långa anropen i stored procedures.

Det enda jag behöver sortera efter är egentligen namnen. Om man bara ska en generation (a.k.a. "djup" eller "level") så lär det ju inte vara några konstigheter. Men på ett ställe (där man lägger till en ny kompetens) ska jag presentera alla kompetenser och dess underkategorier, och då är jag beroende av att de ligger i ordning då det annars blir väldigt rörigt att hitta rätt. Vill inte fråga alltför många frågor, men just det här skulle jag uppskatta väldigt mycket om jag kunde få hjälp med; antingen, som du nämnde, ladda in datan till en multidimensionell array (ingen aning om hur det skulle gå till i nested sets, men det kanske går att använda den nya kolumnen parent du tipsade om eller trolla lite med depth) eller lösa det på något annat sätt.

Lite osäker med exakt vad du menar. Det här är inget jag kommer att tjäna pengar på, om det är det du syftar på.
Jag tycker om att bygga allt från grunden då man lättare håller koll på vad som är vad (möjligen ha andra koder som referens) om det nu inte rör sig om större bibliotek, ex jQuery.

Hm, okej. Får nog kika på det om jag inte får till det själv (vilket jag faktiskt skulle föredra).

Anledningen till att jag tog jQuery som exempel är att det är ett av de få biblioteken (oavsett språk) jag tycker är ruggigt bra.

Är väl just det man använder kommentarer till, om man nu inte verkligen vill skapa ett beroende.
Lite svårt att beskriva projektet. Kan PM:a länk till sidan om du är intresserad.

Men återigen någon, hur laddar man in data från nested sets till en multidimensionell array? Hoppas på ett svar från Biberu igen, hehe.

Jo jag funderade faktiskt på det i början, men det känns onödigt. Man kan väl lika gärna serializa arrayn och spara den i en textfil, känns som att det skulle gå snabbare och samtidigt ta betydligt mindre plats. Men eftersom att alla kompetenser kan ha olika många kategorier så vet jag inte riktigt hur jag skulle göra med looparna.

Får testa mig fram lite, men jag fortfarande uppskatta om någon skulle ha en lösning för detta.

Det förstod jag.
Ska leka mig fram lite.

Nu känns det som att jag kommit en bit på vägen med att sortera datan. Så här ser funktionen ut just nu:

function tree_sorted()
{
	//Get data
	$query = 'SELECT node.id, node.name, node.parent, (COUNT(parent.name) - 1) AS depth FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft';
	$result = mysql_query($query) or die(mysql_error());
			
	//Fetch gotten data
	while($data = mysql_fetch_assoc($result))
	{
		$fetched[$data['depth']][$data['id']] = array($data['name'], $data['parent']);
	}
			
	//Sort fetched data
	foreach ($fetched as $i => $row)
	{
		asort($row);
		$sorted[$i] = $row;
	}
			
	//Merge sorted data
	foreach($sorted as $i => $arr)
	{
		foreach($arr as $x => $row)
		{
			$returnarray[] = array('id' => key($row), 'name' => $row[0], 'depth' => $x);
		}
	}
	
	//Return merged data
	return $returnarray;
}

Tanken är att få ut datan som följande: array(id, name, depth).
Precis som i min funktion nedan:

function tree()
{
	$query = 'SELECT node.id, node.name, (COUNT(parent.name) - 1) AS depth FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft';
	$result = mysql_query($query) or die(mysql_error());
	
	while($data = mysql_fetch_assoc($result))
	{
		$returnarray[] = $data;
	}
	
	return $returnarray;
}

För tillfället så blir alla kompetenser sorterade i tree_sorted(), men även insorterade i vilken nivå de ligger på (depth).
Därför undrar jag om någon skulle vilja hjälpa mig bli klar med funktionen? Osäker på hur jag ska göra härnäst för att placera underkategorierna efter sin parent, precis som i funktionen tree().