devofash Posted June 12, 2006 Share Posted June 12, 2006 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_idtitle category_name category_idauthorcontentstimeits got the following dataARTICLE_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] Quote Link to comment Share on other sites More sharing options...
Barand Posted June 12, 2006 Share Posted June 12, 2006 Give us a clue and tell us how you would like the output to be presented. Quote Link to comment Share on other sites More sharing options...
devofash Posted June 12, 2006 Author Share Posted June 12, 2006 [!--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...titletime/datecontentstags : web, css, php ( tht's wht i mean by multiple tags/category )--------titletime/datecontentstags: 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 ?? Quote Link to comment Share on other sites More sharing options...
Barand Posted June 12, 2006 Share Posted June 12, 2006 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] Quote Link to comment Share on other sites More sharing options...
homchz Posted June 12, 2006 Share Posted June 12, 2006 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 postingThen when I want to get the category list I used this function[code]function get_categories($tid) { print "<div class='blog_cat'>"; print "Categories: "; $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&archive=category&cid=$id'>$cat_name</a> "; } } }[/code] Quote Link to comment Share on other sites More sharing options...
devofash Posted June 13, 2006 Author Share Posted June 13, 2006 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] Quote Link to comment Share on other sites More sharing options...
Barand Posted June 13, 2006 Share Posted June 13, 2006 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. Quote Link to comment Share on other sites More sharing options...
devofash Posted June 19, 2006 Author Share Posted June 19, 2006 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 ? Quote Link to comment Share on other sites More sharing options...
Barand Posted June 24, 2006 Share Posted June 24, 2006 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] Quote Link to comment Share on other sites More sharing options...
devofash Posted November 8, 2006 Author Share Posted November 8, 2006 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 ?? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.