slight tweak of my query to make it get the correct results ?

[newbeee]

SELECT a.created_on AS a, a.topic_id AS a, b.topic_id AS b, c.category_id AS c
FROM `forum_comments` AS a, `forum_topics` AS b, `forum_category` AS c
WHERE a.topic_id = b.topic_id AND b.category_id = c.category_id AND a.confirmed = '1' AND b.confirmed = '1' AND c.confirmed = '1' ORDER BY a.created_on DESC

 

the first 'a' ok this may be wrong so may change it if i have to but the first 'a' is the date of the post made the data is taken from the 'forum_comments' table

 

the next 'a' is the 'topic_id' from the 'forum_comments' table

 

the 'b' is the topic_id from the 'forum_topic' table

 

the 'c' is the category_id from the 'forum_category'

 

a--------------------a--b--c----comment

2008-12-11 18:45:04 41 41 3----most recent post in category 3

2008-12-11 18:44:56 40 40 3----the post before the most recent in category 3

2008-12-11 18:44:51 39 39 3----the post before the previous in category 3

2008-12-11 18:44:39 38 38 1----most recent post in cat, 1

2008-12-11 18:44:24 37 37 2----most recent post in cat, 2

2008-12-08 21:51:00 37 37 2----the post before the most recent in category 2

2008-11-01 00:11:00 36 36 2----the post before the previous in category 2

 

the results should return....

 

a--------------------a--b--c----comment

2008-12-11 18:45:04 41 41 3----most recent post in category 3

2008-12-11 18:44:39 38 38 1----most recent post in cat, 1

2008-12-11 18:44:24 37 37 2----most recent post in cat, 2

 

the most recent result where the comments refer to each of the categorys, so only one result for cat 1 and cat 2 and cat 3 and all the rest.

 

so there will be what seems missing posts but these are just replies made in other topics prior to the most recent.

 

 

