generating scalable category headers

Anon

probably an associative array question. maybe.

i have a publishing system of sorts for a website that contains quite a few categories. let's say for example it's something like:

coffee
computers
foo
music
sheep

when posting a new piece of content, i've
populated a dropdown where i can select one of these types, or add / edit / remove them from the drop down as needed (a seperate table lists them, and i just left join it all together on the backend)

this works all nice and good, and when i spit the whole thing out i sort by name, so my output might look something like:

1] the summer i was wired - (coffee)
2] why juan valdez should be president - (coffee)
3] kernal hacking for fun and prophalactics - (computers)
4] radiohead burps - (music)
5] sam coones hates work some more - (music)
6] baaaaaaa - (sheep)

you get the idea.

the problem is that instead of doing a print "$article - ($type)"; sort of thing, i'd like to just have one "header" title for each type.

now i could do seperate queries to the database, but yuck.

i could also do something along the lines of
if ($values[fruit] == 'apples'), but that involves specifically naming a category, which just doesn't leave the thing very scalable if i decide to add / edit my categories.

so the question is how to encapsulate / group what i've sorted so that a whole group only has one header, while keeping it generic so that it doesn't care as i add / remove types and categories?

tearing hair out;
-a

Anon

First, let me say that I am just learning PHP so I can't offer any help on syntax, etc....

But to scale your database, just put everything in the same table, allowing for each entry to be either a "category" or a "link". If it is a "link" then it will have a parent "category." This will scale to the n.

Anon

well what i was addressing was more that the code should scale to whatever sort of categories i add / edit. the tables themselves can handle pretty much whatever i throw in them, the problem comes in that i need to address that stuff without calling it explicitly in the code.

i'm starting to figure out how i'd do it; basicly making a call to the category table and get a current list of categories, then loop through and populate a second series of loops (that will handle the actual output) with the results of that first query.

then just do the query for content as i normally would, and it'll run through the dynamically generated loop(s).

conceptually i think that makes sense, yes?

now the tricky part will be actually getting that into syntaxical reality. wince

-a

kparker

It's a little hard to tell for sure, since you haven't shown much of your code or database schema, but here goes--please excuse me in advance if this is barking up the wrong tree! :-)

Assuming you have tables as follows:

    create table types
    (
    id int, / primary key /
    name text
   &nbsp😉;

    create table articles
    (
    id int, / primary key /
    type_id int, / references types.id /
    title text,
    / lotsa other columns probably... /
   &nbsp😉;

You can get a list of all articles by type by simply joining the tables:

    select types., articles.
    from types, articles
    where type.id = article.type_id
    order by types.name, article.title

Now it's a simple matter of reading all the results, they will be in order by catogory name + title.

Anon

thanks for that. ironically i just finished getting everything all settled about a moment before your reply, with the help of joe canon over at good old alt.php.

i'm including some code chunks here in case anyone else is interested....

please don't anyone howl over the two left joins. it's a marginally low-traffic site and it made things far easier on my end.

after checking out this stuff below, you can see the page in question (and some screenshots of the admin thing) if you like:

<a href="http://www.graphicsdesign.org/" target="_blank">the page (left side is the stuff i was sorting)</a>

<a href="http://www.graphicsdesign.org/extras/admin/edit_content.gif" target="_blank">the edit content screen</a>

<a href="http://www.graphicsdesign.org/extras/admin/add_cat.gif" target="_blank">add / edit / delete categories</a>

<a href="http://www.graphicsdesign.org/extras/admin/add_linktype.gif" target="_blank">add / edit / delete a type of link (popups of different sizes, regular link, target blank, new, etcetera)</a>

the code for the content include

(content.php)

$category_name = "";

$table = "content";
$table2 = "conaction";
$table3 = "catcall";

$db = mysql_select_db($database, $conn) or die("couldnt select database");

$sql = "
SELECT * FROM $table
LEFT JOIN $table2 ON $table.LID = $table2.LID
LEFT JOIN $table3 on $table.CAT = $table3.CAT
ORDER BY propname, CID DESC
";

$result = mysql_query($sql,$conn) or die("query failed");

while ($data=mysql_fetch_array($result)) {

// thanks to joe cannon for this if

if( $category_name != $data['propname'] ) {
$category_name = $data['propname'];
echo " " . $data['propname'] . " \n"; // print a heading
}

echo $data['actype'];
echo $data['linkurl'];
echo "\",\"";
echo $data['linkname'];
echo "\")</script> \n";

}