[RESOLVED] trying to count again

[jerrylouise]

$sql = "SELECT cat.`id`, cat.`catagory`, top.`title`, top.`about`, post.`id`, post.`topic`, post.`username`, post.`date`
 COUNT(post.`id`) WHERE top.`title` = post.`id` AS `nomtopics` FROM `forum-catagory` AS `cat` 
LEFT JOIN `forum-topics` AS `top` ON cat.`id` = top.`catagory_id`
LEFT JOIN `forum-posts` AS `post` ON top.`catagory_id` = post.`id` 
ORDER BY cat.`id`";

ok this is my good old sql im trying to get it to count the number of topics where the topic id matches the post id

[bpat1434]

Your SQL is malformed..... it should be:

SELECT COUNT(post.`id`) AS `numtopics`, cat.`id`, cat.`category`, 
    top.`title`, top.`about`, 
    post.`id`, post.`topic`, post.`username`, post.`date`
FROM `forum-category` AS cat
LEFT JOIN `forum-topics` AS top
    ON cat.`id` = top.`category_id`
LEFT JOIN `forum-posts` AS post
    ON top.`category_id` = post.`id`
WHERE top.`title` = post.`id`
ORDER BY cat.`id` ASC

[jerrylouise]

i ether get told that it sould be group by, or when i change it to group by i get no results at all.

[bpat1434]

Try grouping by something that is unique (thus nullifying the GROUP BY statement)

SELECT COUNT(post.`id`) AS `numtopics`, cat.`id`, cat.`category`, 
    top.`title`, top.`about`, 
    post.`id`, post.`topic`, post.`username`, post.`date`
FROM `forum-category` AS cat
LEFT JOIN `forum-topics` AS top
    ON cat.`id` = top.`category_id`
LEFT JOIN `forum-posts` AS post
    ON top.`category_id` = post.`id`
WHERE top.`title` = post.`id`
GROUP BY post.`id`
ORDER BY cat.`id` ASC

[jerrylouise]

errors out

Query failed(SELECT COUNT(post.`id`) AS `numtopics`, cat.`id`, cat.`catagory`, top.`title`, top.`about`, top.`catagory_id`, post.`id`, post.`topic`, post.`username`, post.`date` FROM `forum-catagory` AS cat LEFT JOIN `forum-topics` AS top ON cat.`id` = top.`category_id` LEFT JOIN `forum-posts` AS post ON top.`catagory_id` = post.`id` WHERE top.`title` = post.`id` GROUP BY post.`id` ORDER BY cat.`id` ASC): Unknown column 'top.category_id' in 'on clause'

category_id is how i spelled it in the database, its always category.

[jerrylouise]

ok sorted the spelling but it prints no results >_<

<?php
$sql = "SELECT COUNT(post.`id`) AS `numtopics`, cat.`id`, cat.`catagory`, 
    top.`title`, top.`about`, top.`catagory_id`, 
    post.`id`, post.`topic`, post.`username`, post.`date`
FROM `forum-catagory` AS cat
LEFT JOIN `forum-topics` AS top
    ON cat.`id` = top.`catagory_id`
LEFT JOIN `forum-posts` AS post
    ON top.`catagory_id` = post.`id`
WHERE top.`title` = post.`id`
GROUP BY post.`id`
ORDER BY cat.`id` ASC";
$result = mysql_query($sql) or die("Query failed($sql): " . mysql_error()); 
   print '<table width="100%" border="0" cellpadding="3" cellspacing="0" >';
?>
<tr class="purple_top"><td width="70%"><font size="+2">Welcome to the forum <?php echo $usrname ?></font></td><td width="15%"></td><td width="15%"></td></tr>
<?php
print '<tr class="purple_top"><td width="80%">Forum</td><td width="10%">Topics</td><td width="10%">Posts</td></tr>'; 
$last_cat = ''; 
$i = 0; 
$m = 0; 
WHILE($row = mysql_fetch_array($result)) { 
    if($row['catagory'] != $last_cat) { 
        $last_cat = $row['catagory']; 
        printf("<tr class=\"purple_title\"><td>%s" . "</td><td></td><td></td></tr>", $row["catagory"]); 
    } 
    if($i++ % 2) 
      { 
         print '<tr class="wight">'; 
      } 
      else 
      { 
         print '<tr class="pink">'; 
      } 
	printf("<td><a href=\"forum_topic.php?id=%s\">%s</a>" . "</td><td>%s" . "</td><td>1</td></tr>", $row["title"], $row["title"], $row["numtopics"]);
    if($m++ % 2) 
      { 
         print '<tr class="wight">'; 
      } 
      else 
      { 
         print '<tr class="pink">'; 
      }
	printf("<td><font size=\"-1\">%s" . "</font></td><td></td><td></td></tr>", $row["about"]); 
}
print '</table>'; 
?>

