Jump to content

assign multiple categories to one post


devofash

Recommended Posts

hello ppl .... am trying to create a blog ( nothing too advanced ) just trying to improve my php knowledge ... anyways i'm stuck ... i'm trying to assign multiple categories to a post .... meaning an article which can be posted in multiple categories ... meaning 1-to-many relationship ....

my table structure is as follows

[code]

ARTICLE       CATEGORY             ARTICLE_CATEGORY                
----------    ----------------     ------------------------
article_id    category_id          article_id
title         category_name        category_id
author
contents
time

its got the following data

ARTICLE_CATEGORY               CATEGORY            
---------------------------    -----------------------------
article_id      category_id    category_id     category_name
    1              2                1              Design
    2              1                2              Personal
    2              2
[/code]

it makes sense so far .... i dont think the problem is with my database ... its most probably some silly mistake with my php code .... anyways the problem .... when i try to display the articles in this case "article 2" ... its displayed twice ... if i add another category to article 2 .... then "article 2" is displayed three times .... i want it to display all the articles and each one is assigned to multiple tags/categories .. the php code is below .... hope you guys can help me out .... thanx

[code]
  $query = "SELECT *
            FROM article_category ac, articles a, category c
            WHERE a.article_id = ac.article_id
            AND c.category_id = ac.category_id
            ORDER BY time DESC";

  $result = mysql_query( $query ) or die ( mysql_error());

  while( $row = mysql_fetch_array ( $result ))
  {
    $art_id = $row['article_id'];
    $title = $row['title'];
    $author = $row['author'];
    $contents = $row['contents'];
    $time = $row['time'];
    $cat_id = $row['category_id'];
    $cat_name = $row['category_name'];

   // some extra stuff

    echo "</p><div id='bottom'>Filed under : <a href = \"$PHP_SELF?cat=$cat_id\"> $cat_name </a></div>";
    echo '</div></div><br>';
  }
?>
[/code]
Link to comment
Share on other sites

[!--quoteo(post=382986:date=Jun 12 2006, 09:26 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 12 2006, 09:26 PM) [snapback]382986[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Give us a clue and tell us how you would like the output to be presented.
[/quote]

ooo just like all the other blog posts .... eg...

title
time/date
contents
tags : web, css, php ( tht's wht i mean by multiple tags/category )

--------

title
time/date
contents
tags: design, personal

-----------

etc etc...

rite now it displays blog posts .... but if i have 3 tags assigned to article 1 ... it will display article 1 ..three times... *confused* .... am i making sense ??
Link to comment
Share on other sites

Check for change of article. Only output article details when it changes
[code]
$query = "SELECT a.article_id, a.title, a.author, a contents,
            a.time, c.category_id, c.category_name
            FROM articles a
            INNER JOIN article_category ac ON a.article_id = ac.article_id
            INNER JOIN category c ON c.category_id = ac.category_id
            ORDER BY time DESC";

$result = mysql_query( $query ) or die ( mysql_error());

$prev_art = 0;  
while( list($art_id, $title, $author, $contents, $time, $cat_id, $cat_name) = mysql_fetch_row ( $result ))
{
    if ($prev_art != $art_id)  {
        echo "<hr>$title<br>
              $author<br>
              $contents<br>".
              date ('H:i d M Y', strtotime($time)) . "<br>
              tags : ";
    }
    echo $cat_name . ' ';
       $prev_art = $art_id;
}
[/code]
Link to comment
Share on other sites

I did it a little differently with my blog.

I placed all the category id's in one field, buy using check box form fields while posting

Then when I want to get the category list I used this function

[code]
function get_categories($tid)
        {
            print "<div class='blog_cat'>";
            print "Categories:&nbsp; ";
            $this->table = "blog_posts";
            $this->query = mysql_query("SELECT categories from $this->table WHERE topic_id = $tid");
            
            while ($this->result = mysql_fetch_assoc($this->query))
            {
                global $cat;
                $cat = $this->result['categories'];
            }
            
            $c_id = explode(",", $cat);

            $number = count($c_id);
            
            foreach($c_id as $cid)
            {
                
                $table = "blog_category";
                $query = mysql_query("SELECT cid, category FROM $table WHERE cid = $cid");
                
                while($result = mysql_fetch_array($query))
                {
                    $c_name = array($result['category'] => $result['cid']);
                    
                    foreach($c_name as $cat_name => $id)
                    {
                        print "<a href='http://localhost/JoshuaJones/blog/index.php?page=archive&amp;archive=category&amp;cid=$id'>$cat_name</a>&nbsp;&nbsp;";
                    
                    }
                    
                }
            }
[/code]
Link to comment
Share on other sites

thanx homchz appreciate the code ... i'll try to implement it somewhere :) .....

barand thanx for the code too it works like a charm :D ...one question i know about inner join its cool ... although could you explain bits where you have

[code]$prev_art = 0;  and  if ($prev_art != $art_id)  and $prev_art = $art_id; [/code]
Link to comment
Share on other sites

As I said at the top of the post, look for a change of article id

[code]if ($prev_art != $art_id)[/code]

If they aren't equal then it's a new article so output details.

You now have to set the previous to the current value so you can detect the next change

[code]$prev_art = $art_id[/code]

Setting $prev_art = 0 at the begining ensures it treats the first record as a new article.

Hope that helps.
Link to comment
Share on other sites

thanx barand .... errr got another question ... well problem actually ... now i want to limit the amount of articles/posts displayed i.e. i dont want it to display all the posts and i thought doing LIMIT would do this but it doesnt.

rite now say for example i have a post with 3 tags (personal, design, web) and i do

[code]
$query = "SELECT a.article_id, a.title, a.author, a.contents,
            a.time, c.category_id, c.category_name
            FROM articles a
            INNER JOIN article_category ac ON a.article_id = ac.article_id
            INNER JOIN category c ON c.category_id = ac.category_id
            ORDER BY time DESC LIMIT 1";
[/code]

well it shows 1 article and only 1 tag ( it removes design and web ) .... how do i solve this ...??

btw's am i making sense ?
Link to comment
Share on other sites

Your joined query has a row for each item/category so there are 3 rows for the first item. Limiting to 1 loses the other 2.

You need to limit the number of articles. I can't test this yet as I haven't got round to installing 4.1 yet but give it a go
[code]
$query = "SELECT a.article_id, a.title, a.author, a.contents,
            a.time, c.category_id, c.category_name
            FROM (SELECT * FROM articles ORDER BY time DESC LIMIT 10) a
            INNER JOIN article_category ac ON a.article_id = ac.article_id
            INNER JOIN category c ON c.category_id = ac.category_id";
[/code]
Link to comment
Share on other sites

  • 4 months later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.