Sorting into categories

[Stewartiee]

Okay i'm making a forum software. This is my first project in php and mysql, so im still new to learning it. Everything so far, I have done myself.

Except i'm stuck on a little problem. So far I have a category table and a forum table. In both tables I have catid. So if the category id is 1, then I want all forums with the same catid to file under it.

Sorry if this makes no sense at all :) I included the link to my forum below. As you see the forums are not sorting right now. For example Announcements is catid=1 but it's not filing under category 1.

http://92.237.212.5/index.php

[MattF]

Order by cat id and check whether the current catid for the category matches the catid of the forum. If not, close that table and create a new one for the new category and subsequent forums.

For example:


if ($cur_forum['catid'] != $cur_category['catid'])
{
[close the previous category table here and start a new category table]
}

[Stewartiee]

How would I go around implenting this? I know my index.php page is probably messy coded, but i'll learn to clean it up. I just want to sort everything out first. Below i've included my index.php

<?php
//
// Scadoo! - Beta V0.0.1
// index.php
//

include'includes/dbConfig.php';
include'themes/default/overall_header.php';

?>
<table border="0" cellpadding="2" cellspacing="0" style="border-collapse: collapse;" class="table">
<?php
while($rows=mysql_fetch_array($cfresult)){ // Start looping table row
?>
<tr>
<td class="fhead" colspan="4"><? echo $rows['catname']; ?> - <? echo $rows['catdesc']; ?></td>
</tr>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="65%"><center>Forum</center></td>
<td class="frow" width="15%"><center>Topics</center></td>
<td class="frow" width="15%"><center>Replies</center></td>
</tr>
<?php
}
?>
<?php
while($rows=mysql_fetch_array($result)){ // Start looping table row
?>
<tr>
<td class="frow3" colspan="1"><center><img border="0"

src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center></td>
<td class="frow2" colspan="1"><strong><a href="view_forum.php?fid=<? echo $rows['fid']; ?>"><? echo $rows['fname']; ?></a></strong><br /><?

echo $rows['fdesc']; ?></td>
<td class="frow3" colspan="1"><center><?php
$fid=$rows['fid'];
$query = "SELECT fid, COUNT(fid) FROM scadoo_topics WHERE fid='$fid'";

$topiccount = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($topiccount)){
echo "". $row['COUNT(fid)'] ."";
}
?></center></td>
<td class="frow2" colspan="1"><center><?php
$fid=$rows['fid'];
$query = "SELECT fid, COUNT(fid) FROM scadoo_replies WHERE fid='$fid'";

$topiccount = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($topiccount)){
echo "". $row['COUNT(fid)'] ."";
}
?></center></td>
</tr>
<?php
}
mysql_close();
?>
<tr>
<td class="sep" colspan="4"></td>
</tr>
</table>

<?php
include'themes/default/overall_footer.php';
?>

[MattF]

Simplest way to figure it out is to have a look at an existing setup. This file illustrates the method:

http://fluxbb.org/svn/fluxbb/trunk/upload/index.php

[Old Pedant]

close the previous category table here and start a new category table


I don't see any reason to "close" the table (I assume Matt means output a </table> and then start another <table>).

Looking at the layout of the HTML in your forum (which is illegal HTML, by the by, since it is missing *at least* a pair of </table> tags!), I think all you need to do is output a new pair of <tr> rows.

But your most desperate need is to come up with a better set of queries!!! As it is now, you have to make two separate SQL queries for each forum!!

No reason for that. If I've analyzed it correctly, you only need two separate SQL queries for the ENTIRE PAGE, no matter how many categories or forums there are.

What I'm missing is the first query you make, the one that gets the list of categories.

Oh...and what's the datatype of your fid field??? It *appears* that it only holds integers, yes? If that's so, why do you use '...' around values when you try to match it???

*IF* I've got it right, you have this DB structure:

Table: scadoo_categories [not sure what the table name is]
catid : int, autonumber, primary key
catname: varchar
catdesc: varchar

Table: scadoo_forums [again, not sure of table name]
fid : int, autonumber, primary key
catid : int, foreign key to categories
fname : varchar

Table: scadoo_topics
fid : int, foreign key to forums

Table: scadoo_replies
fid : int, foreign key to forums

Is that right? If so, then indeed you need only two queries for the entire *PAGE*.

[MattF]

I don't see any reason to "close" the table (I assume Matt means output a </table> and then start another <table>).


That is indeed what I meant. :) I assumed that was the layout he was after from the quick look I had at the link posted, but obviously, if not, whichever layout suits his needs best.

[MattF]

On an unrelated note, I'd suggest checking that the message content has some value when you post a message too. You can submit blank, (no content), posts as is.

[Old Pedant]

Remembering that I do *NOT* code in PHP, here's my stab at it.

Forgive any obvious PHP errors, please. And of course this assumes the DB layout I showed in my prior post.


<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_categories AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid "
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount "
. " FROM scadoo_categories AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid "
$main = mysql_query($mainquery) or die(mysql_error());
$reply = mysql_query($replyquery) or die(mysql_error());

