Hi guys.
I am hoping one of you can help me create a SQL query to update this function and improve on it.
We have a ticketing system with different ticket tables, which contain a "reviewer" field which shows who opened the ticket first.
On our Stats page we want to show how many tickets each person opened (reviewer), and how many replies they have made in total, and then order them in order of the amount, and if 0, then order it by account name.
With PHP, I could only figure out how to order it by account name, and apparantly a "JOIN" SQL query would be more appropriate to do this.
Can someone help me do that? I just confused myself reading the JOIN manual.
echo " <table>\n";
echo " <tr>\n";
echo " <td><strong>Account:</strong></td>\n";
echo " <td><strong>Reviewer:</strong></td>\n";
echo " <td><strong>Replies:</strong></td>\n";
echo " </tr>\n\n";
$replies=mysql_query2("SELECT * FROM user ORDER BY account");
while ($replydetail=mysql_fetch_array($replies))
{
// total reviewer per user
$table1_reviewer=mysql_num_rows(mysql_query2("SELECT reviewer FROM table1 WHERE reviewer='".mysql_real_escape_string($replydetail['account'])."'"));
$table2_reviewer=mysql_num_rows(mysql_query2("SELECT reviewer FROM table2 WHERE reviewer='".mysql_real_escape_string($replydetail['account'])."'"));
$table3_reviewer=mysql_num_rows(mysql_query2("SELECT reviewer FROM table3 WHERE reviewer='".mysql_real_escape_string($replydetail['account'])."'"));
$num_reviewer = $table1_reviewer + $table2_reviewer + $table3_reviewer;
// total number of replies per user
$table1_replies=mysql_num_rows(mysql_query2("SELECT name, user FROM table1reply WHERE name='".mysql_real_escape_string($replydetail['account'])."' AND user=1"));
$table2_replies=mysql_num_rows(mysql_query2("SELECT name, user FROM table2reply WHERE name='".mysql_real_escape_string($replydetail['account'])."' AND user=1"));
$table3_replies=mysql_num_rows(mysql_query2("SELECT name, user FROM table3reply WHERE name='".mysql_real_escape_string($replydetail['account'])."' AND user=1"));
$num_replies = $table1_replies + $table2_replies + $table3_replies;
echo " <tr>\n";
echo " <td><strong>".$replydetail['account']."</strong>".($replydetail['deleted']==1 ? ' <span style="color: red;">*</span>' : "")."</td>\n";
echo " <td>".$num_reviewer."</td>\n";
echo " <td>".$num_replies."</td>\n";
echo " </tr>\n\n";
}
echo " </table><br />\n\n";
Thank you!
