Help on the page with the id article the number of comments I bring out such a query:

 $result_c = mysql_query ("SELECT COUNT(*) FROM comments WHERE post='$id'",$db); $comm_sum = mysql_fetch_array($result_c); echo "<div class='col_comm'>Комментариев: $comm_sum[0]</div>";

But how to do this if the id not so I need to display the number of comments for each article. What kind of request needs to be done or something else? Help.

  • $result = mysql_query("SELECT id,title,description,date,author,mini_img,view,(SELECT COUNT(*) FROM comments WHERE post='$id')AS count_comment FROM news ORDER BY id DESC LIMIT $start, $num",$db); In fact, I need to change something here - paloshmaxpro
  • hmmm ... <br /> is this news output? there you make a request in the news and in the process of output, more precisely, before the very "beautifully output all this crap" make a selection of the result)), that is, you also get data from the database, that is, in the cycle you already have a variable with the ID substitute it)) or is it something I don’t understand ?? <br /> Let's make it easier, what is the structure of the database with news and the structure of the database with comments - DemoriaN
  • there is a separate table with the output of all information (news) and comments (comments) - paloshmaxpro
  • I can’t figure out how to do it and the articles are still complicated for me a little easier if you comment on the code $ result = mysql_query ("SELECT id, title, description, date, author, mini_img, view, (SELECT COUNT (*) FROM comments JOIN news ON news.id = comments.id) AS count_comment FROM news ORDER BY id DESC LIMIT $ start, $ num ", $ db); right? but it doesn't work for me - paloshmaxpro
  • See the example below, maybe it will be easier) - Barton

3 answers 3

As I understand it you need to display a list of articles and the number of comments to them. Then it is done like this:

  SELECT posts.*,COUNT(comm.id) AS `comments_count` FROM `news` AS `posts` LEFT JOIN `comments` AS `comm` ON (news.id=comments.post)

    First, read about the primary key in mysql so that there are no such questions, generally on a subject:
    In any case, you need some unique value, unfortunately while the DBMS do not have extrasensory abilities and cannot choose for what is not, but otherwise it will not get ...

    In general, the output - you have the correct query, implemented by the primary key int autoincrement - profit ...

    • Please throw an article where you can read, otherwise I found only how to make queries tabular like CREATE TABLE example_autoincrement (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, data VARCHAR (100) - paloshmaxpro
    • So you still do not have a unique key? well then on the fingers - the table field is unique for any record that always exists and can never be repeated, now you can simply make unique Kei and read about JOINS and subqueries and you can solve your problem yourself, by the way I can assume that about indexes you don't know anything either :) :) You can also read about them ... - Zowie
    • And you can make it easier. I just wonder how the problem is implemented, and time is short. - paloshmaxpro
    • Of course, I can write you a request and everything will be cool and so on. But I will not do this, I’ve sent you on the right path, choose it yourself or not, wait a little, maybe someone will be “kinder” me and just write you the necessary query :) - Zowie
    • Thank you sarcastically, but you're right, I'll try better myself, and if it doesn't work out then you will help, right? - paloshmaxpro

    If I understand correctly, you need to display the number of comments for each news / article. if so then...

      цикл... $comnum = mysql_num_rows(mysql_query("SELECT * FROM `news_comments` WHERE `newsid` = '".$res['id']."'"));

    then where you need to display the type Comments: <? echo $comnum; ?> <? echo $comnum; ?>

    Accordingly, $ comnum = lines from comments if a newsid in the comment table matches the already selected (cycle) id of the news itself.

    UPD

     //запрос в базу $q=mysql_query("SELECT * FROM news ORDER BY id LIMIT {$n->start()},$pnumber "); //выводим результаты while (false!==($res=mysql_fetch_assoc($q))) { //ПОДСТАВЛЯТЬ СТРОКУ НАДО В ЦИКЛЕ! ЕСЛИ ЗА ЦИКЛОМ, ОНА ТЕБЕ ТОЛЬКО 1 ЗНАЧЕНИЕ ВЫВЕДЕТ! $comnum = mysql_num_rows(mysql_query("SELECT * FROM `news_comments` WHERE `newsid` = '".$res['id']."'")); ?>

    Here is your conclusion blah blah blah blah blah

      <? // закрываем цикл, когда вывели все)) } ?>
    • No, the TC wanted to explain comments to posts without a unique identifier by inexplicable miracle code :) - Zowie
    • I am not a psychic) ​​I'm just learning)) I assumed that it was necessary) I suggested a solution to the problem) <br /> Perhaps, by “no ID” he meant that this is a general sheet with articles. - DemoriaN
    • Everything can be :) - Zowie
    • all right, this is a general list of articles - paloshmaxpro
    • well i subquery just add to the query something like <br> SELECT COUNT (*) AS comments FROM comments WHERE id = "id" - Zowie