I am creating a table of all the users listing their username and pending questions with total number of allotted question.

I have 2 tables first one contain the username,initial question number and final question number columns and second table contain the timestamp username and submitted question number

first db table

second db table

so i want to output it as a username,total question allotted, pending question

Output i want on webpage

total pending question must be calculated as by (total questions in series - total submitted questions)

CodePudding user response：

You can done this using next query:

SELECT 
    first_table.username, 
    (last_que-first_que) total_questions_allotted,
    (last_que-first_que-answers) pending_questions
FROM first_table 
JOIN (
    SELECT username, COUNT(*) answers FROM second_table GROUP BY username
) second_table_groupped USING(username);

or using next PHP code:

<?php
$query = 'SELECT 
    first_table.username, 
    (last_que-first_que) total_questions_allotted,
    (last_que-first_que-answers) pending_questions
FROM first_table 
JOIN (
    SELECT username, COUNT(*) answers FROM second_table GROUP BY username
) second_table_groupped USING(username)';


$result = $mysqli->query($query);
$data = $result->fetch_all(MYSQLI_ASSOC);

print_r($data);

run PHP online