Page 1 of 1
how to calculate total comment per user
Posted: Wed Apr 13, 2011 9:14 am
by bella1010
have anyone know how to calculate the total comment post by a user ? which display the retrieve data from comment table. $matric_number, $comment are in my comment table. how to do it? hav anyone know how to do it?
Re: how to calculate total comment per user
Posted: Wed Apr 13, 2011 12:34 pm
by social_experiment
If
$matric_number is the value that is assigned to indicate who made a post, the code could be similar to this
Code: Select all
$countQry = "SELECT COUNT(id) FROM comment_tbl WHERE matric_number_field = '" . mysql_real_escape_string($matric_number) . "' ";
$countSql = mysql_query($countQry);
$array = mysql_fetch_array($countSql);
// this gives you the amount of records for an specific 'matric_number'
$rows = $array[0];
Displaying is relatively easy : select info, pass it to a function (mysql_fetch_array()), then display it. Create some code for this then paste it
Hth
Re: how to calculate total comment per user
Posted: Wed Apr 13, 2011 9:19 pm
by bella1010
thanks for reply. now, i wanna change my concept (change to ranking). from comment table, rank the matric_number in comment table which appear most to less. have anyone help?
Re: how to calculate total comment per user
Posted: Thu Apr 14, 2011 12:37 am
by social_experiment
You have to add this to the end of the query string where you select the data : ORDER BY matric_number DESC
Re: how to calculate total comment per user
Posted: Thu Apr 14, 2011 11:16 am
by bella1010
i have do my coding :
<?php
$host="localhost"; // Host name
$username="aaaaa"; // Mysql username
$password="aaaaa"; // Mysql password
$db_name="aaaaaa"; // Database name
$tbl_name="vrcomment"; // Table name
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
$sql2 = "SELECT COUNT(*) AS num,a_name FROM vrcomment GROUP BY(a_name) ORDER BY num DESC ";
$result2=mysql_query($sql2);
echo "<table border='1' cellpadding='5' cellspacing='0' bgcolor='#00aa00''>"; // bordercolor='#000033'
print"\t<td><font color=white face=arial size=3/>Matric</font></td>\n
<td><font color=white face=arial size=3/>Total comment</font></td>\n";
while($result2){
$row = mysql_fetch_assoc($result2);
echo "<tr table border='1' cellpadding='5' bgcolor='#FFFFFF'>";
echo '<td><font face=arial size=2/>' . $row['a_name'] . '</font></td>';
echo '<td><font face=arial size=2/>' . $row['num'] . '</font></td>';
echo "</tr>";
break;
}
print "</table>\n";
?>
the problem now is, the programme can't stop looping. so, i put 'break' into it,but the result is it only display 1 [a_name] from table. what coding should insert into it, in order to display all [a_name] in table and able stop looping.
Re: how to calculate total comment per user
Posted: Thu Apr 14, 2011 11:46 am
by social_experiment
Code: Select all
<?php
$query = "SELECT a_name FROM vrcomment GROUP BY a_name ORDER BY num DESC";
$sql = mysql_query($query);
while ($array = mysql_fetch_array($sql)) {
echo $array['a_name'];
}
?>
The COUNT() function returns a number of rows. You want to simply use SELECT. This code will display all values in the column 'a_name' and stop when all values for 'a_name' is displayed.