[resolved] Individual member counts, how is this possible? Options

[horrorshow]

I've searched and searched but couldn't find a topic on this Or maybe it's really obvious and everyone knows how to do this except me

What I want to show, is a list of my owned fanlistings and the number of approved and pending members for each. I know this was possible in PHPFanbase, so is it possible for Enth? This was the tutorial for phpfanbase, in case I didn't explain it clearly enough

[Luinthoron]

Do you mean this?

[horrorshow]

No; that's just customizing my owned fanlistings page. What I'd like to show is a page with just fanlisting member counts, like what you can do with phpfanbase like this.

And I can't think of a way to do that

[Luinthoron]

You probably can't have this separately from the owned listings page, unless you're willing to update the owned listings page manually and use the owned listings template for a page like this.

However, a page exactly like this (although without the collective stats, unless you use the Collective Stats Addon) can be made with the owned listings template.

[Julie]

As far as I know, there is not an existing snippet or tutorial for this feature for Enth 3, unless like Luinthoron wrote, you want to use the owned fanlistings template. I wanted the members count for my collective in addition to a more detailed owned fanlistings page, so I wrote the code for it and you are welcome to use it. This code works with or without the collective stats add-on and even works if your fanlistings don't all use the same database.

In action at my site: http://new-place.org/members.php

Paste the following code where ever you'd like the table to show up:

CODE

<?php
include 'config.php';
?>

<table>
<!-- customize column headings -->
<tr>
<td>fanlisting</td>
<td>listed</td>
<td>pending</td></tr>
<!-- stop -->

<?php
$get_owned = mysql_query("SELECT * FROM $db_owned WHERE status='2' ORDER BY subject ASC");
while ($row = mysql_fetch_array($get_owned)) {
    $title = $row['title'];
    $subject = $row['subject'];
    $url = $row['url'];
    
    if ($row['dbserver'] == $db_server) {
      $getapprovedcount = mysql_query("SELECT COUNT(*) as approvedcount FROM $row[dbtable] WHERE pending='0'");
      $approvedcount = mysql_result($getapprovedcount, 0, "approvedcount");
      $getpendingcount = mysql_query("SELECT COUNT(*) as pendingcount FROM $row[dbtable] WHERE pending='1'");
      $pendingcount = mysql_result($getpendingcount, 0, "pendingcount");
    } else {
      $newlink = mysql_connect($row['dbserver'], $row['dbuser'], $row['dbpassword'], true);
      mysql_select_db($row['dbdatabase'], $newlink);
      $getapprovedcount = mysql_query("SELECT COUNT(*) as approvedcount FROM $row[dbtable] WHERE pending='0'");
      $approvedcount = mysql_result($getapprovedcount, 0, "approvedcount");
      $getpendingcount = mysql_query("SELECT COUNT(*) as pendingcount FROM $row[dbtable] WHERE pending='1'");
      $pendingcount = mysql_result($getpendingcount, 0, "pendingcount");
        mysql_close($newlink);
        include 'config.php';
    }
?>

<!-- customize the output -->
<tr>
<td><a href="<?=$url?>" target="_blank"><?=$title?>: <?=$subject?></a></td>
<td class="numberrow"><?=$approvedcount?></td>
<td><?=$pendingcount?></td>
</tr>
<!-- stop -->

<?php
}
?>
</table>

The code looks like my site as is but I commented to places to edit the output if you want a different look. If you need help getting it to look a certain way, just ask!

(Techie note to anyone who might understand: This was my first attempt at working with multiple mysql connections, and I couldn't stop 'mysql_close($newlink);' from closing all open connections instead of just $newlink. I think this is because the main connection Enth makes in the config file doesn't have a link identifier, but if anyone knows a better way please tell! I just solved it by re-including config.php. )

[horrorshow]

That's exactly what I was looking for, thank you so much!!

[Birgit]

Ah, great, Julie - gotta try this out once I get home this afternoon!

[ancalimesh]

You saved me again Julie. I was nearly trying to just put the config file of the fl and then the stats and so on.
But, is there also a way to have complete approved and pending?

[Monica]

This is great Julie, I used your code and I love it

[morbid romantic]

*cries* I would love to use this, but I have my stuff on two domains (one flinx install, two domains) so I always get errors.

