Jump to content


Photo

assign multiple categories to one post


  • Please log in to reply
9 replies to this topic

#1 devofash

devofash
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 12 June 2006 - 07:44 PM

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


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

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

  $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>';
  }
?>


#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 12 June 2006 - 08:26 PM

Give us a clue and tell us how you would like the output to be presented.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 devofash

devofash
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 12 June 2006 - 08:56 PM

[!--quoteo(post=382986:date=Jun 12 2006, 09:26 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 12 2006, 09:26 PM) View Post[/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 ??

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 12 June 2006 - 10:46 PM

Check for change of article. Only output article details when it changes
$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;
}

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 homchz

homchz
  • Members
  • PipPipPip
  • Advanced Member
  • 93 posts
  • LocationLee, New Hampshire

Posted 12 June 2006 - 10:55 PM

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

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;";
                    
                    } 
                    
                }
            }


#6 devofash

devofash
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 13 June 2006 - 10:27 AM

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

$prev_art = 0;  and  if ($prev_art != $art_id)  and $prev_art = $art_id;


#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 13 June 2006 - 01:33 PM

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

if ($prev_art != $art_id)

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

$prev_art = $art_id

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

Hope that helps.

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 devofash

devofash
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 19 June 2006 - 12:26 PM

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

$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";

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 ?

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 24 June 2006 - 10:58 AM

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
$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";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#10 devofash

devofash
  • Members
  • PipPipPip
  • Advanced Member
  • 81 posts

Posted 08 November 2006 - 07:17 PM

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 ??




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users