$priorcat = -1;
while ( $mainrows = mysql_fetch_array($main) )
{
// now check if change of category:
$curcat = $mainrows["catid"];
if ( $priorcat != $curcat )
{
// yes...new category...put out category header
?>
<tr>
<td class="fhead" colspan="4">
<?=$mainrows['catname']?> - <?=$mainrows['catdesc']?>
</td>
</tr>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="65%"><center>Forum</center></td>
<td class="frow" width="15%"><center>Topics</center></td>
<td class="frow" width="15%"><center>Replies</center></td>
</tr>
<?php
// and remember new category
$priorcat = $curcat;
}
// now get the info for a single forum:
$fid = $mainrows["fid"];
$fname = $mainrows["fname"];
$fdesc = $mainrows["fdesc"];
$topiccount = $mainrows["topiccount"];
// also get a row from the replies query:
if ( ! $replyrows = mysql_fetch_array($reply) )
{
die("Mismatch on number of records in reply!");
}
$replycount = $replyrows["replycount"];
if ( $fid != $replyrows["fid"] || $curcat != $replyrows["catid"] )
{
die("Mismatch on fid or catid from replyrows!");
}
?>
<tr>
<td class="frow3" colspan="1">
<center><img border="0" src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center>
</td>
<td class="frow2" colspan="1"><strong>
<a href="view_forum.php?fid=<?= $fid?>"><?=$fname?></a></strong>
<br /><?=$fdesc?>
</td>
<td class="frow3" style="text-align: center;"><?=("" . $topiccount)?></td>
<td class="frow2" style="text-align: center;"><?=("" . $replycount)?></td>
</tr>
<?php
}
mysql_close();
?>
<tr>
<td class="sep" colspan="4"></td>
</tr>
</table>

[Old Pedant]

MattF wrote:
I assumed that was the layout he was after from the quick look I had at the link posted,
If you look again, esp. at the HTML he is producing, you'll see that his HTML is invalid. It's missing a pair of </table>s. BUT.. But you could more easily correct the HTML by simply getting rid of the extra <table> tags! And then the whole code is simpler, as I showed.

[Stewartiee]

Okay i tried your suggestion. Although I get a parse error...
<?php
//
// Scadoo! - Beta V0.0.1
// index.php
//

include'includes/dbConfig.php';
include'themes/default/overall_header.php';

?>
<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid "
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid "
$main = mysql_query($mainquery) or die(mysql_error());
$reply = mysql_query($replyquery) or die(mysql_error());