[Becky]

I'm sorry to reply here I just wondered if maybe somebody could help me out with some errors I'm getting?

Warning: mysql_query() [function.mysql-query]: Access denied for user 'nobody'@'localhost' (using password: NO) in /home/beckykin/public_html/counts.php on line 62

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /home/beckykin/public_html/counts.php on line 62

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/beckykin/public_html/counts.php on line 63

:/

[shirley_temple]

Please post your counts.php including the config.php

[Becky]

counts.php page:

<?php
include '/home/beckykin/public_html/enth3/config.php';
?>

<table>
<!-- customize column headings -->
<tr>
<td>fanlisting</td>
<td>listed</td>
<td>pending</td></tr>
<!-- stop -->

<?php
$get_owned = mysql_query("SELECT * FROM $db_owned WHERE status='2' ORDER BY subject ASC");
while ($row = mysql_fetch_array($get_owned)) {
$title = $row['title'];
$subject = $row['subject'];
$url = $row['url'];

if ($row['dbserver'] == $db_server) {
$getapprovedcount = mysql_query("SELECT COUNT(*) as approvedcount FROM $row[dbtable] WHERE pending='0'");
$approvedcount = mysql_result($getapprovedcount, 0, "approvedcount");
$getpendingcount = mysql_query("SELECT COUNT(*) as pendingcount FROM $row[dbtable] WHERE pending='1'");
$pendingcount = mysql_result($getpendingcount, 0, "pendingcount");
} else {
$newlink = mysql_connect($row['dbserver'], $row['dbuser'], $row['dbpassword'], true);
mysql_select_db($row['dbdatabase'], $newlink);
$getapprovedcount = mysql_query("SELECT COUNT(*) as approvedcount FROM $row[dbtable] WHERE pending='0'");
$approvedcount = mysql_result($getapprovedcount, 0, "approvedcount");
$getpendingcount = mysql_query("SELECT COUNT(*) as pendingcount FROM $row[dbtable] WHERE pending='1'");
$pendingcount = mysql_result($getpendingcount, 0, "pendingcount");
mysql_close($newlink);
include 'config.php';
}
?>

<!-- customize the output -->
<tr>
<td><a href="<?=$url?>" target="_blank"><?=$title?>: <?=$subject?></a></td>
<td class="numberrow"><?=$approvedcount?></td>
<td><?=$pendingcount?></td>
</tr>
<!-- stop -->

<?php
}
?>
</table>

config file:

<?php
/******************************************************************************
DATABASE VARIABLES
These are the variables for your listing collective.
$db_server - database server, usually localhost
$db_user - username for your database
$db_password - password for your database
$db_database - actual database name
******************************************************************************/
$db_server = 'localhost';
$db_user = 'beckykin';
$db_password = '-----';
$db_database = 'beckykin_enth';


/******************************************************************************
DATABASE TABLE VARIABLES
These are the variables for your listing collective's tables. You can leave
these as they are unless you already use one of the default values as a
table name in your database previously.
$db_settings - table for Enth3 settings
$db_category - table for the categories
$db_joined - table for your joined listings
$db_owned - table for storing information about your owned listings
$db_affiliates - table for your *collective* affiliates
$db_emailtemplate - table for your email templates
$db_errorlog - table for your error logs (required even if not set)
******************************************************************************/
$db_settings = 'settings';
$db_category = 'category';
$db_joined = 'joined';
$db_owned = 'owned';
$db_affiliates = 'affiliates';
$db_emailtemplate = 'emailtemplate';
$db_errorlog = 'errorlog';



/******************************************************************************
SENDMAIL VARIABLE
This is the sendmail variable for your server. Please check with your
hosting provider for the correct value.
******************************************************************************/
$sendmail = '/usr/sbin/sendmail';



/******************************************************************************
DO NOT MODIFY BELOW THIS LINE!
******************************************************************************/
if( !defined( 'DATABASE_CONNECT_ERROR' ) )
define( 'DATABASE_CONNECT_ERROR', 'Cannot connect to the database. ' .
'Check your config file and try again. MySQL said: ' );
if( !defined( 'STANDARD_ERROR' ) )
define( 'STANDARD_ERROR', '<p class="error">Error executing query. ' .
'Please see the error logs.' );
?>