[SOLVED] Annoying Query...

[dannyluked]

Hi,

I have two tables:

cat

|id|name|

|1|Cat 1|

 

forum

|id|forumname|catid|

|1|Forum 1    |1|

|2|Forum 2    |1|

 

I have this code:

<?php
   include "config.php";
   mysql_connect($server, $db_user, $db_pass) or die(mysql_error());
   mysql_select_db($database) or die(mysql_error());

$forums = mysql_query("SELECT forum.forumname, forum.catid, count(cat.id) AS categorys
FROM forum LEFT OUTER JOIN cat ON forum.catid = cat.id
GROUP BY forum.catid order by forum.id asc") or die(mysql_error());

$result = mysql_query("SELECT cat.id, cat.name, count(forum.id) AS forumcount
FROM cat LEFT OUTER JOIN forum ON cat.id = forum.catid
GROUP BY cat.id order by id asc") or die(mysql_error());

while($qry = mysql_fetch_array($result) and $qry2 = mysql_fetch_array($forums)){
echo "<div id='cat'>$qry[name]</div><div id='forums'>$qry2[forumname]</div>There are $qry2[categorys] forums in $qry[name]!<br><br>";
}
?>

 

This shows:

Cat 1

Forum 1

 

I want it to show:

Cat 1

Forum 1

Forum 2

 

Could anyone please point me in the right direction?

 

[onedumbcoder]

Ill tell u right now that the way u have the code written you will run into many issues, but here is ur solution

 

while($qry = mysql_fetch_array($result))
{
     while($qry2 = mysql_fetch_array($forums))
     {
          echo "<div id='cat'>$qry[name]</div><div id='forums'>$qry2[forumname]</div>There are $qry2[categorys] forums in $qry[name]!<br><br>";
     }
}

[dannyluked]

Thanks, but that still only shows:

Cat 1

Forum 1

 

Does anyone know how to make it show the right thing or another way of doing this?

[kickstart]

Hi

 

Something like this would seem to be what you want.

 

<?php
   include "config.php";
   mysql_connect($server, $db_user, $db_pass) or die(mysql_error());
   mysql_select_db($database) or die(mysql_error());

$forums = mysql_query("SELECT forum.forumname, forum.catid, count(cat.id) AS categorys, cat.name, Deriv1.forumcount
FROM forum 
LEFT OUTER JOIN cat ON forum.catid = cat.id
LEFT OUTER JOIN (SELECT cat.id AS id, count(forum.id) AS forumcount FROM cat LEFT OUTER JOIN forum ON cat.id = forum.catid GROUP BY cat.id) Deriv1 ON cat.id = Deriv1.id
GROUP BY forum.catid order by forum.id asc") or die(mysql_error());

$PrevCat = "";
while($qry = mysql_fetch_array($forums))
{
if ($PrevCat != $qry['catid'])
{
	echo '<div id="cat">'.$qry['name'].' ('.$qry['forumcount'].' forums)</div>';
	$PrevCat = $qry['catid'];
}
echo '<div id="forums">'.$qry['forumname'].'</div><br><br>';
}
?>	

 

Basically do a subselect to get a count of the number of forums in each category, and join that subselect to the other returned rows. When looping round if the category changes then output the line giving the category table.

 

All the best

 

Keith

[dannyluked]

Thanks for the reply!

That shows:

 

Cat 1 (2 forums)

Forum 1

 

This is near enough the same result I had before, and the forum count proves that there are 2 forums yet only one is being displayed!

 

Could this code be made to show all forums in a certain category?

[kickstart]

Hi

 

I should have read it a bit more closely.

 

You really need the first table to be the category table, and then left join in the forum table. However not sure that would cause an issue except for a category that has no forums.

 

With the joins moved around:-

 

<?php
   include "config.php";
   mysql_connect($server, $db_user, $db_pass) or die(mysql_error());
   mysql_select_db($database) or die(mysql_error());

$forums = mysql_query("SELECT forum.forumname, forum.catid, count(cat.id) AS categorys, cat.name, Deriv1.forumcount
FROM cat 
LEFT OUTER JOIN forum ON cat.id = forum.catid
LEFT OUTER JOIN (SELECT catid, count(forum.id) AS forumcount FROM forum GROUP BY catid) Deriv1 ON cat.id = Deriv1.catid
GROUP BY forum.catid order by forum.catid, forum.id asc") or die(mysql_error());

$PrevCat = "";
while($qry = mysql_fetch_array($forums))
{
if ($PrevCat != $qry['catid'])
{
	echo '<div id="cat">'.$qry['name'].' ('.$qry['forumcount'].' forums)</div>';
	$PrevCat = $qry['catid'];
}
echo '<div id="forums">'.$qry['forumname'].'</div><br><br>';
}
?>

 

If that doesn't work, use the sql in phpmyadmin and check what is returned.

 

All the best

 

Keith

[dannyluked]

That just displays the same results. Im not 100% sure what you mean by;

use the sql in phpmyadmin and check what is returned.

But it you mean what results does it show, it shows the forum name with the lowest id that matches the cateogry.

[kickstart]

Hi

 

Ooops, left and extra group by in there:-

 

<?php
   include "config.php";
   mysql_connect($server, $db_user, $db_pass) or die(mysql_error());
   mysql_select_db($database) or die(mysql_error());

$forums = mysql_query("SELECT forum.forumname, forum.catid, count(cat.id) AS categorys, cat.name, Deriv1.forumcount
FROM cat 
LEFT OUTER JOIN forum ON cat.id = forum.catid
LEFT OUTER JOIN (SELECT catid, count(forum.id) AS forumcount FROM forum GROUP BY catid) Deriv1 ON cat.id = Deriv1.catid
ORDER BY forum.catid, forum.id ASC") or die(mysql_error());

$PrevCat = "";
while($qry = mysql_fetch_array($forums))
{
if ($PrevCat != $qry['catid'])
{
	echo '<div id="cat">'.$qry['name'].' ('.$qry['forumcount'].' forums)</div>';
	$PrevCat = $qry['catid'];
}
echo '<div id="forums">'.$qry['forumname'].'</div><br><br>';
}
?>

 

Do you have any way of running a piece of sql directly on the database? Phpmyadmin is a php based tool to allow you to maintain MySQL databases, and one of its function is to allow you to just execute SQL and see the results.

 

All the best

 

Keith

[dannyluked]

That code shows the same thing but will only show one category. So if I have two categorys and a forum for each it will only show one category and one forum!

 

I have phpmyadmin but just so you know it is run from my hosts website, not mine.

[kickstart]

That code shows the same thing but will only show one category. So if I have two categorys and a forum for each it will only show one category and one forum!

 

Why? Unless I have missed something with your design it should give you rows like:-

 

forumname|catid|categorys|name|forumcount|

Forum 1    |1|1|Cat 1|2|

Forum 2    |1|1|Cat 1|2|

 

The categorys count is probably useless though.

 

I have phpmyadmin but just so you know it is run from my hosts website, not mine.

 

Whereabout are you trying this code? Can you just install a copy of it on your local development environment if necessary?

 

All the best

 

Keith

[dannyluked]

I can install phpmyadmin if needed and this is what we have so far!

 

Table:

 

Results on page:

 

This is after the last code you gave me.

 

PS. I hope we can solve this!

[kickstart]

Hi

 

Just set up copies of your tables and found an issue with the sql.

 

Remove , count(cat.id) AS categorys (which I don't think makes sense to have anyway, but if needs be would require joining to another subselect).

 

With that removed the SQL then returns the right data I think.

 

Can't see anything wrong with how it loops round what is returned and when I test run it, it returns the data as expected.

 

All the best

 

Keith

[dannyluked]

It worked!! Thanks alot. It shows perfectley. Im sure I will be back soon with another problem!

 

Thanks again