CODEWITHSUNDEEP
phpmysqlcount
mrmunk
mrmunk

Reputation: 69

Counting rows from table B using id from table A php mysql

I must be exhausted because I know I have done this before but I can't remember how I did or or find that snippet. I have two tables "questions" and "answers". The primary "id" from "questions" is also "id" in "answers". I'm trying to count "answers" based on "id" from that table.

     <?php

  $query = "SELECT * FROM questions ORDER BY id DESC";

  $result = mysql_query("$query");
  while($row = mysql_fetch_array($result)){
$id = $row['id'];
    $date = $row['date'];
    $question = $row['question'];
$time = date("U");
$likes = $row['likes'];
    $query2 = "SELECT * FROM answers WHERE id = $id";
    $num_rows = mysql_num_rows($query2);
    print("<span style='font-size: .7em';><a href='qplusone.php?id=$id'>+1</a>
 - Likes:$likes</span> $question - <a href='answer.php?id=$id&pp=$time'>answer it</a>
 or <a href='answers.php?id=$id&pp=$time'>read $num_rows answers</a> 
<span style='font-size: .7em';>($date)</span><br />");
  }

  ?>

Upvotes: 1

Views: 1649

Answers (2)

zz1433
zz1433

Reputation: 3586

Well, first of all you never execute $query2. But even better than returning all the rows and counting them, simply return the number of rows counted by mysql.

$query2 = "SELECT count(*) FROM answers WHERE id = $id";
$result = mysql_query($q);
$row = mysql_fetch_array($result);

$count = $row['count(*)']; // $count holds the number of matching records.

Upvotes: 1

juergen d
juergen d

Reputation: 204894

To get the count of answers per question you can do this

select q.id,
       count(*) as answers
from answer a
left join questions q on q.id = a.id 
group by q.id

Upvotes: 0

Related Questions