$priorcat = -1;
while ( $mainrows = mysql_fetch_array($main) )
{
// now check if change of category:
$curcat = $mainrows["catid"];
if ( $priorcat != $curcat )
{
// yes...new category...put out category header
?>
<tr>
<td class="fhead" colspan="4">
<?=$mainrows['catname']?> - <?=$mainrows['catdesc']?>
</td>
</tr>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="65%"><center>Forum</center></td>
<td class="frow" width="15%"><center>Topics</center></td>
<td class="frow" width="15%"><center>Replies</center></td>
</tr>
<?php
// and remember new category
$priorcat = $curcat;
}
// now get the info for a single forum:
$fid = $mainrows["fid"];
$fname = $mainrows["fname"];
$fdesc = $mainrows["fdesc"];
$topiccount = $mainrows["topiccount"];
// also get a row from the replies query:
if ( ! $replyrows = mysql_fetch_array($reply) )
{
die("Mismatch on number of records in reply!");
}
$replycount = $replyrows["replycount"];
if ( $fid != $replyrows["fid"] || $curcat != $replyrows["catid"] )
{
die("Mismatch on fid or catid from replyrows!");
}
?>
<tr>
<td class="frow3" colspan="1">
<center><img border="0" src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center>
</td>
<td class="frow2" colspan="1"><strong>
<a href="view_forum.php?fid=<?= $fid?>"><?=$fname?></a></strong>
<br /><?=$fdesc?>
</td>
<td class="frow3" style="text-align: center;"><?=("" . $topiccount)?></td>
<td class="frow2" style="text-align: center;"><?=("" . $replycount)?></td>
</tr>
<tr>
<td class="sep" colspan="4"></td>
</tr>
</table>

<?php
include'themes/default/overall_footer.php';
?>
http://92.237.212.5/test.php

[djm0219]

Both of the lines below are missing the end semi-colon.


$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid ";
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid ";

[MattF]

The two sql queries are missing their closing semicolons.

[Stewartiee]

I noticed and corrected these, although there's still an error somewhere...

[MattF]

If you've got this far with the coding, you should be familiar with debugging. :)

What's on line 84 to 86?

[Stewartiee]

This is my coding so far. I tried putting a space in. No result. There must be an error before it...

<?php
//
// Scadoo! - Beta V0.0.1
// index.php
//

include 'includes/dbConfig.php';
include 'themes/default/overall_header.php';

?>
<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid ";
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid ";
$main = mysql_query($mainquery) or die(mysql_error());
$reply = mysql_query($replyquery) or die(mysql_error());

$priorcat = -1;
while ( $mainrows = mysql_fetch_array($main) )
{
// now check if change of category:
$curcat = $mainrows["catid"];
if ( $priorcat != $curcat )
{
// yes...new category...put out category header
?>
<tr>
<td class="fhead" colspan="4">
<?=$mainrows['catname']?> - <?=$mainrows['catdesc']?>
</td>
</tr>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="65%"><center>Forum</center></td>
<td class="frow" width="15%"><center>Topics</center></td>
<td class="frow" width="15%"><center>Replies</center></td>
</tr>
<?php
// and remember new category
$priorcat = $curcat;
}
// now get the info for a single forum:
$fid = $mainrows["fid"];
$fname = $mainrows["fname"];
$fdesc = $mainrows["fdesc"];
$topiccount = $mainrows["topiccount"];
// also get a row from the replies query:
if ( ! $replyrows = mysql_fetch_array($reply) )
{
die("Mismatch on number of records in reply!");
}
$replycount = $replyrows["replycount"];
if ( $fid != $replyrows["fid"] || $curcat != $replyrows["catid"] )
{
die("Mismatch on fid or catid from replyrows!");
}
?>
<tr>
<td class="frow3" colspan="1">
<center><img border="0" src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center>
</td>
<td class="frow2" colspan="1"><strong>
<a href="view_forum.php?fid=<?= $fid?>"><?=$fname?></a></strong>
<br /><?=$fdesc?>
</td>
<td class="frow3" style="text-align: center;"><?=("" . $topiccount)?></td>
<td class="frow2" style="text-align: center;"><?=("" . $replycount)?></td>
</tr>
<tr>
<td class="sep" colspan="4"></td>
</tr>
</table>

<?php
include 'themes/default/overall_footer.php';
?>

[MattF]

Aye. Muppet moment on my part there. :D Try changing these: <?= to full <?php tags.

Edit: Plus add echo's where necessary.

[Stewartiee]

Nope, nothing.

[MattF]

Nothing after altering what?

[Stewartiee]

I changed the <?= to <?php and added echo for example//

<? echo $mainrows['catname']; ?>

[MattF]

I changed the <?= to <?php and added echo for example//

<? echo $mainrows['catname']; ?>

You do have short tags enabled?

[Stewartiee]

My current coding... :/
Not sure if i've done it correctly or not :(

<?php
//
// Scadoo! - Beta V0.0.1
// index.php
//

include 'includes/dbConfig.php';
include 'themes/default/overall_header.php';

?>
<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid ";
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid ";
$main = mysql_query($mainquery) or die(mysql_error());
$reply = mysql_query($replyquery) or die(mysql_error());

$priorcat = -1;
while ( $mainrows = mysql_fetch_array($main) )
{
// now check if change of category:
$curcat = $mainrows["catid"];
if ( $priorcat != $curcat )
{
// yes...new category...put out category header
?>
<tr>
<td class="fhead" colspan="4">
<?php echo $mainrows['catname']; ?> - <?php echo $mainrows['catdesc']; ?>
</td>
</tr>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="65%"><center>Forum</center></td>
<td class="frow" width="15%"><center>Topics</center></td>
<td class="frow" width="15%"><center>Replies</center></td>
</tr>
<?php
// and remember new category
$priorcat = $curcat;
}
// now get the info for a single forum:
$fid = $mainrows["fid"];
$fname = $mainrows["fname"];
$fdesc = $mainrows["fdesc"];
$topiccount = $mainrows["topiccount"];
// also get a row from the replies query:
if ( ! $replyrows = mysql_fetch_array($reply) )
{
die("Mismatch on number of records in reply!");
}
$replycount = $replyrows["replycount"];
if ( $fid != $replyrows["fid"] || $curcat != $replyrows["catid"] )
{
die("Mismatch on fid or catid from replyrows!");
}
?>
<tr>
<td class="frow3" colspan="1">
<center><img border="0" src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center>
</td>
<td class="frow2" colspan="1"><strong>
<a href="view_forum.php?fid=<?php echo $fid; ?>"><?php echo $fname; ?></a></strong>
<br /><?php echo $fdesc; ?>
</td>
<td class="frow3" style="text-align: center;"><?php echo ("" . $topiccount); ?></td>
<td class="frow2" style="text-align: center;"><?php echo ("" . $replycount); ?></td>
</tr>
<tr>
<td class="sep" colspan="4"></td>
</tr>
</table>

<?php
include 'themes/default/overall_footer.php';
?>

[MattF]

Try this.

<?php
//
// Scadoo! - Beta V0.0.1
// index.php
//

include 'includes/dbConfig.php';
include 'themes/default/overall_header.php';

?>
<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid ";
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid ";
$main = mysql_query($mainquery) or die(mysql_error());
$reply = mysql_query($replyquery) or die(mysql_error());

$priorcat = -1;
while ( $mainrows = mysql_fetch_array($main) )
{
// now check if change of category:
$curcat = $mainrows["catid"];
if ( $priorcat != $curcat )
{
// yes...new category...put out category header
?>
<tr>
<td class="fhead" colspan="4">
<?php echo $mainrows['catname'].' - '.$mainrows['catdesc']; ?>
</td>
</tr>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="65%"><center>Forum</center></td>
<td class="frow" width="15%"><center>Topics</center></td>
<td class="frow" width="15%"><center>Replies</center></td>
</tr>
<?php
// and remember new category
$priorcat = $curcat;
}
// now get the info for a single forum:
$fid = $mainrows["fid"];
$fname = $mainrows["fname"];
$fdesc = $mainrows["fdesc"];
$topiccount = $mainrows["topiccount"];
// also get a row from the replies query:
if ( ! $replyrows = mysql_fetch_array($reply) )
{
die("Mismatch on number of records in reply!");
}
$replycount = $replyrows["replycount"];
if ( $fid != $replyrows["fid"] || $curcat != $replyrows["catid"] )
{
die("Mismatch on fid or catid from replyrows!");
}
?>
<tr>
<td class="frow3" colspan="1">
<center><img border="0" src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center>
</td>
<td class="frow2" colspan="1"><strong>
<a href="view_forum.php?fid=<?php echo $fid.'">'.$fname; ?></a></strong>
<br /><?php echo $fdesc; ?>
</td>
<td class="frow3" style="text-align: center;"><?php echo $topiccount; ?></td>
<td class="frow2" style="text-align: center;"><?php echo $replycount; ?></td>
</tr>
<tr>
<td class="sep" colspan="4"></td>
</tr>
</table>

<?php
include 'themes/default/overall_footer.php';
?>

[Stewartiee]

Nope, still not working. :confused:

[MattF]

Put this line just after the opening php tag at the top of the file.


error_reporting(E_ALL);

[Stewartiee]

Okay I have. But it's still showing that same error like nothing has changed.

[MattF]

Comment these lines out.


if ( ! $replyrows = mysql_fetch_array($reply) )
{
die("Mismatch on number of records in reply!");
}
$replycount = $replyrows["replycount"];
if ( $fid != $replyrows["fid"] || $curcat != $replyrows["catid"] )
{
die("Mismatch on fid or catid from replyrows!");
}

[Stewartiee]

Okay, I have.

[MattF]

Okay, I have.

Just out of curiosity, (without having to scan through the thread again), that SQL code that Old Pedant posted is actually relevant to your table layout, is it not? You haven't just blindly used example code without adjusting the SQL queries to match your DB layout?

[Stewartiee]

Ofcourse I fixed it to my layout :P

[MattF]

Then I have no idea offhand. I can't see anything which seems out of place.

You do need to adjust that block of code that you just commented out, btw. Unless I've overlooked that part of the code, that was referencing a nonexistent query.

What are your logs saying, btw, regarding the errors?

[Stewartiee]

If you like you can have a look at the mysql tables for it?

[MattF]

You do need to adjust that block of code that you just commented out, btw. Unless I've overlooked that part of the code, that was referencing a nonexistent query.

Scrub that comment above and uncomment those lines. They're correct.

[This is one good example of why not to look at code at gone 2 o'clock in a morning.] :D

[Old Pedant]

Well, I get nothing but a timeout when I hit the page you noted:
http://92.237.212.5/test.php

So I have no idea what the error is that you are getting.

In any reasonable language, when you get a parse error it would tell you exactly what line the error is on. I gather from the remarks of others here that PHP isn't that reasonable.

Okay, so use the classic "trick" for recalcitrant languages.

Start off by temporarily removing as much code as possible. For example, just do this much:

<?php
//
// Scadoo! - Beta V0.0.1
// index.php
//

include 'includes/dbConfig.php';
include 'themes/default/overall_header.php';

?>
<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid ";
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount "
. " FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid ";
$main = mysql_query($mainquery) or die(mysql_error());
$reply = mysql_query($replyquery) or die(mysql_error());

$priorcat = -1;
?>


If you don't get a parse error out of that, then start adding more code, a bit at a time, until you finally get the parse error. Ugly trial and error, but if PHP doesn't help you diagnose the point of error, dunno what else you can do.

[Old Pedant]

Oh...and the other thing you should clearly do is test those queries in some DB tool, not in your own code, to be sure that *they* don't give errors.

You could do

$echo $mainquery . "<hr>" . $replyquery . "<hr>";

And then copy/paste each query separately to the DB tool ("myphpadmin"??? is that right??) and make sure they get reasonable results.

[MattF]

In any reasonable language, when you get a parse error it would tell you exactly what line the error is on. I gather from the remarks of others here that PHP isn't that reasonable.

It was the end of file where it was saying the parse error was, (line 86). That's all it was printing to output, if I recall.

[MattF]

Change:


</tr>
<tr>
<td class="sep" colspan="4"></td>
</tr>
</table>

<?php
include 'themes/default/overall_footer.php';
?>


to:


</tr>
<tr>
<td class="sep" colspan="4"></td>
</tr>
<?php
}
?>
</table>

<?php
include 'themes/default/overall_footer.php';
?>



The closing brace is missing on the while loop.

[Stewartiee]

Now were getting somewhere :D
http://92.237.212.5/test.php

[Old Pedant]

The closing brace is missing on the while loop.
AHA!

It was there in my original code (post #8 in this thread). <grin style="monstrous" />

[Old Pedant]

Okay. time for somebody who knows PHP to step in, I think.

This error:Notice: Undefined index: topiccount in C:\xampp\htdocs\test.php on line 56

is from this line:

$topiccount = $mainrows["topiccount"];

And the "undefined index" I assume means that MySQL thinks that there's no column of that name coming from the query.

And apparently PHP is case sensitive on column names (first language I have seen that is! even JSP isn't!).

Because in the SQL it is topicCount

$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount " ...

So change one or the other to match.

**********************

Then the next two errors relate to the same line
Notice: Undefined variable: replies in C:\xampp\htdocs\test.php on line 58

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\test.php on line 58

And now I'm out of my depth.

Line 58 is this:
if ( ! $replyrows = mysql_fetch_array($reply) )

And unless the name $reply is a reserved word in PHP (hmmm...I guess it might well be...if so, just change it), I don't see what the WARNING there is nattering about. After all, I did
$reply = mysql_query($replyquery) or die(mysql_error());

Until/unless some PHP guru comes along, maybe you could assume that $reply is poison and just change those two uses of it (the only uses) to maybe $theReply ???

**************

Anyway, after getting that error on line 58, then the error message that my code produced ("Mismatch on number of records in reply!") is of course correct: Indeed, since there are *no* records in "reply" you have a mismatch.

[MattF]

The array keys are case sensitive. :)

With regards to the second 'error', I haven't had another proper look at the code yet, but I would suggest that probably isn't an error, merely incorrect logic. Replies don't necessarily exist, so these:


if ( ! $replyrows = mysql_fetch_array($reply) )
{
die("Mismatch on number of records in reply!");
}
$replycount = $replyrows["replycount"];
if ( $fid != $replyrows["fid"] || $curcat != $replyrows["catid"] )
{
die("Mismatch on fid or catid from replyrows!");
}


should be enclosed within an 'if rows exist' type clause rather than throwing an error if none do, similar to the following:


if (mysql_num_rows($reply))
{
//There are some replies to the initial post so process the counts here.
[code here]
}

[Stewartiee]

Thanks everyone for your help :) I just took out the replies row as it was causing too much problems. I'll probably replace it in future. With this all working now, I am going to restart building the whole project to make it cleaner.

Thanks for your help :)

[MattF]

Thanks everyone for your help :) I just took out the replies row as it was causing too much problems. I'll probably replace it in future. With this all working now, I am going to restart building the whole project to make it cleaner.

Thanks for your help :)

If you use that clause I posted above, you should have no need to remove it.

[Stewartiee]

I'm not sure a replies column is exactly needed on the homepage, maybe a last post column. But i'll leave that and updating post icons to show new posts, for another day :)

