HELP! Please. with forum

Discussion in 'Web Design and Development' started by Cabbit, Jul 14, 2007.

  1. Cabbit macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #1
    Ok this is the start of my sites forum system. But it only prints out one record and i cant get the two counters to work i cant it to display the post count and the reply count. :confused:
    ____Database_________
    forum-catagory
    id
    catagory

    forum-topics
    id
    catagory_id
    title
    about

    forum-posts
    id
    topic_id
    username
    date
    subject
    post

    forum-reply
    id
    posts_id
    username
    date
    subject
    post
    _____________________

    ................................
    Forum..............................Topics.......Posts
    About
    ..........Topic 1.....................1...............4...
    decript
    ..........Topic 2.....................1...............4...
    decript
    ..........Topic 3.....................1...............4...
    decript
    This and that
    ..........Topic 1.....................1...............4...
    decript
    ..........Topic 2.....................1...............4...
    decript
    ..........Topic 3.....................1...............4...
    decript
    ................................


    PHP:
    <?
    include(
    "include/session.php");
    $sql "SELECT s.`id`, s.`catagory`, c.`title`, c.`about` FROM `forum-catagory` AS `s` LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` GROUP BY s.`id`";
    $result mysql_query($sql) or die("Query failed($sql): " mysql_error()); 
       print 
    '<table width="100%" border="1" cellpadding="4" cellspacing="1" >';
    print 
    '<tr><td width="70%">Forum</td><td width="15%">Topics</td><td width="15%">Posts</td></tr>'
    WHILE(
    $row mysql_fetch_array($result)) { 
          
    printf("<tr><tr><td><font size=\"+2\" color=\"#ee103a\">%s" "</font><td><td></td></tr>"$row["catagory"]); 
    WHILE(
    $row mysql_fetch_array($result)) {
        
    printf("<tr><tr><td>%s" "</td>"$row["title"]);
        print(
    "<td>1</td><td>1</td></tr>");
        
    printf("<tr><tr><td>%s" "</td><td></td><td></td></tr></tr>"$row["about"]);  
    print 
    '</tr>';
    }
    }
    print 
    '</table>'
    ?>
     
  2. angelneo macrumors 68000

    Joined:
    Jun 13, 2004
    Location:
    afk
    #2
    I'm not sure what are you trying to achieve, but you have 2 while loops going over $row = mysql_fetch_array($result) at the same time, did you cut and paste the code from somewhere else?
     
  3. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #3
    i dont understand loops to well you see. the code is my own i've used it before just not with what im trying to do here.
     
  4. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #4
    with the two while loops i get one catagory with all its topics, without the while loop i get all the catagorys but only one topic per catagory.
     
  5. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #5
    PHP:
    <?
    include(
    "include/session.php");
    $sql "SELECT s.`id`, s.`catagory`, c.`title`, c.`about` FROM `forum-catagory` AS `s` LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` GROUP BY s.`id`";
    $result mysql_query($sql) or die("Query failed($sql): " mysql_error()); 
       print 
    '<table width="100%" border="1" cellpadding="4" cellspacing="1" >';
    print 
    '<tr><td width="70%">Forum</td><td width="15%">Topics</td><td width="15%">Posts</td></tr>'
    WHILE(
    $row mysql_fetch_array($result)) { 
          
    printf("<tr><tr><td><font size=\"+2\" color=\"#ee103a\">%s" "</font><td><td></td></tr>"$row["catagory"]);
    $arr=array("one""two""three");
    foreach (
    $arr as $value)
    {
        
    printf("<tr><tr><td>%s" "</td>"$row["title"]);
        print(
    "<td>1</td><td>1</td></tr>");
        
    printf("<tr><tr><td>%s" "</td><td></td><td></td></tr></tr>"$row["about"]); }  
    print 
    '</tr>';
    }
    print 
    '</table>'
    ?>
    okies i done a little extra coding, so it now prints out all the catagorys and but the same title 3 times over. i suck at loops.

    more failed attempts.

    PHP:
    <?
    include(
    "include/session.php");
    $sql "SELECT s.`id`, s.`catagory`, c.`title`, c.`about` FROM `forum-catagory` AS `s` LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` GROUP BY s.`id`";
    $result mysql_query($sql) or die("Query failed($sql): " mysql_error()); 
       print 
    '<table width="100%" border="1" cellpadding="4" cellspacing="1" >';
    print 
    '<tr><td width="70%">Forum</td><td width="15%">Topics</td><td width="15%">Posts</td></tr>'
    WHILE(
    $row mysql_fetch_array($result)) { 
          
    printf("<tr><tr><td><font size=\"+2\" color=\"#ee103a\">%s" "</font><td><td></td></tr>"$row["catagory"]);
    $arrmysql_fetch_array($result);
    foreach (
    $arr as $value)
    {
        
    printf("<tr><tr><td>%s" "</td>"$row["title"]);
        print(
    "<td>1</td><td>1</td></tr>");
        
    printf("<tr><tr><td>%s" "</td><td></td><td></td></tr></tr>"$row["about"]); }  
    print 
    '</tr>';
    }
    print 
    '</table>'
    ?>
     
  6. angelneo macrumors 68000

    Joined:
    Jun 13, 2004
    Location:
    afk
    #6
    First off, everytime when you do a "mysql_fetch_array($result)", you are actually incrementing your resultset from your query. Looking at your code, you are definitely missing some records from your printout.

    I think your sql query might have some error, correct if I am wrong, you are trying to get a list of all forum topics with their respectively categories right? And you want your table layout to group according to category?

    You shouldn't use "group by" in your sql query, just do a "order by". Your resultset will have a list of the forum topics listed alphabetically (by category id).

    A single while loop is sufficient, while you are looping, you just need to detect when your category_id changes and applies the correct header accordingly.
     
  7. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #7
    Ok getting there, however it print out the Catagory every time.

    ____________
    Comunity
    topic 1
    Comunity
    topic 2
    __________

    instead of

    ________
    Comunity
    topic 1
    topic 2

    ______



    PHP:
    <?
    include(
    "include/session.php");
    $sql "SELECT s.`id`, s.`catagory`, c.`title`, c.`about` FROM `forum-catagory` AS `s` LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` ORDER BY s.`id`";
    $result mysql_query($sql) or die("Query failed($sql): " mysql_error()); 
       print 
    '<table width="100%" border="1" cellpadding="4" cellspacing="1" >';
    print 
    '<tr><td width="70%">Forum</td><td width="15%">Topics</td><td width="15%">Posts</td></tr>'
    WHILE(
    $row mysql_fetch_array($result)) { 
    printf("<tr><tr><td><font size=\"+2\" color=\"#ee103a\">%s" "</font><td><td></td></tr>"$row["catagory"]);      
        
    printf("<tr><tr><td>%s" "</td>"$row["title"]);
        print(
    "<td>1</td><td>1</td></tr>");
        
    printf("<tr><tr><td>%s" "</td><td></td><td></td></tr></tr>"$row["about"]); 
    }  
    print 
    '</tr>';
    print 
    '</table>'
    ?>
     
  8. angelneo macrumors 68000

    Joined:
    Jun 13, 2004
    Location:
    afk
    #8
    You might want to take a look at how you construct your html table

    PHP:
    <?
    include(
    "include/session.php");
    $sql "SELECT s.`id`, s.`catagory`, c.`title`, c.`about` FROM `forum-catagory` AS `s` LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` ORDER BY s.`id`";
    $result mysql_query($sql) or die("Query failed($sql): " mysql_error()); 
       print 
    '<table width="100%" border="1" cellpadding="4" cellspacing="1" >';
    print 
    '<tr><td width="70%">Forum</td><td width="15%">Topics</td><td width="15%">Posts</td></tr>'
    WHILE(
    $row mysql_fetch_array($result)) { 
    printf("<tr><tr><td><font size=\"+2\" color=\"#ee103a\">%s" "</font><td><td></td></tr>"$row["catagory"]);      
        
    printf("<tr><tr><td>%s" "</td>"$row["title"]);
        print(
    "<td>1</td><td>1</td></tr>");
        
    printf("<tr><tr><td>%s" "</td><td></td><td></td></tr></tr>"$row["about"]); 
    }  
    print 
    '</tr>';
    print 
    '</table>'
    ?> 
    try this

    PHP:
    <?
    include(
    "include/session.php");
    $sql "SELECT s.`id`, s.`catagory`, c.`title`, c.`about` FROM `forum-catagory` AS `s` LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` ORDER BY s.`id`";
    $result mysql_query($sql) or die("Query failed($sql): " mysql_error()); 
       print 
    '<table width="100%" border="1" cellpadding="4" cellspacing="1" >';
    print 
    '<tr><td width="70%">Forum</td><td width="15%">Topics</td><td width="15%">Description</td></tr>'
    WHILE(
    $row mysql_fetch_array($result)) { 
        print(
    "<tr>")
        print(
    "<td><font size=\"+2\" color=\"#ee103a\">" .$row["title"]."</font></td>");
        print(
    "<td>".$row["catagory"]."</td>");
        print(
    "<td>".$row["about"]."</td>");
        print 
    '</tr>';
    }  
    print 
    '</table>'
    ?> 
    I have formatted it to show you more clearly, but I can see that you wanted forum, topics follow by the number of posts in there, which means you need to include the posts table in your sql query

    PHP:
    <?
    include(
    "include/session.php");
    $sql "SELECT s.`id`, s.`catagory`, c.`title`, c.`about`, SUM(p.`topic_id`) AS total_post FROM `forum-catagory` AS `s`, forum-posts AS 'p' LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` WHERE p.`topic_id`=s.`id` GROUP BY p.`topic_id`";
    $result mysql_query($sql) or die("Query failed($sql): " mysql_error()); 
       print 
    '<table width="100%" border="1" cellpadding="4" cellspacing="1" >';
    print 
    '<tr><td width="70%">Forum</td><td width="15%">Topics</td><td width="15%">Posts</td></tr>'
    WHILE(
    $row mysql_fetch_array($result)) { 
        print(
    "<tr>")
        print(
    "<td><font size=\"+2\" color=\"#ee103a\">" .$row["title"]."</font></td>");
        print(
    "<td>".$row["catagory"]."</td>");
        print(
    "<td>".$row["total_post"]."</td>");
        print 
    '</tr>';
    }  
    print 
    '</table>'
    ?> 
    This query includes the post table and try to do a summation of the posts in each topic_id
     
  9. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #9
    using your last code block.

    Thanks but im getting a failed quiry.

    Code:
    Query failed(SELECT s.`id`, s.`catagory`, c.`title`, c.`about`, SUM(p.`topic_id`) AS total_post FROM `forum-catagory` AS `s`, `forum-posts` AS 'p' LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` WHERE p.`topic_id` = s.`id` GROUP BY s.`catagory`): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''p' LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` WHERE p.`topic_i' at line 1
     
  10. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #10
    http://www.dev.cossycomforts.com/forum.php looks like this with the following code.

    I only what the catagorys(in red) printed once each.

    PHP:
    <?
    include(
    "include/session.php");
    $sql "SELECT s.`id`, s.`catagory`, c.`title`, c.`about` FROM `forum-catagory` AS `s` LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` ORDER BY s.`id`";
    $result mysql_query($sql) or die("Query failed($sql): " mysql_error()); 
       print 
    '<table width="100%" border="0" cellpadding="4" cellspacing="1" >';
    print 
    '<tr><td width="70%">Forum</td><td width="15%">Topics</td><td width="15%">Posts</td></tr>'
    WHILE(
    $row mysql_fetch_array($result)) {
        
    printf("<tr><td><font size=\"+2\" color=\"#ee103a\">%s" "</font><td><td></td></tr>"$row["catagory"]);
        
    printf("<tr><tr><td>%s" "</td>"$row["title"]);
        print(
    "<td>1</td><td>1</td></tr>");
        
    printf("<tr><tr><td>%s" "</td><td></td><td></td></tr></tr>"$row["about"]); 
    }
    print 
    '</tr>';
    print 
    '</table>'
    ?>
     
  11. angelneo macrumors 68000

    Joined:
    Jun 13, 2004
    Location:
    afk
    #11
    I did not really test my query, but the idea is that you do a summation of the number of post while grouping them via their topic id.
     
  12. Cabbit thread starter macrumors 68020

    Cabbit

    Joined:
    Jan 30, 2006
    Location:
    Scotland
    #12
    fixed it now.

    PHP:
    <?php
    $sql 
    "SELECT s.`id`, s.`catagory`, c.`title`, c.`about` FROM `forum-catagory` AS `s` LEFT JOIN `forum-topics` AS `c` ON s.`id` = c.`catagory_id` ORDER BY s.`id`";
    $result mysql_query($sql) or die("Query failed($sql): " mysql_error()); 
       print 
    '<table width="100%" border="0" cellpadding="4" cellspacing="1" >';
    print 
    '<tr><td width="70%">Forum</td><td width="15%">Topics</td><td width="15%">Posts</td></tr>'
    $last_cat ''
    WHILE(
    $row mysql_fetch_array($result)) { 
        if(
    $row['catagory'] != $last_cat) { 
            
    $last_cat $row['catagory']; 
            
    printf("<tr><td><font size=\"+2\" color=\"#ee103a\">%s" "</font><td><td></td></tr>"$row["catagory"]); 
        } 
        
    printf("<tr><tr><td>%s" "</td>"$row["title"]);
        print(
    "<td>1</td><td>1</td></tr>");
        
    printf("<tr><tr><td>%s" "</td><td></td><td></td></tr></tr>"$row["about"]); 
    }
    print 
    '</tr>';
    print 
    '</table>'
    ?>
     

Share This Page