NineChime forum

I take it you want to print more than one result. 

To do this, you need to do a loop with mysql_fetch_array(), instead of mysql_result(), to get each of the ten rows, one at a time.

Gimme a day to give you something that works better (it's really late/early now).

Waccoon wrote:

Code:

<!-- Pic rank -->
<?
$pictureranking = mysql_query ("SELECT usrname, piccount FROM op_oekaki WHERE piccount > 0 ORDER BY piccount DESC LIMIT 0, 10");
$top_rank_num = mysql_num_rows ($pictureranking);
?>
<table bgcolor=<?=$cPostbgColor?> style="font-size:12;" width="100%">
    <tr>
        <th align="left" valign="top" width="50%">
            Name
        </th>
        <th align="right" valign="top" width="50%">
            Pictures
        </th>
    </tr>
<? for ($n = 0; $n < $top_rank_num; $n++) {
    $top_rank = mysql_fetch_array ($pictureranking); ?>
    <tr>
        <td align="left" valign="top" width="50%">
            <? echo $top_rank['usrname']; ?><br />
        </td>

        <td align="right" valign="top" width="50%">
            <? echo $top_rank['piccount']; ?><br />
        </td>
    </tr>
<? } ?>
</table>



<!-- Comment rank -->
<?
$commentranking = mysql_query ("SELECT usrname, commcount FROM op_oekaki WHERE commcount > 0 ORDER BY commcount DESC LIMIT 0, 10");
$top_rank_num = mysql_num_rows ($commentranking);
?>
<table bgcolor=<?=$cPostbgColor?> style="font-size:12;" width="100%">
    <tr>
        <th align="left" valign="top" width="50%">
            Name
        </th>
        <th align="right" valign="top" width="50%">
            Comments
        </th>
    </tr>
<? for ($n = 0; $n < $top_rank_num; $n++) {
    $top_rank = mysql_fetch_array ($commentranking); ?>
    <tr>
        <td align="left" valign="top" width="50%">
            <? echo $top_rank['usrname']; ?><br />
        </td>

        <td align="right" valign="top" width="50%">
            <? echo $top_rank['commcount']; ?><br />
        </td>
    </tr>
<? } ?>
</table>

This code also won't print members that have zero pictures or comments, just in case.

Thanks for the help. However, I want it to only display the latest number of pictures or comments from these top posters and commenters, not the most number of pictures or comments ever. Since I have more than 300 pictures stored, the number of latest comments and pictures from that artist will drop. Here is this piece of code from profile.php for example:

Code:

$result2 = mysql_query("SELECT * FROM ".$OekakiPoteto_Prefix."oekakidta WHERE usrname='$user' ORDER BY postdate desc");
$rownum = mysql_num_rows($result2);
$i = 0;
$result = mysql_query("SELECT * FROM ".$OekakiPoteto_MemberPrefix."oekaki WHERE usrname='$user'");
$row = mysql_fetch_array($result);

Waccon, how would I get it to display only the latest number of comments and pictures at the most for a certain user?

Waccoon wrote:

Ah, so you want to show the pictures/comments that are actually stored at any point in time.  Will this include links to a person's latest picture, or just a count of how many pictures exist?

I only want it to count the most number of pictures and comments that currently exist on the user's account, not the most number of pictures and comments from a user, ever.

Waccoon wrote:

Counts are easy, links are trickier and have lots of gottchas that wouldn't be all that great for the notice.

To get the count, you have to get the rank first (already doing that), and then count up everything in the picture and comment tables.

How would I do that?

Just so you know, this isn't totally accurate, as people are still ranked based on how many pictures and comments they have made in total, but what is printed is a count of how many pictures and comments they currently have in the database.  The reason for this is because it isn't practical to count up how many pictures and comments each member has to get the rank.  It would take way too much time per pageview.  This is a good approximation for ranking, though, and in the end, it does provide the most accurate counts, as opposed to simply printing the picture and comment count stored in each member's profile.

But now, it's displaying "8, 0, 0, 30, 3, 0, 0, 2". How do I get it to display the ranking of the most number of pictures and comments that they currently have in the database for a certain username? For example, 30, 23, 8, 3, 2, etc.

How would I get it to work with the $pictureranking, $commentranking, $top_rank and $top_rank_num figures? Would I have to create a new field to store the number of pictures and comments in the database for a certain username in the op_oekakidta table?

For example, in the picture field, I have the following:

<table bgcolor=<?=$cPostbgColor?> style="font-size:12;" width=100% height=156>
    <? for ($n = 0; $n < $top_rank_num; $n++) {
        $top_rank = mysql_fetch_array ($pictureranking); ?>
        <? $result_mrp = mysql_query ("SELECT count(PIC_ID) FROM op_oekakidta WHERE usrname='{$top_rank['usrname']}'"); ?>
        <? $max_rank_pics = mysql_result ($result_mrp, 0); ?>

        <tr>
            <td align="left" valign="top" width="50%">
                <? echo $top_rank['usrname']; ?><br />
            </td>
            <td align="right" valign="top" width="50%">
                <? echo $max_rank_pics; ?><br />
            </td>
        </tr>
    <? } ?>
</table>

And in the comment field, I have the following as well as it hasn't changed much:

<table bgcolor=<?=$cPostbgColor?> style="font-size:12;" width=100% height=156>
    <? for ($n = 0; $n < $top_rank_num; $n++) {
        $top_rank = mysql_fetch_array ($commentranking); ?>
        <tr>
            <td align="left" valign="top" width="50%">
                <? echo $top_rank['usrname']; ?><br />
            </td>
            <td align="right" valign="top" width="50%">
                <? echo $top_rank['commcount']; ?><br />
            </td>
        </tr>
    <? } ?>
</table>

Last edited by rainbow (10-05-2006 11:17:37)

Sigh.  Without a caching engine, I can't think of a way to do this accurately or efficiently, which is why you're getting so many zeros in your results.

The problem is that what you want can't be done efficiently with the board strictly with code in the notice.  To fix this requires a few things:

- Adding new entries to the op_oekaki table.  This ranks people correctly based on what's in the database at the moment, rather than total pictures and comments.  Integer values named "pic_rec" (picture record) and "comm_rec" (comment record) would do fine.

- Modify the boad so that any time a picture is removed, all the comments are counted and the appropriate "comm_rec" is reduced, and then the proper "pic_rec" is updated.  Comment deletes from admins and artists must be taken into account, but not comment edits.

- Obviously, count up any new pictures/comments added.

- Make a script that creates an array, and "bins" counts.  Such a script may cause a timeout error if not done correctly, due to the sheer number of comments that must be counted.

Such code is very prone to error, especially with the way Wax is constructed, and things will get out of sync often.  The "total" picture count already in the database is already faulty and is regularly set to values less than zero.  OP worked this way, and I never fixed it because it's just too much trouble.

You could just do a recount every time the board changes, but the script that does the counting would have to account for timeouts, which requires yet more flags to be stored in the database.

Sorry if I'm not too helpful as far as code is concerned, but I'm more interested in helping people with technical problems, rather than with making mods of the board.  This is especially true since I'm currently updating Wax to behave more like Wacintaki (fixing the massive sorting problems).  If I was using a true template engine, I wouldn't even have two versions of the oekaki in the first place.