Also how would I go around removing the blank tables if there are no forums attached to a category. As represented by the "Other" category.

http://92.237.212.5/test.php

[Old Pedant]

Matt: Go look at the SQL query I used for the replies.

SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount
FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F
ON C.catid = F.catid
LEFT JOIN scadoo_replies AS R ON R.fid = F.fid
GROUP BY C.catid, F.fid
ORDER BY C.catid, F.fid

If the other query is getting records with catid and fid, then this query *must* be getting the exact same records. And then the LEFT JOIN to scadoo_replies would *NOT* affect the rest of the query.

And since I'm doing COUNT(R.fid) that *should* return a correct value (even zero).

Stewart: Can you please try that query (just above) in a DB tool--*not* in your web page?? See what it gives you?

Also, I (perhaps foolishly) assumed you *wanted* ALL the categories, whether there are any forums in them or not. To elimnate categories with no forums, just change the *FIRST* (but not the second!) LEFT JOIN in each of those two queries to INNER JOIN. Presto.

Oh, w.t.h. That is,
$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_category AS C INNER JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid ";
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount "
. " FROM scadoo_category AS C INNER JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid ";


See how easy this stuff can be if you get the SQL doing most of the work for you??

[Old Pedant]

Finally, if for some reason PHP balks at using two queries and two result sets at the same time, there's another way to do this: We could create a VIEW in MySQL and then LEFT JOIN the VIEW to the mainquery and thus have only *ONE* query!

