Wilian Brain Wilian Brain - 1 year ago 60
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");
foreach ($gquest->fetchAll() as $rowL)
$cat = $DB_con->prepare("SELECT * FROM `comment` WHERE `questid`=".$rowL['id']."");
$cominf = $cat->fetch(PDO::FETCH_ASSOC);
$countquest = $cat->rowCount();

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


Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download