Jump to content

Archived

This topic is now archived and is closed to further replies.

devofash

assign multiple categories to one post

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]

Share this post


Link to post
Share on other sites
Give us a clue and tell us how you would like the output to be presented.

Share this post


Link to post
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 ??

Share this post


Link to post
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]

Share this post


Link to post
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]

Share this post


Link to post
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]

Share this post


Link to post
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.

Share this post


Link to post
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 ?

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
it still doesnt work properly :(

prints out the entries fine but the same entry is printed 2-3 times. e.g. first entry has 2 tags so it prints out the entry 2 times etc...

any ideas ??

Share this post


Link to post
Share on other sites

×

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.