Wilian Brain Wilian Brain - 1 month ago 7
PHP Question

get data between two table

i have to table sql table1 for question and table2 for comment

i want to get post has the largest number of comments

how can to this between two table ??

table question like this

id title
----- -------
1 title1
2 title2
3 title3
4 title4


table comment like this

id content questid
----- ------- --------
1 test 1
2 test 3
2 test 3
2 test 3


my code

$gquest = $DB_con->prepare("SELECT * FROM `question` ORDER BY id DESC");
$gquest->execute();
foreach ($gquest->fetchAll() as $rowL)
{
$cat = $DB_con->prepare("SELECT * FROM `comment` WHERE `questid`=".$rowL['id']."");
$cat->execute();
$cominf = $cat->fetch(PDO::FETCH_ASSOC);
$countquest = $cat->rowCount();


$gqt = $DB_con->prepare("SELECT * FROM `question` ORDER BY ".$cominf['id']." DESC");
$gqt->execute();
$cfr = $gqt->fetch(PDO::FETCH_ASSOC);


}

Answer

You can get what you're looking for from a single query by joining the two tables on the question ID and then COUNT() the number of comment occurrences for each question. Here is an example of what it could look like:

$gquest  = $DB_con->prepare("
     SELECT q.*, COUNT(c.questid) AS num_comments
     FROM question q
     JOIN comment c
       ON q.id = c.questid
     GROUP BY q.id
     ORDER BY num_comments DESC
     LIMIT 1;
");

Here's an SQL fiddle

Comments