If you want to try that, ask again.

[MattF]

Matt: Go look at the SQL query I used for the replies.

SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount
FROM scadoo_category AS C LEFT JOIN scadoo_forums AS F
ON C.catid = F.catid
LEFT JOIN scadoo_replies AS R ON R.fid = F.fid
GROUP BY C.catid, F.fid
ORDER BY C.catid, F.fid


That's why I didn't phrase my answer as an absolute answer. :) I know I'm not exactly on the ball today, (not an unknown phenomena :D), so it was intended, (unless I misphrased it), as a possibility suggestion. :)

[Stewartiee]

I must say thanks for showing me how to remove that unused category. Not quite sure the other thing your asking me though...

[Old Pedant]

Don't you have some MySQL database *utility* handy? (I think there is one called myphpadmin? or mysqladmin?) Or could you maybe simply use MySQL from the command line?

If so, copy/paste that "replies" query into the utility and then execute it to see what results you get from it.

(I use MySQL with JSP and there are *TONS* of Java-based GUI tools for MySQL out there. But more often than not I just use the mysql command line tools.)

[Old Pedant]

Matt: Yeah, I saw the disclarimer. I was just explaining why I don't *think* that's the problem.

[Stewartiee]

Oh! in phpMyadmin. Okay I done it, and it got no errors and showed the catid, fid and how many replies were in it. Meaning it's correct coding.

[Old Pedant]

I'm not sure a replies column is exactly needed on the homepage, maybe a last post column.
So you mean the date/time of the last post? That's doable. But you'd still need to "hit" the replies table to get that, in essentially the same manner as I was trying to get the count. So we really need you to try some debugging of the mysql queries to progress further.

*DID* you try changing the name $reply to something else???

Oh, and if you want the date/time of the last post instead of (or, actually, in addition to! easy to get both!) the count, then you need to show the relevant fields (and field *types*) in your tables.

[Stewartiee]

Would you then use the date and time to then determine whether theres been a new post? Then if there was since the last time the new post icon shows, and if not it shows the no new post icon?

[Old Pedant]

