Compare results from two identical selects

I'm running this query to see how many questions there are in each category:

$query = "SELECT category_table_id, COUNT(question) AS nbr FROM question GROUP BY category_table_id ORDER BY nbr";

Then I add and delete rows in the question table manually. And then I run the query again to compare. How can I then g开发者_运维百科et the number of added or deleted rows in the categories that has a new number of rows?

Many thanks

Since the adding and removing of questions is happening on the same script, the easiest is to store the first results in an array in the form ($category_table_id => $num_questions) and in the second query, subtract the results of the first query.

Translating this to code:

mysql_connect(...);

$read_category_counts_query = "SELECT category_table_id, COUNT(question) AS nbr FROM question GROUP BY category_table_id ORDER BY nbr";

function read_category_counts() {
    $result = mysql_query($read_category_counts_query);

    $counts = array();
    while ($row = mysql_fetch_assoc($result)) {
        $counts[$row['category_table_id']] = $row ['nbr'];
    }

    mysql_free_result($result);

    return $counts;
}

function compare_category_counts($before, $after) {
    $difference = array();

    foreach ($before as $key => $val) {
        if (!isset($after[$key]) $after[$key] = 0;
    }

    foreach ($after as $key => $val) {
        if (!isset($before[$key]) $before[$key] = 0;
        $diff_val = $val - $before[$key];
        if ($diff_val != 0) {
            $difference[$key] = $diff_val;
        }
    }

    return $difference;
}

$initial_count = read_category_counts();

// do something ...

$current_count = read_category_counts();
$differences = compare_category_counts($initial_count, $current_count);

Store your first results somewhere (like back in the DB), do your manual DB editing, requery, compare old (stored) and new (queried).

If I understand correctly your question, you want to have some sort of logging of changes in the table category. To have a list of inserted and deleted record you could put some flags in your table to mark delete or insert. You could also use a datetime field to have the exact moment of the changes for future uses. (a daily report for example)

If I get this clear, you want the amount of newly added or deleted rows? If this number is needed on the same page, you can save the amount in a temporary variable, then perform the add and delete actions, and compare the new amount with the old one, discovering how many have been added or deleted.

If you just want to show the recently added question, you should include a added column, containing the timestamp at which the question was added, so you can query for all new questions since a given moment in time.