0

I cant figure out how to get comments underneath mysql db entries..

For example There is one article witch id is 1 and then there are some (for example 3) comments in that article (ID - 1) [ARTICLE_ID][CATEGORY]
And the comments are like: [ID][ARTICLE_ID] ..

so it is like

| ID | SUB_ID | TITLE....
|   1  | 123     | THIS IS A ARTICLE
|   2  | 1         | COMMENT TO ARTICLE 1
|   3  | 1         | COMMENT 2 to article 1
|   4  | 1         | Comment 3 to Article 1
AND CEN_TYPE for all of thesese IDs, 1,2,3,4 is forumentry

I've tryed like this:

  1. SELECT everthing from CNT_Entries_A WHERE CEN_TYPE is like forumentry - so everthing is fine here, but it doesnt put comments underneath (logically)
  2. Putting all ID's AND SUB_ID's in to a array's
  3. making foreach cycle, inside that checking if current ID is equal to arrays value if it is, check if that ID is in array - if is not then printing it out and putting it to an array..
  4. SAME THING to SUB ID
  • Is there a reason for putting articles and comments into the same table? – VolkerK Nov 23 '12 at 08:58
  • @VolkerK i wouldnt make articles, comments like that, but the table was already there.. so i have to get information out. –  Nov 23 '12 at 09:02
  • The condition of the first query is not refelected by the table layout you've posted. CEN_TYPE is like forumentry? – VolkerK Nov 23 '12 at 09:10
  • @VolkerK CEN_TYPE for all of thesese IDs, 1,2,3,4 is `forumentry` –  Nov 23 '12 at 09:15
  • can u put the expected result to make it easier to understand from the given scenarion n rough schema of tables? – Angelin Nadar Nov 23 '12 at 09:24
  • the sub_id for 2,3,4 will be actually the article id.Similarly what will the sub_id be representing for row 1 which is an article? – Angelin Nadar Nov 23 '12 at 09:31
  • @AngelinNadar it is Category id.. –  Nov 23 '12 at 09:36

2 Answers2

1

I highly recommend to split the data into two data tables:

Articles
article_id | text

Comments
comment_id | article_id | comment

Then You won't have problem to do a simple SELECT + LEFT JOIN query...

shadyyx
  • 15,825
  • 6
  • 60
  • 95
0

This will give result as article and their comments so that u can then loop over:

find the implementation here

SELECT c1 . * , c2 . * 
FROM comments AS c1
JOIN comments AS c2 ON ( c1.id = c2.sub_id
AND c1.id != c2.id ) 
WHERE c1.cen_type =  'forumentry'


+----+--------+-------------------+------------+----+--------+----------------------+------------+
| id | sub_id | title             | cen_type   | id | sub_id | title                | cen_type   |
+----+--------+-------------------+------------+----+--------+----------------------+------------+
|  1 |      1 | THIS IS A ARTICLE | forumentry |  2 |      1 | COMMENT TO ARTICLE 1 | forumentry |
|  1 |      1 | THIS IS A ARTICLE | forumentry |  3 |      1 | COMMENT 2 to article | forumentry |
|  1 |      1 | THIS IS A ARTICLE | forumentry |  4 |      1 | Comment 3 to Article | forumentry |
+----+--------+-------------------+------------+----+--------+----------------------+------------+
Angelin Nadar
  • 8,944
  • 10
  • 43
  • 53