CREATE TABLE IF NOT EXISTS `forum_category` (
  `category_id` bigint(20) NOT NULL auto_increment,
  `confirmed` text,
  `category` text NOT NULL,
  `created_by` text NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `forum_category`
--

INSERT INTO `forum_category` (`category_id`, `confirmed, `category`, `created_by`, `created_on`) VALUES
(1, '1', 'Your stories', 'Admin', '0000-00-00 00:00:00'),
(2, '1', 'Something else', 'jason', '0000-00-00 00:00:00'),
(3, '1', 'Some other random category', 'Admin', '0000-00-00 00:00:00');

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

--
-- Table structure for table `forum_comments`
--

CREATE TABLE IF NOT EXISTS `forum_comments` (
  `postcounter` bigint(20) NOT NULL auto_increment,
  `topic_id` bigint(20) NOT NULL default '0',
  `comment` longtext NOT NULL,
  `username` varchar(65) NOT NULL default '',
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `confirmed` text,
  KEY `postcounter` (`postcounter`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=54 ;

--
-- Dumping data for table `forum_comments`
--

INSERT INTO `forum_comments` (`postcounter`, `topic_id`, `comment`, `username`, `created_on`, `confirmed`) VALUES
(53, 37, 'mmm', 'Jason', '2008-12-08 21:51:00', '1'),
(52, 36, '22222222222222', 'Jason', '0000-00-00 00:00:00', '1');

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

--
-- Table structure for table `forum_topics`
--

CREATE TABLE IF NOT EXISTS `forum_topics` (
  `topic_id` bigint(20) NOT NULL auto_increment,
  `category_id` bigint(20) NOT NULL default '0',
  `topic` longtext NOT NULL,
  `username` text NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `confirmed` text,
  KEY `topic_id` (`topic_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=38 ;

--
-- Dumping data for table `forum_topics`
--

INSERT INTO `forum_topics` (`topic_id`, `category_id`, `topic`, `username`, `created_on`, `confirmed`) VALUES
(36, 2, '2', 'Jason', '2008-12-08 21:42:39', '0'),
(37, 2, 'mmmmmm', 'Jason', '2008-12-08 21:51:00', '1');

 

 

[fenway]

What are you trying to do?

[newbeee]

i have created my own forum.

 

i wish to display dates and usernames of the most recent comments made on the category page.

 

for each of the categorys

 

 

[fenway]

BTW, why is confirmed storing "1" but in a field of TEXT?!?!?

And you have almost no indexes whatsoever -- where are the ones for the JOINs???

 

This is the typical groupwise-max issue that comes up time & time again.

 

First, start with gettting the most recent comments:

 

SELECT 
topic_id
, MAX(created_on)
FROM
forum_comments
WHERE confirmed = '1'
GROUP BY topic_id

 

Normally, we would need to join this back to the comments table to get the rest of the fields from this table... but you're not using them, so we can skip to the main join:

 

SELECT 
cat.category_id
, t.topic_id
, c.created_on
FROM 
forum_categories AS cat
LEFT JOIN forum_topics AS t USING ( category_id )
LEFT JOIN 
( 
SELECT 
topic_id
, MAX(created_on) AS created_on
FROM
forum_comments
WHERE confirmed = '1'
GROUP BY topic_id
) AS c ( USING topic_id )

 

[newbeee]

confirmed = 1  and the post is shown

confirmed = 0 and the post is awaiting approval

confirmed = d the post was placed in the delete queue (to prevent a member from messing up the whole forum, i have a two stange delete to safe guard the forum.

[newbeee]

i just tried the query in phpmyadmin...

i got the following error

Error

 

SQL query: Documentation

 

SELECT cat.category_id, t.topic_id, c.created_on

FROM forum_categories AS cat

LEFT JOIN forum_topics AS t

USING ( category_id )

LEFT JOIN (

 

SELECT topic_id, MAX( created_on ) AS created_on

FROM forum_comments

WHERE confirmed = '1'

GROUP BY topic_id

) AS c(

 

USING topic_id

)

LIMIT 0 , 30

 

MySQL said: Documentation

#1064 - 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 '( USING topic_id )

LIMIT 0, 30' at line 17

[fenway]

confirmed = 1  and the post is shown

confirmed = 0 and the post is awaiting approval

confirmed = d the post was placed in the delete queue (to prevent a member from messing up the whole forum, i have a two stange delete to safe guard the forum.

Doesn't explain why you're not using an ENUM.

 

Does your query have a space beween "AS c" and "( USING" --- mine does, but your post seems to suggest otherwise?

[newbeee]

yep it does have a space, i copied it in to notepad and then copied from there into the query box and tried again and also added a few more spaces and still the error page shows the error and removes the spaces

[fenway]

Sorry, my bad, the parens should around topic_id, not USING... like earlier in the query:

 

SELECT
cat.category_id
, t.topic_id
, c.created_on
FROM
forum_categories AS cat
LEFT JOIN forum_topics AS t USING ( category_id )
LEFT JOIN
(
SELECT
topic_id
, MAX(created_on) AS created_on
FROM
forum_comments
WHERE confirmed = '1'
GROUP BY topic_id
) AS c USING ( topic_id )

[newbeee]

ok that query works but it is showing my the following results, i think it shows the same results as my rather messy code did. but it has put them in order of category_id.

 

i only need one result for each of the category_id and where the created_on (in the forum_comments) is the most recent date

 

but also where `confirmed` = "1" in all tables

 

category_id----------topic_id----------created_on

1----------------------38----------2008-12-11 18:44:39

2----------------------36----------0000-00-00 00:00:00

2----------------------37----------2008-12-11 18:44:24

3----------------------40----------2008-12-11 18:44:56

3----------------------41----------2008-12-11 18:45:04

3----------------------39----------2008-12-11 18:44:51

 

 

expected results would be...

 

category_id----------topic_id----------created_on

1----------------------38----------2008-12-11 18:44:39

2----------------------37----------2008-12-11 18:44:24

3----------------------41----------2008-12-11 18:45:04

 

 

 

i will be back online sometime tomorrow, been up all day!! need to get a bit of shut eye.

 

thank you for all the help you have given so far, i really do appreciate it.

[fenway]

Right, that makes sense.

 

I'm not sure if this will work -- the subquery should definitely pull the value you're interested in -- but I haven't thought the rest through, the join condition is a bit tricky.

 

What does this produce?

 

SELECT
cat.category_id
, t.topic_id
, c.created_on
FROM
forum_comments AS c
INNER JOIN forum_topics AS t USING ( topic_id )
INNER JOIN 
(
SELECT
t.category_id
, MAX(c.created_on) AS created_on
FROM
forum_comments AS c
INNER JOIN forum_topics AS t USING ( topic_id )
WHERE c.confirmed = '1' AND t.confirmed = '1'
GROUP BY t.category_id
) AS c2 ON ( c2.category_id = t.category_id AND c2.created_on = c.created_on )

[newbeee]

firstly thank you for your help.

 

i tried it and had an error but from what i have learnt so far i think i was right in that the first first field seleted should have been..  c2.category_id not cat.category_id  ?

 

ok seems to be getting there so excited as this is the last part i need.

 

all i need is to also have in the results the 'category' and 'stickypost' fields from the 'forum_category' table

 

so the results have it in the following order...

 

 

ORDER BY `stickypost` DESC, `created_on` DESC

 

 

i did try to add them myself but it could not see where i was going wrong.  seems like the query is not searching the forum_category table

 

 

 

 

SELECT c2.category_id, t.topic_id, c.created_on, t.stickypost FROM forum_comments AS c INNER JOIN forum_topics AS t USING ( topic_id ) INNER JOIN ( SELECT

t.category_id, MAX(c.created_on) AS created_on FROM forum_comments AS c INNER JOIN forum_topics AS t USING ( topic_id ) WHERE c.confirmed = '1' AND t.confirmed = '1' GROUP BY t.category_id ) AS c2 ON ( c2.category_id = t.category_id AND c2.created_on = c.created_on ) ORDER BY `created_on` DESC

[fenway]

Try this:

 

SELECT
cat.category_id
, cat.category
, cat.stickypost
, t.category_id
, t.topic_id
, c.created_on
FROM
forum_comments AS c
INNER JOIN forum_topics AS t USING ( topic_id )
INNER JOIN forum_category AS cat USING ( category_id )
INNER JOIN
(
SELECT
t.category_id
, MAX(c.created_on) AS created_on
FROM
forum_comments AS c
INNER JOIN forum_topics AS t USING ( topic_id )
WHERE c.confirmed = '1' AND t.confirmed = '1'
GROUP BY t.category_id
) AS c2 ON ( c2.category_id = t.category_id AND c2.created_on = c.created_on )

[newbeee]

the following is now geting one of the entries twice!

 

it should be getting each category once and order by...

 

stickypost, (from the category table, high number first)

then

created_on,  (from the comments table, recent date first)

 

can anyone see whats wrong with the query.

 

 

this is my table and the data.

 

--
-- Table structure for table `forum_category`
--

CREATE TABLE IF NOT EXISTS `forum_category` (
  `category_id` bigint(20) NOT NULL auto_increment,
  `confirmed` text,
  `stickypost` tinyint(4) NOT NULL default '0',
  `category` text NOT NULL,
  `created_by` text NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `theip` text NOT NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `forum_category`
--

INSERT INTO `forum_category` (`category_id`, `confirmed`, `stickypost`, `category`, `created_by`, `created_on`, `theip`) VALUES
(1, '1', 0, 'category about cats', 'Admin', '2008-12-30 01:02:03', ''),
(2, '1', 0, 'category about dogs', 'Admin', '2008-12-30 01:02:03', '');

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

--
-- Table structure for table `forum_comments`
--

CREATE TABLE IF NOT EXISTS `forum_comments` (
  `postcounter` bigint(20) NOT NULL auto_increment,
  `topic_id` bigint(20) NOT NULL default '0',
  `reportedby` text NOT NULL,
  `comment` longtext NOT NULL,
  `alreadyexists` text NOT NULL,
  `username` varchar(65) NOT NULL default '',
  `theip` text NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `confirmed` text,
  `approvedby` text,
  `approveddatetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `editedby` text,
  `editeddatetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `deletedby` text NOT NULL,
  `restoredby` text NOT NULL,
  KEY `postcounter` (`postcounter`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

--
-- Dumping data for table `forum_comments`
--

INSERT INTO `forum_comments` (`postcounter`, `topic_id`, `reportedby`, `comment`, `alreadyexists`, `username`, `theip`, `created_on`, `confirmed`, `approvedby`, `approveddatetime`, `editedby`, `editeddatetime`, `deletedby`, `restoredby`) VALUES
(16, 7, '', 'dog', 'no', 'Jason', '', '2009-01-11 00:00:00', '1', 'Jason', '0000-00-00 00:00:00', 'Jason', '0000-00-00 00:00:00', '', ''),
(17, 1, '', 'cats comment 2', 'no', 'Jason', '', '2009-01-11 14:09:00', '1', 'Jason', '0000-00-00 00:00:00', NULL, '0000-00-00 00:00:00', '', ''),
(18, 1, '', 'cat topic 3', 'no', 'Jason', '', '2009-01-11 00:00:00', '1', 'Jason', '0000-00-00 00:00:00', 'Jason', '0000-00-00 00:00:00', '', ''),
(20, 1, '', 'last dogs', 'no', 'Jason', '', '2009-01-11 00:00:00', '1', 'Jason', '0000-00-00 00:00:00', 'Jason', '0000-00-00 00:00:00', '', ''),
(22, 1, '', 'new vt 8', 'no', 'Jason', '', '2009-01-11 00:00:00', '1', 'Jason', '0000-00-00 00:00:00', 'Jason', '0000-00-00 00:00:00', '', ''),
(23, 7, '', 'cat topic 3 - new to replace the blank entry', 'no', 'Jason', '', '2009-01-11 00:00:00', '1', 'Jason', '0000-00-00 00:00:00', 'Jason', '0000-00-00 00:00:00', '', ''),
(24, 8, '', 'tttttttt', '', 'Jason', '', '2009-01-11 22:11:00', '1', 'Jason', '0000-00-00 00:00:00', NULL, '0000-00-00 00:00:00', '', ''),
(15, 1, '', 'cats', 'no', 'Jason', '', '2009-01-11 00:00:00', '1', 'Jason', '0000-00-00 00:00:00', 'Jason', '0000-00-00 00:00:00', '', '');

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

--
-- Table structure for table `forum_topics`
--

CREATE TABLE IF NOT EXISTS `forum_topics` (
  `topic_id` bigint(20) NOT NULL auto_increment,
  `category_id` bigint(20) NOT NULL default '0',
  `topic` longtext NOT NULL,
  `username` text NOT NULL,
  `theip` text NOT NULL,
  `stickypost` tinyint(4) NOT NULL default '0',
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `confirmed` text,
  `updated_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `views` bigint(20) NOT NULL default '0',
  `lastviewer` text NOT NULL,
  `approvedby` text,
  `approveddatetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `editedby` text,
  `editeddatetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `deletedby` text NOT NULL,
  `restoredby` text NOT NULL,
  KEY `topic_id` (`topic_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `forum_topics`
--

INSERT INTO `forum_topics` (`topic_id`, `category_id`, `topic`, `username`, `theip`, `stickypost`, `created_on`, `confirmed`, `updated_on`, `views`, `lastviewer`, `approvedby`, `approveddatetime`, `editedby`, `editeddatetime`, `deletedby`, `restoredby`) VALUES
(1, 1, 'topic about cats', 'Admin', '', 0, '2009-01-11 14:09:00', '1', '2009-01-11 14:09:13', 13, 'Jason', 'Jason', '0000-00-00 00:00:00', NULL, '0000-00-00 00:00:00', '', ''),
(8, 1, 'cat topic 3', 'Jason', '', 0, '2009-01-11 22:11:00', '1', '2009-01-11 22:10:53', 0, '', 'Jason', '0000-00-00 00:00:00', NULL, '0000-00-00 00:00:00', '', ''),
(7, 2, 'topic about dogs', 'Jason', '', 0, '2009-01-11 14:17:00', '1', '2009-01-11 14:17:08', 0, '', 'Jason', '0000-00-00 00:00:00', NULL, '0000-00-00 00:00:00', '', '');

 

SELECT
cat.category_id
, cat.category
, cat.stickypost
, t.category_id
, t.topic_id
, c.created_on
, c.username
FROM
forum_comments AS c
INNER JOIN forum_topics AS t USING ( topic_id )
INNER JOIN forum_category AS cat USING ( category_id )
INNER JOIN
(
SELECT
t.category_id
, MAX(c.created_on) AS created_on
FROM
forum_comments AS c
INNER JOIN forum_topics AS t USING ( topic_id )
WHERE c.confirmed = '1' AND t.confirmed = '1'
GROUP BY t.category_id
) AS c2 ON ( c2.category_id = t.category_id AND c2.created_on = c.created_on )
ORDER BY `stickypost` DESC, `created_on` DESC

[fenway]

What is the output now?

[newbeee]

category about cats Jason 12 Jan 2009 04:11

category about dogs Jason 11 Jan 2009 06:00

category about dogs Jason 11 Jan 2009 06:00

 

it shows the dogs entry twice in phpmyadmin and on the site

 

and there are only two categories at the moment for testing..

 

and should show just two

 

category about cats Jason 12 Jan 2009 04:11

category about dogs Jason 11 Jan 2009 06:00

[fenway]

I just realized the problem... max(created_on) wasn't unique... try this (tested using the data you provided):

 

select
ca.category_id
, ca.category
, ca.stickypost
, t.topic_id
, co.created_on
, co.username
from
forum_comments as co
inner join
(
SELECT
t.category_id
, MAX(co.created_on) AS created_on
, MAX(co.postcounter) AS postcounter
FROM
forum_comments AS co
INNER JOIN forum_topics AS t USING ( topic_id )
WHERE co.confirmed = '1' AND t.confirmed = '1'
GROUP BY t.category_id
) as sub using ( created_on, postcounter )
inner join forum_category as ca using ( category_id )
inner join forum_topics as t on ( co.topic_id = t.topic_id )