showed the catid, fid and how many replies were in it.
Great. So if the problem is not the word $reply, then I have to assume that PHP has some silly limitation of only being able to process one result set (that is, only one mysql_fetch_array collection) at a time. (Hard to believe that's true, but ASP.NET has a similar restriction on DataReader so maybe it is.)

So then we go to the VIEW approach.

[Old Pedant]

Would you then use the date and time to then determine whether theres been a new post? Then if there was since the last time the new post icon shows, and if not it shows the no new post icon?

You could do that. Yes. What is your definition of "since the last time"?? Are you logging into the db when the user last made a query? Or are you relying on a cookie to tell you when the "last time" was??

[Old Pedant]

Gotta go eat lunch. Back in a while.

How about posting the code as you are now using it, so we're on the same page again?

Or send it to me: asp ATSIGN juncojunction PERIOD com

[Stewartiee]

The login system is currently using sessions, although I don't think this is the best way to process it. If there's a better way, please suggest it, then i'll research into it.

The current coding as viewed on: http://92.237.212.5/test.php
<?php
//
// Scadoo! - Beta V0.0.1
// index.php
//
error_reporting(E_ALL);
include 'includes/dbConfig.php';
include 'themes/default/overall_header.php';

?>
<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_category AS C INNER JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid ";
$main = mysql_query($mainquery) or die(mysql_error());

$priorcat = -1;
while ( $mainrows = mysql_fetch_array($main) )
{
// now check if change of category:
$curcat = $mainrows["catid"];
if ( $priorcat != $curcat )
{
// yes...new category...put out category header
?>
<tr>
<td class="fhead" colspan="3">
<?php echo $mainrows['catname'].' - '.$mainrows['catdesc']; ?>
</td>
</tr>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="65%"><center>Forum</center></td>
<td class="frow" width="15%"><center>Topics</center></td>
</tr>
<?php
// and remember new category
$priorcat = $curcat;
}
// now get the info for a single forum:
$fid = $mainrows["fid"];
$fname = $mainrows["fname"];
$fdesc = $mainrows["fdesc"];
$topiccount = $mainrows["topicCount"];
?>
<tr>
<td class="frow3" colspan="1">
<center><img border="0" src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center>
</td>
<td class="frow2" colspan="1"><strong>
<a href="view_forum.php?fid=<?php echo $fid.'">'.$fname; ?></a></strong>
<br /><?php echo $fdesc; ?>
</td>
<td class="frow3" style="text-align: center;"><?=($topiccount) ?></td>
</tr>
<?php
}
?>
<tr>
<td class="sep" colspan="3"></td>
</tr>
</table>

<?php
include 'themes/default/overall_footer.php';
?>

[Stewartiee]

I got replies working, although I think a last post column should be put there too.

<?php
//
// Scadoo! - Beta V0.0.1
// index.php
//
error_reporting(E_ALL);
include 'includes/dbConfig.php';
include 'themes/default/overall_header.php';

?>
<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$mainquery = "SELECT C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc, COUNT(T.fid) AS topicCount "
. " FROM scadoo_category AS C INNER JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_topics AS T ON T.fid = F.fid "
. " GROUP BY C.catid, C.catname, C.catdesc, F.fid, F.fname, F.fdesc "
. " ORDER BY C.catid, F.fid ";
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount "
. " FROM scadoo_category AS C INNER JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid ";
$main = mysql_query($mainquery) or die(mysql_error());
$reply = mysql_query($replyquery) or die(mysql_error());

$priorcat = -1;
while ( $mainrows = mysql_fetch_array($main) )
{
// now check if change of category:
$curcat = $mainrows["catid"];
if ( $priorcat != $curcat )
{
// yes...new category...put out category header
?>
<tr>
<td class="fhead" colspan="4">
<?php echo $mainrows['catname'].' - '.$mainrows['catdesc']; ?>
</td>
</tr>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="65%"><center>Forum</center></td>
<td class="frow" width="15%"><center>Topics</center></td>
<td class="frow" width="15%"><center>Replies</center></td>
</tr>
<?php
// and remember new category
$priorcat = $curcat;
}
// now get the info for a single forum:
$fid = $mainrows["fid"];
$fname = $mainrows["fname"];
$fdesc = $mainrows["fdesc"];
$topiccount = $mainrows["topicCount"];

if ( ! $replyrows = mysql_fetch_array($reply) )
{
die("Mismatch on number of records in reply!");
}
$replycount = $replyrows["replyCount"];
if ( $fid != $replyrows["fid"] || $curcat != $replyrows["catid"] )
{
die("Mismatch on fid or catid from replyrows!");
}
?>
<tr>
<td class="frow3" colspan="1">
<center><img border="0" src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center>
</td>
<td class="frow2" colspan="1"><strong>
<a href="view_forum.php?fid=<?php echo $fid.'">'.$fname; ?></a></strong>
<br /><?php echo $fdesc; ?>
</td>
<td class="frow3" style="text-align: center;"><?=($topiccount) ?></td>
<td class="frow2" style="text-align: center;"><?=($replycount) ?></td>
</tr>
<?php
}
?>
<tr>
<td class="sep" colspan="4"></td>
</tr>
</table>

<?php
include 'themes/default/overall_footer.php';
?>

[Old Pedant]

But what did you *CHANGE*????

I just ran a "diff" on my original version versus what you just posted, and aside from "replyCount" in place of "replycount", there wasn't any change that affected $reply or $replyrows.

So I'm lost: Why didn't it work before??? The line where the error was reported, line 58, is *completely* unchanged. BIZARRE!

I have to ask: How come so many PHP people insist on using <?php echo xxx; ?> instead of using the short form <?=xxx?> ???? The latter is *so* much easier to plunk in place in HTML output, seems to me. (Note that the same situation exists in ASP, where many people actually write <% Response.Write( xxx ) %> in place of just <%=xxx%>, so I have to think people are just gluttons for punishment.)

Anyway...you didn't answer my question about what your replies table looks like, so I'll just take a stab at it.

You *should* be able to just change that second query to
$replyquery = "SELECT C.catid, F.fid, COUNT(R.fid) AS replyCount, MAX(R.replydate) AS lastReply "
. " FROM scadoo_category AS C INNER JOIN scadoo_forums AS F "
. " ON C.catid = F.catid "
. " LEFT JOIN scadoo_replies AS R ON R.fid = F.fid "
. " GROUP BY C.catid, F.fid "
. " ORDER BY C.catid, F.fid ";

(The guess, of course, is that your field in scadoo_replies is named "replydate" ... change as appropriate. Note that it *must* be a MySQL DateTime field, *not* a VarChar field.)

And then just add a line here:
$replycount = $replyrows["replyCount"];
if ( $fid != $replyrows["fid"] || $curcat != $replyrows["catid"] )
{
die("Mismatch on fid or catid from replyrows!");
}
$lastreply = $replyrows["lastReply"];

If necessary, convert that to a PHP date datatype (is there such a thing??) and compare it to the datetime the user was last here (you didn't answer where you are getting that from...I'll assume a cookie, so you'll probably have to convert it from string to date datatype as well) and if the DB date is later, you can change the icon to "has new posts".

[Stewartiee]

I didn't update it properly :P It works now though.
I told you I was using sessions to see whether or not the user is logged in. And i'll try this later..

[Old Pedant]

Not sure how sessions relate to showing new posts. New since the session started??? That's not real useful. What happens when they first visit tomorrow?

Think it would be better to use a cookie that marks last visit. Then tomorrow (or next week or...) they'd see all forums with new posts in them.

[Stewartiee]

As I said, i'm going to change the way login works. For now i've been working on the forum view. Although i've ran into a problem. I'm trying to get the view_forum.php to read from 2 tables at once. From one table it's to read the fid and display threads with the same fid. And the other the last reply date to order them by newest post.

The error seems to be that it's fetching all topics and replies with fid even though ive set it to read fid from scadoo_topics only. Any help?

<?php
//
// Scadoo! - Beta V0.0.1
// index.php
//
error_reporting(E_ALL);
include 'includes/dbConfig.php';
include 'themes/default/overall_header.php';

?>
<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$fid=$_GET['fid'];
$mainquery = "SELECT * FROM scadoo_topics AS T, scadoo_replies AS R WHERE T.fid='$fid' ORDER BY R.replydate";
$forumquery = "SELECT * FROM scadoo_forums WHERE fid='$fid'";
$main = mysql_query($mainquery) or die(mysql_error());
$forum = mysql_query($forumquery) or die(mysql_error());

?>
<?php
while($rows=mysql_fetch_array($forum)){ // Start looping table row
?>
<tr>
<td class="fhead" colspan="6">
<? echo $rows['fname']; ?> - <? echo $rows['fdesc']; ?>
</td>
</tr>
<?php
}
?>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="35%"><center>Thread</center></td>
<td class="frow" width="15%"><center>Author</center></td>
<td class="frow" width="15%"><center>Last Post</center></td>
<td class="frow" width="15%"><center>Replies</center></td>
<td class="frow" width="15%"><center>Views</center></td>
</tr>
<?php
while($rows=mysql_fetch_array($main)){ // Start looping table row
?>
<tr>
<td class="frow3" colspan="1">
<center><img border="0" src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center>
</td>
<td class="frow2" colspan="1">
<a href="view_topic.php?fid=<? echo $rows['fid']; ?>&tid=<? echo $rows['tid']; ?>"><? echo $rows['topic_title']; ?></a>
</td>
<td class="frow3" style="text-align: center;" colspan="1">
<? echo $rows['user']; ?>
</td>
<td class="frow2" style="text-align: center;"><? echo $rows['replydate']; ?></td>
<td class="frow3" style="text-align: center;"><?php
$tid=$rows['tid'];
$query = "SELECT tid, COUNT(tid) FROM scadoo_replies WHERE tid='$tid'";

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
echo "". $row['COUNT(tid)'] ."";
}
?></td>
<td class="frow2" style="text-align: center;"><? echo $rows['view']; ?></td>
</tr>
<?php
}
?>
<tr>
<td class="sep" colspan="6"></td>
</tr>
</table>

<?php
include 'themes/default/overall_footer.php';
?>

http://92.237.212.5/new/view_forum.php?fid=1

[Old Pedant]

You aren't JOINing the two tables!

By the rule of SQL, if you select from two tables and don't join them, then you get the CARTESIAN PRODUCT (look it up) of *all* qualifying records from both tables.

So this query:{code]
$mainquery = "SELECT * FROM scadoo_topics AS T, scadoo_replies AS R WHERE T.fid='$fid' ORDER BY R.replydate";
[/code]
Is indeed getting only topics with the right fid, but then it matches each such record with EACH AND EVERY RECORD in the replies table!

To JOIN tables, you can use either an implicit or explicit join:

// implicit join:
$q = "SELECT * "
. " FROM scadoo_topics AS T, scadoo_replies AS R "
. " WHERE T.fid = R.fid '
. " AND T.fid=$fid "
. " ORDER BY R.replydate";

// explicit join:
$q = "SELECT * "
. " FROM scadoo_topics AS T INNER JOIN scadoo_replies AS R "
. " ON T.fid = R.fid '
. " WHERE T.fid=$fid "
. " ORDER BY R.replydate";


Note that *ONLY* INNER JOINs can be coded as implicit joins. LEFT and RIGHT and FULL OUTER JOINs must be coded as explicit joins.

Incidentally, you will note that I removed the apostrophes from around $fid. I *assume* that fid is a numeric field in your database, so it's actually a mistake to compare it to the *string* that the apostrophes would produce. (MySQL is "sloppy" enough that it could handle it with the apostrophes, but why get in the habit of doing things wrongly?)

Finally, note that there is no reason for two SQL queries on this page. You could simply JOIN all three tables together to get a single result set.

[MattF]

I have to ask: How come so many PHP people insist on using <?php echo xxx; ?> instead of using the short form <?=xxx?> ???? The latter is *so* much easier to plunk in place in HTML output, seems to me. (Note that the same situation exists in ASP, where many people actually write <% Response.Write( xxx ) %> in place of just <%=xxx%>, so I have to think people are just gluttons for punishment.)


Portability. :) The compact syntax relies on short tags being enabled which isn't guaranteed to be so. With <? rather than <?php, the latter always works whereas the former, (the <?= syntax too), is reliant upon individual configs.

