[SOLVED] Multiple Categories For Blog

[EternalSorrow]

I'm currently creating a simple blog/database for an article database.  I wish to assign multiple categories, or tags, to each article but I can't seem to figure out how to accomplish this goal.

 

Currently I have two tables: history, and category

The history table has fields: id, title, category, sub_category, and info.

The category table merely has fields: category_id, category_name

 

I am able to display a single category for the articles without a problem, but I have no idea how I should go about implementing a second category.  Do I add another table which relates the article id field to the category_id field?  Is there an ELSE or IF statement I can use to adopt my sub_category field to read the same value that is in the category_id field using another Join statement in the query?

 

I'm stumped and would be grateful for any guidance.

 

Here's the code for the article page (the $sub field is a lame attempt at using the $sub_category field; I doubt the IF will work at all in what I'm trying to do):

 

<?php
mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$query = "SELECT l.*, r.* from history l Inner Join category r ON l.category = r.category_id 
ORDER BY l.id desc LIMIT 10";
$result = mysql_query($query) or die(mysql_error());

while($row=mysql_fetch_array($result))
{
extract($row);

$sub = '';

if (strlen($sub_category) >= 1) { 
$sub = ", <a href=\"categories.php?category_id=$sub_category\">$sub_category_name</A>"; 
}

echo '<p><blockquote><div class="title">'.$title.'</div>
'.$info.'
<div class="cat">Categories: <a href="categories.php?category_id='.$category_id.'">'.$category_name.'</A>'.$sub.'</div>
</blockquote>';

}

?>

[EternalSorrow]

This is both a bump and an upgrade with the code.  I've modified the code with assistance from this discussion, but I've found I still have some problems.

 

I now have three tables to work with:

The history table has fields: entry_id, title, category, sub_category, and info

The category table merely has fields: category_id, category_name

And the catjoin table: entry_id, category_id

 

The problems that arise are two: the articles are duplicated according to how many categories they have, and I obviously want only a single article shown.  The LIMIT command doesn't work and I'm unsure how to apply the code from the discussion above for my own PHP.

 

Second, I wish to show all categories assigned to a single article at the bottom of that article.  To do this I would need to create an IF statement which would review the results of the query, determine whether an article had greater than one category assigned (such as category > 1 or category >= 2), and output the second category using the IF statement.  I have no idea what command, other than a count command, could do this and I'm not sure how to set up such a code (multiple attempts have failed).

 

Here's the modified code (ignore the one on the first posting):

 

<?php
mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$query = "SELECT a.*, b.* FROM (SELECT * FROM history ORDER BY entry_id DESC) a
            INNER JOIN catjoin c ON a.entry_id = c.entry_id
            INNER JOIN category b ON b.category_id = c.category_id";
$result = mysql_query($query) or die(mysql_error());

while($row=mysql_fetch_array($result))
{
extract($row);

echo '<p><blockquote><div class="title">'.$title.'</div>
'.$info.'
<div class="cat">Categories: <a href="categories.php?category_id='.$category_id.'">'.$category_name.'</A>'.$sub.'</div>
</blockquote>';

}
?>

[Mchl]

Do I add another table which relates the article id field to the category_id field?

 

Yes you do.

 

[edit]

 

Oh... you know already

[EternalSorrow]

Bump.  Does anyone even know a place I can find a tutorial for this?

[EternalSorrow]

I have this horrible feeling this post won't be solved...and another bump.

[Mchl]

1. Try:

SELECT DISTINCT a.* ...

 

2. I would just take article's ID and list all categories assigned to this ID.

[EternalSorrow]

1. Try:

SELECT DISTINCT a.* ...

 

2. I would just take article's ID and list all categories assigned to this ID.

 

1. I'm afraid it doesn't, regardless of where I put the DISTINCT command.

 

2. This is what I'm asking how to perform.  I have single to two categories per article, so an IF statement which counted the number of categories assigned to that entry_id would be ideal.  Any idea where to learn such a statement?

[EternalSorrow]

Bump.

[EternalSorrow]

Bump....any tutorial ideas?

[Mchl]

Can't recall any tutorials right now. You might want to check some links in stickies in MySQL section of this forum.

 

For point #2 you would basically need one more query. I.e. first query would pull the article data from DB (including article's ID). Then you pass this ID to second query, and pull all categgories assigned to this ID.

[EternalSorrow]

For point #2 you would basically need one more query. I.e. first query would pull the article data from DB (including article's ID). Then you pass this ID to second query, and pull all categgories assigned to this ID.

 

That's where I've become stuck.  I have no idea what code to create in the second query which would allow me to pull all categories assigned to the entry_id and then display it.  Even after I've figured out the second query, I have no idea what sort of IF command would retrieve the second category of the entry, if such a field were filled (some entries have one category and others have two).

 

I changed the code slightly to: limit the entries to one using the GROUP command, try a simple second query without success, and try a simple IF command which brings only an error message:

<?php
mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$query = "SELECT a.*, b.* FROM history a 
            INNER JOIN catjoin c ON a.entry_id = c.entry_id 
            INNER JOIN category b ON b.category_id = c.category_id
            GROUP BY a.entry_id
            ORDER BY a.entry_id desc LIMIT 5 ";
$result = mysql_query($query) or die(mysql_error());

