HELP! Please. with forum

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,129
1
Scotland
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>'; 
?>
 

angelneo

macrumors 68000
Jun 13, 2004
1,541
0
afk
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?
 

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,129
1
Scotland
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.
 

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,129
1
Scotland
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.
 

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,129
1
Scotland
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"]);
$arr= mysql_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>'; 
?>
 

angelneo

macrumors 68000
Jun 13, 2004
1,541
0
afk
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.
 

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,129
1
Scotland
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>'; 
?>
 

angelneo

macrumors 68000
Jun 13, 2004
1,541
0
afk
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
 

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,129
1
Scotland
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
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
 

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,129
1
Scotland
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>'; 
?>
 

angelneo

macrumors 68000
Jun 13, 2004
1,541
0
afk
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
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.
 

Cabbit

macrumors 68020
Original poster
Jan 30, 2006
2,129
1
Scotland
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>'; 
?>