[SOLVED] Forum Query

[The Little Guy]

I need a query that will:

 

1. Get each category title

2. Get each board in each category

3. Get the count of topics in each board

4. Get the count of posts in each board

 

I can not figure out how to do this in one query (or two)...

[djbuddhi]

1 select category_title from table_name group by category_title

 

if u provide the table design i might have give the SQL ..... or email me 2 buddhikaperera@hotmail.com

[The Little Guy]

OK, I want to modify the query I need.

 

I would like it to:

1. Get the title, description, id from "boards"

2. Get total count of posts per board from "replies"

3. Get total count of topics pre board from "topics"

 

Lets say a category has 3 boards in it, 3 rows should be returned from this query.

 

CREATE TABLE IF NOT EXISTS `boards` (
  `id` bigint(20) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `group` bigint(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint(20) NOT NULL auto_increment,
  `category` varchar(255) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Table structure for table `replies`
--

CREATE TABLE IF NOT EXISTS `replies` (
  `id` bigint(20) NOT NULL auto_increment,
  `text` text NOT NULL,
  `poster_id` bigint(20) NOT NULL,
  `post_date` varchar(30) NOT NULL,
  `title` varchar(255) NOT NULL,
  `poser_name` varchar(255) NOT NULL,
  `topic_id` bigint(20) NOT NULL,
  `category_id` bigint(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `topics`
--

CREATE TABLE IF NOT EXISTS `topics` (
  `id` bigint(20) NOT NULL auto_increment,
  `boardID` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `text` text NOT NULL,
  `poster_id` bigint(20) NOT NULL,
  `post_date` varchar(30) NOT NULL,
  `poser_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

[xtopolis]

edit, i didn't read the top post.. but

sorry, didn't read the top post.. but I don't see how category relates, other than one field in replies.  Is category above boards in hierarchy?  Categories->boards->topics->replies?  If so, shouldn't boards have a cat_id, or was that supposed to be group?

It's a little hard to tell from just the structure.  The code I put below did what your questions asked, but without the categories.. also remove LEFT from the join to only return rows with data.

_________________________________

 

This may work, but I'm not sure it's the best method.. I never really learned the reason behind mysql stuffs..

SELECT b.title AS 'Title', 
b.description AS 'Description', 
b.id 'Board ID', 
COUNT(t.id) AS 'Topics per Board',
COUNT(r.id) AS 'Replies per Board' 

FROM `boards` b 
LEFT JOIN topics t ON(t.boardID = b.id)
LEFT JOIN replies r ON(r.topic_id = t.id)
GROUP BY b.id

[pre]

Title  Description                  Board ID      Topics per Board    Replies per Board

Board 1 This is board 1.              8              0                      0

Board 2 Fear me, for I am board 2. 9              1                      1

Board 3 Man I'm board 3              10            0                      0

Board 4 Four score.                  11            1                      0

[/pre]

[The Little Guy]

That almost works awesome, accept for the order of everything!

 

I get the data I want, I just need to order it a certain way.

 

I first need to order it by categories.order then by boards.group then finally boards.order

 

Thank you SO much for the help so far!

[xtopolis]

Two things:

1) You're using reserved words in your column names.  Which is bad.  Bad Bad TLG.  So enclose them in backticks when you reference them in your order by:  b.`group`

 

2) boards does not have the column `order` in your table structure provided.  I assumed you meant categories here.

 

This is a statement I came up with, joining table categories as well, and adding the multiple orderby clause.  However your structure isn't the best; it uses reserved words, and different words for the same thing (group, category, etc).  Perhaps some normalization is in order.

 

SELECT b.title AS 'Title',
b.description AS 'Description',
b.id 'Board ID',
COUNT(t.id) AS 'Topics per Board',
COUNT(r.id) AS 'Replies per Board'

FROM `boards` b
LEFT JOIN topics t ON(t.boardID = b.id)
LEFT JOIN replies r ON(r.topic_id = t.id)
LEFT JOIN categories c ON(r.category_id = c.id)

GROUP BY b.id

ORDER BY
c.id ASC, b.`group` ASC, c.`order` ASC

[The Little Guy]

Sorry, I added a that column, after I made the post...

 

Here is the order I would like: http://dudeel.com/forums/ I want to convert to this single query due to the fact that the current one uses 10 queries (Seen on bottom of page) to produce the same result.

 

Here is the current (one or two added columns)

 

--
-- Database: `file_host_forums`
--

-- --------------------------------------------------------

--
-- Table structure for table `boards`
--

CREATE TABLE IF NOT EXISTS `boards` (
  `id` bigint(20) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `group` bigint(20) NOT NULL,
  `order` varchar(2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

-- --------------------------------------------------------

--
-- Table structure for table `categories`
--

CREATE TABLE IF NOT EXISTS `categories` (
  `id` bigint(20) NOT NULL auto_increment,
  `category` varchar(255) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Table structure for table `replies`
--

CREATE TABLE IF NOT EXISTS `replies` (
  `id` bigint(20) NOT NULL auto_increment,
  `text` text NOT NULL,
  `poster_id` bigint(20) NOT NULL,
  `post_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `title` varchar(255) NOT NULL,
  `poser_name` varchar(255) NOT NULL,
  `topic_id` bigint(20) NOT NULL,
  `boardID` bigint(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

--
-- Table structure for table `topics`
--

CREATE TABLE IF NOT EXISTS `topics` (
  `id` bigint(20) NOT NULL auto_increment,
  `boardID` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `text` text NOT NULL,
  `poster_id` bigint(20) NOT NULL,
  `post_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `poser_name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

[The Little Guy]

This looks like the query I am after:

 

SELECT c.category, 
b.order,
b.title AS 'Title',
b.description AS 'Description',
b.id 'Board ID',
COUNT(t.id) AS 'Topics in Board',
COUNT(r.id) AS 'Replies in Board'

FROM `boards` b
LEFT JOIN topics t ON(t.boardID = b.id)
LEFT JOIN replies r ON(r.topic_id = t.id)
LEFT JOIN categories c ON(b.group = c.id)

GROUP BY b.id

ORDER BY
c.order ASC, b.`group` ASC, c.`order` ASC

 

Thank You Sooo much for your help!