[Stewartiee]

These aren't really doing anything. It still showing topics and replies where fid = $fid

[Old Pedant]

???? Yes? That's what you asked the query to do.

If you wanted something different, I sure didn't understand that from your post.

If you'd just say what you wanted the page to show--and not give us code--it might be easier to help.

[Stewartiee]

Okay i'll explain again. I'm wanting to:
1) Fetch topics from scadoo_topics where fid=$fid
2) List replydates from scadoo_replies.

However since there is a fid in both databases it's fetching the topics and replies and posting them on the view_forum.php page. I just want it to fetch the fid from scadoo_topics not from scadoo_replies.

As you see on the page below it's fetching the topics and replies where fid=$fid, causing the page to show topics and replies instead of just topics.

http://92.237.212.5/new/view_forum.php?fid=1

[Old Pedant]

Okay, the reason you get both fid's is because you are doing "SELECT *" and that *says* "get all fields from all tables." *IF* you would EXPLICITLY list the fields you need, that wouldn't happen. That is,

SELECT T.fid, T.tid, T.topic_title, R.reply_date ...


But...As I said before, you really need to stop making nested queries and try to use only *ONE* query for the entire page.

But the query you showed in post #61 doesn't match the display of "view_forum.php", as the query is getting just the date/time of the latest reply whereas the display shows getting the date/time *AND* the name of the person making that reply.

[Old Pedant]

You have never shown us all the fields in your tables, so to some degree it's hard to guess what the final "right" answer is.

For example, is there a "user" field in *both* the topics and replies tables??

[Old Pedant]

Also, do you have an autonumber field in your scadoo_replies table? That is, an "rid" field?? If not, I assume that replydate is a date WITH time field?

[Stewartiee]

I can give you access to phpmyadmin?