while($row=mysql_fetch_array($result))
{
extract($row);

$select_category = mysql_query("SELECT * FROM history WHERE category ='$category' ") or die (mysql_error());

$cat = '';

if (row[$category] >= 2) { 
$cat = ", <a href="categories.php?category='.$category.'">'.$category_name.'</A>"; 
}

echo '<p><blockquote><div class="title">'.$title.'</div>
'.$info.'
<div class="cat">Categories: <a href="categories.php?category='.$category.'">'.$category_name.'</A>'.$cat.'</div>
</blockquote>';

}
?>

[Mchl]

SELECT * FROM category AS b INNER JOIN catjoin AS c ON b.category_id = c.category_id WHERE c.entry_id = ?

 

Now you just need to put ID of article instead of question mark.

 

[EternalSorrow]

Now the final leg of this horrendous coding project.  All articles appear once and their categories have been assigned to them, but I am having trouble with the IF statement to show the second category.

 

First, here's the code for the IF statement:

$cat = '';

if (mysql_num_rows($select_category) >= 2) { 
$cat = ", <a href=\"categories.php?category=$category\">$category_name</A>"; 
}

 

Since all articles have at least one category, I've opted to leave that out of the IF statement for simplicity.  The problem here, however, is I don't know how to tell the IF statement to retrieve only the second category row (if applicable).  Currently it only retrieves the first category row and repeats that twice (once for the main echo and another for the IF statement).

 

Any ideas on how to specify the second category row to appear?  Should I place all category options in the IF statement rather than the echo and go from there?

[Mchl]

It is not simplicity actually. It would  be easier to just display all categories as they go...

 

You will need a while query for that

See examples in manual for mysql_query. These are good ones.

[EternalSorrow]

See examples in manual for mysql_query. These are good ones.

 

You just exploded my brain.

 

I looked through the entire page, reading every post, and my noobie-ness could barely grasp the concepts they were attempting, much less how they attempted them.  I did find more relevant posts at the while page, but even they barely broached upon the simple command I'm trying to attempt.

 

Now I'm not even sure if I should replace the current while statement I have into one more complex, or create a while statement within an IF statement, or even do a while statement imbeded into another while statement.

 

And I still have no clue how to access the categories assigned to the entry, separate them to create unique fields, and then print them out within the current echo statement.

 

 

[Mchl]

Oh come on. It is not that complicated.

 

It works like this

 

1. You run a query to select categories assigned to the article

2. Query will return a result (hopefully) consisting of one or more rows

3. You use while loop to get all rows from result.

 

So it would look something like this:

 

$select_category = mysql_query("SELECT * FROM category AS b INNER JOIN catjoin AS c ON b.category_id = c.category_id WHERE c.entry_id = $entry_id") or die (mysql_error());
//$select_category is now a result of query, so we will get rows from it

while ($row = mysql_fetch_array($select_category)) {
  $cat .= "<a href=\"categories.php?category={$row['category']}\">$row['category_name']</a>, ";
}
$cat = substr($cat,0,-2); //this will remove last comma and space from $cat

[EternalSorrow]

Oh come on. It is not that complicated.

 

Sorry, drama moment T.T

 

So I studied your code (bless you for the comments in them!) and found I had to change the ['category'] to [category] or I'd receive the "unexpected t encapsed and whitespace" error.

 

There are appears to be a problem with repetition.  With each descending article the previous article's category(s) are added, so by the last article it holds all categories.

[Mchl]

Sorry...  it should be {$row['category']}  (in {} braces)

 

Please post your entire current code (related to this topic of course ), because we might be missing something when working on parts

[EternalSorrow]

Here's the code so far:

<?php
mysql_connect(localhost,user,pw);
@mysql_select_db(db) or die( "Unable to select database");

$query = "SELECT a.*, b.* FROM history a 
            INNER JOIN catjoin c ON a.entry_id = c.entry_id 
            INNER JOIN category b ON b.category_id = c.category_id
            GROUP BY a.entry_id
            ORDER BY a.entry_id desc LIMIT 5 ";
$result = mysql_query($query) or die(mysql_error());

while($row=mysql_fetch_array($result))
{
extract($row);

$select_category = mysql_query("SELECT * FROM category AS b INNER JOIN catjoin AS c ON b.category_id = c.category_id WHERE c.entry_id = $entry_id") or die (mysql_error());
//$select_category is now a result of query, so we will get rows from it

while ($row = mysql_fetch_array($select_category)) {
   $cat .= "<a href=\"categories.php?category={$row[category_name]}\">$row[category_name]</a>, ";
}

$cat = substr($cat,0,-2); //this will remove last comma and space from $cat

echo '<p><blockquote><div class="title">'.$title.'</div>
'.$info.'
<div class="cat">Categories: '.$cat.'</div>
</blockquote>';

}
?>

[Mchl]

Ok... you need to clear $cat before while loop

 

$cat = "";
while($row=mysql_fetch_array($result))
{
...
}

 

 

Also, you should not use $row for both queries. Use $row1, $row2 instead, or just name one different from another. If you don't you might run into hard to diagnose problems later.

[EternalSorrow]

I can't thank you enough for helping me through this coding.  It works perfectly and I've taken your advice about the $row duplicates.  I've learn a lot from this, er, "exercise" and am definitely the wiser when using the PHP manual.

[Mchl]

Do not be afraid of manual. It's your friend

 

Cheers