phpSensei Posted August 1, 2007 Share Posted August 1, 2007 Okay, ill break this down for you: I have a news system on my front page which uses the following table: id_news title_news message_news I also have a comment system which goes like this: Which everytime you click on post comment, it gets the id of the message which you want to post a comment in and inserts the id in a table row called "id_int". id_int = id_news Now I want to show my users how comment each news has, but it doesnt work... $Recordcount=mysql_query("SELECT * FROM comment INNER JOIN news ON id_int=id_news WHERE // here is where I am stuck $RecordResult=mysql_num_rows($Recordcount); What should I put in the WHERE statement? Quote Link to comment Share on other sites More sharing options...
teng84 Posted August 1, 2007 Share Posted August 1, 2007 explain more we dont understand Quote Link to comment Share on other sites More sharing options...
phpSensei Posted August 1, 2007 Author Share Posted August 1, 2007 K, there is a set of messages in my front page: Tables: id_news // The Id of the News title_news // The title message_news //My Message date_news // And the Date (obviously) [code]<?php include("config/connect1.php"); $string= "SELECT * FROM news"; $query= mysql_query($string); while ($news = mysql_fetch_array($query)){ $idcount= $news['news_id']; $Recordcount=mysql_query("SELECT count(subject_cmt) FROM comment INNER JOIN news WHERE id_int=1"); $RecordResult=mysql_fetch_row($Recordcount); echo' <table width="100%" height="191" border="0" cellpadding="0" cellspacing="2"> <tr> <td height="16" bgcolor="#7CADC2"><div align="left" class="style30"> <img src="files/images/spacer.gif" width="1" height="1"><img src="files/images/spacer.gif" width="1" height="1"><img src="files/images/spacer.gif" width="1" height="1">' . $news['title_news'] . ' <span class="style17">- Posted by: Adminstrator on ' . $news['date_news'] . '</span></div></td> </tr> <tr> <td height="134" align="left" valign="top"><div align="left"> <span class="style29"><br>' . $news['message_news'] . ' <br> </div></td> </tr> <tr> <td height="19" valign="top"><hr size="1" noshade></td> </tr> <tr> <td height="12" valign="top" class="style17"><div align="right" class="style200"> <a href="#">Suscribe</a> | <a href="?page=viewcomments&uid=' . $news['id_news'] . '">Comments</a> (' . $RecordResult['subject_cmt']. ')</div></td> </tr> </table>'; } ?> So this is a simple code... Every news is looped, and there is a link after each news that says comments. When you click comment, the URL is this <a href = ?page=viewcomment // The switch statement for viewing a comment &uid=anything... // This is the id_news Now the id is passed from the url to our comment page, and from there on we have a post comment link which goes like this: <a href =?page=postcomment // THe switch statement for posting the comment &uid=... //the id we got from the URL earlier Now when you are done posting a comment, the uid goes in the comment table: id_cmt // The id of the comment id_int // The UID is placed in here title_cmt ... .. .etc Now I want to show the number of comments within each news like this: $Recordcount=mysql_query("SELECT count(subject_cmt) FROM comment INNER JOIN news WHERE id_int=1"); $RecordResult=mysql_fetch_row($Recordcount); [/code] It Doesnt work. Quote Link to comment Share on other sites More sharing options...
teng84 Posted August 2, 2007 Share Posted August 2, 2007 look the way you join is very wrong i dont know what yu wnat to join bu let me tell you this joining will condiotn like you are trying to join two tables having the same field records or data like field1=field2 i have here some stuff i know its not the exact but i think your idea will fall on to this $Recordcount=mysql_query("SELECT count(subject_cmt) as total_count, subject_cmt FROM comment group by subject_cmt order by subject_cmt"); now this will output something like subject_cmt = teng total_count = number note no join because i dont know the relationship of your tbl Quote Link to comment Share on other sites More sharing options...
phpSensei Posted August 2, 2007 Author Share Posted August 2, 2007 I want the number of comments posted for the news... News " Welcome to my site, just launched it" # of comments ( 2 ) I cant just count the number of comments, I need the number of comments for the certan news id. Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 2, 2007 Share Posted August 2, 2007 SELECT count(subject_cmt) FROM comment INNER JOIN news WHERE id_int=1 Why are you joining news? What column are you joining news on? Quote Link to comment Share on other sites More sharing options...
phpSensei Posted August 2, 2007 Author Share Posted August 2, 2007 Oops, sorry, that was my old code. INNER JOIN news ON id_news=id_int WHERE id_news = /// This is where I am stuck Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 2, 2007 Share Posted August 2, 2007 What field(s) are you getting from the news table in your COUNT query that you need to join it? Why not just use one table? SELECT COUNT(comments) FROM news_comments WHERE related_news_article_id = 1 Quote Link to comment Share on other sites More sharing options...
phpSensei Posted August 2, 2007 Author Share Posted August 2, 2007 I need to join the id of the news with the id_int of the comment table (which holds the id of the news) News Table: id title suject ..etc Comment id id_int title subject ..etc Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted August 2, 2007 Share Posted August 2, 2007 If you're relating the comments to the original news story by the id of the news story (id_int?) then you don't need to get news_table:id. Quote Link to comment Share on other sites More sharing options...
teng84 Posted August 2, 2007 Share Posted August 2, 2007 is this what you mean $Recordcount=mysql_query("SELECT n.*,c.* FROM comment c INNER JOIN news n ON n.id=c.id_int"; Quote Link to comment Share on other sites More sharing options...
phpSensei Posted August 2, 2007 Author Share Posted August 2, 2007 KK listen, or read... I have the frontpage, just imagine. You see three news posted in the frontpage, just imagine lol. The id of the first news is "1", and you go post a comment, then you are directed to the post comment page. In the post comment page, you see this in the URL of the page "?uid=1", so every news you post a comment to, the "?uid= ' The id of the news' "... Hope we understand so far. Now when you are done posting, this code is generated $id_of_the_news=$_GET['uid']; mysql_query("insert into comment_table (holder_of_id_of_the_news) VALUES ('$id'); So now when you want to view the comments of the news, you click comments and it sends you to view the comments.. This code Generates $id_of_the_news=$_GET['uid']; //This is passed from the url $string="SELECT * FROM comment INNER JOIN news ON holder_of_id_of_news = 'id_of_the_news' WHERE = '$id_of_the_news' " $query=mysql_query($string); while($row=mysql_fetch_array($query)){ ECHO ALL THE TABLES OF THE COMMENT } ?> Now I want to count the number of comments that are assigned to the news depending on the id of the news.... DOnt want to say your stupid by typing like this, please take no offense. Quote Link to comment Share on other sites More sharing options...
teng84 Posted August 2, 2007 Share Posted August 2, 2007 'SELECT COUNT(id) AS ID, news.title FROM Comment inner join News on News.id = Comment.id_int goup by id_int order by ID' try it will count the rows from comment and display thetitle of the news so it should output like title of news and the ID which is the number of comment Quote Link to comment Share on other sites More sharing options...
phpSensei Posted August 2, 2007 Author Share Posted August 2, 2007 thanks, Can you make it so it only counts the number of comments, and not show the title... Quote Link to comment Share on other sites More sharing options...
teng84 Posted August 2, 2007 Share Posted August 2, 2007 simply 'SELECT COUNT(id) AS ID FROM Comment inner join News on News.id = Comment.id_int group by id_int order by ID' it will only ouput the number of comment per news woas!!!!! hope its done lol Quote Link to comment Share on other sites More sharing options...
phpSensei Posted August 2, 2007 Author Share Posted August 2, 2007 KK, THANKYOU!!! Is this okay....I switched it to my real table names and rows 'SELECT COUNT(id_int) AS id_news FROM comment inner join news on news.id = comment.id_int group by id_int order by ID'; Quote Link to comment Share on other sites More sharing options...
teng84 Posted August 2, 2007 Share Posted August 2, 2007 yeah try the query and mark this solved if it is hoooooo at last lol Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.