[Old Pedant]

Let's take a stab at it first without going that far.

First of all, you can use phpmyadmin to create two new VIEWS that will help tremendously.

Try these two queries, each of which creates one view (if I have any field names wrong, change them of course):

CREATE VIEW latestReplyPerThread
AS
SELECT R.tid, R.user, R.replydate
FROM scadoo_replies AS R,
( SELECT tid, MAX(replydate) AS maxdate
FROM scadoo_replies
GROUP BY tid ) AS M
WHERE R.tid = M.tid
AND R.replydate = M.maxdate;


CREATE VIEW replyCountPerThread
AS
SELECT tid, COUNT(*) AS replycount
FROM scadoo_replies
GROUP BY tid;



After they are successfully created, TEST each of them, separately, by simply doing:

SELECT * FROM latestReplyPerThread;

SELECT * FROM replyCountPerThread;


If they seem to be giving you the right answers, then we're ready to tackle the revised page.

Again, I don't even *HAVE* PHP on my machine, so this is all off the top of my head. And I *do* tend to make silly PHP mistakes, due to lack of experience using it. So try to correct my obvious idiocies.

Try this:
<?php
//
// Scadoo! - Beta V0.0.1
//
error_reporting(E_ALL);
include 'includes/dbConfig.php';
include 'themes/default/overall_header.php';

?>
<table cellpadding="2" style="border-collapse: collapse;" class="table">
<?php
$fid=$_GET['fid'];
$sql = "SELECT F.fname, F.fdesc, F.fid, T.tid, T.topic_title, T.view, T.user AS topicstarter, "
. " IFNULL(L.user,'(no replies)') AS lastreplier, L.replydate, "
. " IFNULL(C.replycount,0) AS replies "
. " FROM scadoo_forums AS F INNER JOIN scadoo_topics AS T
. " ON F.fid = T.fid "
. " LEFT JOIN latestReplyPerThread AS L ON L.tid = T.tid "
. " LEFT JOIN replyCountPerThread AS C ON C.tid = T.tid "
. " WHERE F.fid = " . $fid
. " ORDER BY T.tid "
$main = mysql_query($sql) or die(mysql_error());
// all rows contain the forum info, so get that info from the first row:
$first = true;

while($rows=mysql_fetch_array($main)) // Start looping table row
{
if ( $first ) // get forum info first row only
{
?>
<tr>
<td class="fhead" colspan="6">
<? echo $rows['fname']; ?> - <? echo $rows['fdesc']; ?>
</td>
</tr>
<tr>
<td class="frow" width="1%"></td>
<td class="frow" width="35%"><center>Thread</center></td>
<td class="frow" width="15%"><center>Author</center></td>
<td class="frow" width="15%"><center>Last Post</center></td>
<td class="frow" width="15%"><center>Replies</center></td>
<td class="frow" width="15%"><center>Views</center></td>
</tr>
<?php
// end of dumping out forum header one time only
$first = false; // so we don't do this again
}
// now one row per thread:
// the image should depend on checking vs. cookie to see if current reader
// is up to date on this thread...but we'll do that later
?>
<tr>
<td class="frow3" colspan="1">
<center><img border="0" src="http://www.pixel2life.com/forums/style_images/p2l22/bf_nonew.gif"></center>
</td>
<td class="frow2" colspan="1">
<a href="view_topic.php?fid=<? echo $rows['fid']; ?>&tid=<? echo $rows['tid']; ?>"><? echo $rows['topic_title']; ?></a>
</td>
<td class="frow3" style="text-align: center;" colspan="1">
<? echo $rows['topicstarter']; ?>
</td>
<td class="frow2" style="text-align: center;">
<? echo $rows['replydate']; ?><br/><? echo $rows['replier']; ?>
</td>
<td class="frow3" style="text-align: center;">
<?php echo $rows['replycount']; ?>
</td>
<td class="frow2" style="text-align: center;"><? echo $rows['view']; ?></td>
</tr>
<?php
} // end of the loop on rows ... shouldn't you close the connection, etc.???
?>
<tr>
<td class="sep" colspan="6"></td>
</tr>
</table>
<?php
include 'themes/default/overall_footer.php';
?>

[Stewartiee]

CREATE VIEW latestReplyPerThread
AS
SELECT R.tid, R.user, R.replydate
FROM scadoo_replies AS R,
( SELECT tid, MAX(replydate) AS maxdate
FROM scadoo_replies
GROUP BY tid ) AS M
WHERE R.tid = M.tid
AND R.replydate = M.maxdate;


That's having problems creating. Do you happen to have MSN or something. It seems much easier than discussing it here.

The error code returned was "#1349 - View's SELECT contains a subquery in the FROM clause". It seems to be something with MAX as it was red.

[Old Pedant]

WOW! Didn't know that MySQL didn't allow subquery in a VIEW! Huh...could have *sworn* I used that before.

What version of MySQL are you using???? Please don't tell me it's something older than version 5.

Well, we *can* do this by adding another view. In place of that one, use two, thus:

CREATE VIEW latestReplydateByThread
AS
SELECT tid, MAX(replydate) AS maxdate
FROM scadoo_replies
GROUP BY tid;

CREATE VIEW latestReplyPerThread
AS
SELECT R.tid, R.user, R.replydate
FROM scadoo_replies AS R, latestReplydatePerThread AS M
WHERE R.tid = M.tid
AND R.replydate = M.maxdate;


Just a minor hack.

No, I don't use IM. I'm way overwhelmed with email and forums and work, as it is.

[Stewartiee]

I'm using MySQL 5.1.33
So would that page code above still work with these codes you just included? Or do I need to edit something...

[Old Pedant]

Should work by just adding those two views, along with the third view from the prior message.

Again, add the views to the DB and test them with "SELECT *". If they work and results seem reasonable, then give it a shot.