And i've still to count the 4th table posts 🙁

[bpat1434]

for the first line in the WHILE loop, put this:

var_dump($row);

Give me the output.

[jerrylouise]

WHILE ($row = mysql_fetch_array($result)) { 
var_dump($row); 

like this, there is no output.

[paulnaj]

You seem to be wanting to write reasonably difficult code and minimise your typing at the same time.

Give up on trying to minimise the amount of characters you type. Don't use 'handy' shortcuts as they become impossible to debug when something goes wrong.

I can't be sure, but I think the problem could the WHERE clause. You seem to be wanting a 'title' to be equal to an 'id'?

Here's how I spotted it ...

<?php
$sql = "SELECT COUNT(post.`id`) AS `numtopics`
,	`forum-catagory`.`id`
,	`forum-catagory`.`catagory`
,	`forum-topics`.`title`
,	`forum-topics`.`about`
,	`forum-topics`.`catagory_id`
,	`forum-posts`.`id`
,	`forum-posts`.`topic`
,	`forum-posts`.`username`
,	`forum-posts`.`date`
FROM `forum-catagory`
LEFT JOIN `forum-topics` ON `forum-catagory`.`id` = `forum-topics`.`catagory_id`
LEFT JOIN `forum-posts` ON `forum-topics`.`catagory_id` = `forum-posts`.`id`
WHERE `forum-topics`.`title` = `forum-posts`.`id`
GROUP BY `forum-posts`.`id`
ORDER BY `forum-catagory`.`id` ASC
";

$result = mysql_query($sql) or die("Query failed($sql): " . mysql_error());
?>

Took about 30 secs to format it.

P 😉

[jerrylouise]

paulnaj wrote:

You seem to be wanting to write reasonably difficult code and minimise your typing at the same time.

Give up on trying to minimise the amount of characters you type. Don't use 'handy' shortcuts as they become impossible to debug when something goes wrong.

I can't be sure, but I think the problem could the WHERE clause. You seem to be wanting a 'title' to be equal to an 'id'?

Here's how I spotted it ...

<?php
$sql = "SELECT COUNT(post.`id`) AS `numtopics`
,	`forum-catagory`.`id`
,	`forum-catagory`.`catagory`
,	`forum-topics`.`title`
,	`forum-topics`.`about`
,	`forum-topics`.`catagory_id`
,	`forum-posts`.`id`
,	`forum-posts`.`topic`
,	`forum-posts`.`username`
,	`forum-posts`.`date`
FROM `forum-catagory`
LEFT JOIN `forum-topics` ON `forum-catagory`.`id` = `forum-topics`.`catagory_id`
LEFT JOIN `forum-posts` ON `forum-topics`.`catagory_id` = `forum-posts`.`id`
WHERE `forum-topics`.`title` = `forum-posts`.`id`
GROUP BY `forum-posts`.`id`
ORDER BY `forum-catagory`.`id` ASC
";

$result = mysql_query($sql) or die("Query failed($sql): " . mysql_error());
?>

Took about 30 secs to format it.

P 😉

using your formating it not longer outputs but seems to take a long time for the webpage to work out what its doing.

[paulnaj]

Do you know how to write PHP?

[jerrylouise]

paulnaj wrote:

Do you know how to write PHP?

im learning.

[paulnaj]

OK, sorry, but did you understand what I was trying to tell you?

I replaced your SQL shortcuts to find out what was wrong.

Try to see yourself where I made an error.

And also, was the WHERE correct or not?

P.

[jerrylouise]

paulnaj wrote:

OK, sorry, but did you understand what I was trying to tell you?

I replaced your SQL shortcuts to find out what was wrong.

Try to see yourself where I made an error.

And also, was the WHERE correct or not?

P.

oh i'll have to take a look, i think i may give up on this and just add another table that counts. like numtopics = 1. on add new topic numtopics == numtopics ++ 1. or somthing like that though it makes it less dynamic.

[bpat1434]

paulnaj wrote:

You seem to be wanting to write reasonably difficult code and minimise your typing at the same time.

Give up on trying to minimise the amount of characters you type. Don't use 'handy' shortcuts as they become impossible to debug when something goes wrong.

I can't be sure, but I think the problem could the WHERE clause. You seem to be wanting a 'title' to be equal to an 'id'?

Here's how I spotted it ...

<?php
$sql = "SELECT COUNT(post.`id`) AS `numtopics`
,	`forum-catagory`.`id`
,	`forum-catagory`.`catagory`
,	`forum-topics`.`title`
,	`forum-topics`.`about`
,	`forum-topics`.`catagory_id`
,	`forum-posts`.`id`
,	`forum-posts`.`topic`
,	`forum-posts`.`username`
,	`forum-posts`.`date`
FROM `forum-catagory`
LEFT JOIN `forum-topics` ON `forum-catagory`.`id` = `forum-topics`.`catagory_id`
LEFT JOIN `forum-posts` ON `forum-topics`.`catagory_id` = `forum-posts`.`id`
WHERE `forum-topics`.`title` = `forum-posts`.`id`
GROUP BY `forum-posts`.`id`
ORDER BY `forum-catagory`.`id` ASC
";

$result = mysql_query($sql) or die("Query failed($sql): " . mysql_error());
?>

Took about 30 secs to format it.

P 😉

That's not always true. The original SQL that i wrote uses what you call "Shortcuts" which is just valid SQL code.

As for the query, try a simpler query, and build up to it. Or even better, execute the query in phpMyAdmin and see what output it gives 🙂

Start with this query:

SELECT COUNT(post.`id`) AS `numtopics`
FROM `forum-posts` AS post

If that doesn't work, then you know something else is messed up. Otherwise, continue on to something like this:

SELECT COUNT(post.`id`) AS `numtopics`, cat.`id`, cat.`catagory`,
     top.`title`, top.`about`, top.`catagory_id`,
     post.`id`, post.`topic`, post.`username`, post.`date`
FROM `forum-catagory` AS cat
LEFT JOIN `forum-topics` AS top
    ON cat.`id` = top.`catagory_id`
LEFT JOIN `forum-posts` AS post
    ON top.`id` = post.`topic_id`
WHERE top.`title` = '%'
ORDER BY cat.`id` ASC

From there it's just a short leap to help narrow down the results to something like only one topics posts or one page of posts...

[paulnaj]

Hi Bpat,

Well you know what you've written , how to interpret etc. but Jerry doesn't.

Shortcuts (that's what they are) are ALWAYS harder to debug. Writing out code fully is ALWAYS easier to debug, even for the experts.

If you're just starting to code, shortcuts will often hinder understanding.

Also, valid code yes, but result no ... we were trying to find out why. The first thing I did was expand the SQL and out popped a potential glitch.

IMHO, unless you're an expert, write stuff out as fully as possible.

But hey.

P.

[jerrylouise]

well because of the way i made the table doing it the easy way is also the hard way so looks like i need to keep at trying to make this way work.

[paulnaj]

Hi Jerry,

Your tables look fine to me!

But please, have you checked out the WHERE clause yet. It doesn't seem to be correct.

It is looking for a values in a 'title' (character?) field to be equal to values in an 'id' (numeric?) field. I could be wrong but looks likely to be the problem because whilst the syntax remains valid (so the query will run) you still won't get any results.

P 🙂

[bpat1434]

Jerry:

What do your tables look like? How are the set up? Give us the table structure and we can help you out more.

[jerrylouise]

ok emm, The tables relating to the forum except for the user table as it sould not affect the workings of the forum.

forum-catagory (id, catagory)
forum-topics (id, catagory_id, title, about, lastpost)
forum-posts (id, topic, username, date, subject, post, lastpost)
forum-reply (id, post_id, username, date, subject, post)

Sample records.

forum-catagory
id.............catagory
1..........About the Site

forum-topics
id...........catagory_id.................title.......................about.....................lastpost......
1...................1................Announcments...........about the forum..........user1........

forum-posts
id.....topic.....................username..........date.........subject...........post.........lastpost...
14...Announcments....JerryLouise......07. 3, 2007.....test............test..........user1.....

forum-reply
id.......post_id.......username........date..........subject.........post...........
1......test......JerryLouise.....22/12/22.....sample...........text