How to create a [USERS] placeholder to show Number of Users

Solutions for other advanced phplisters

How to create a [USERS] placeholder to show Number of Users

Postby Andy000 » 9:49am, Mon 18 May, 2009

Hi there,

Would anyone know how to do this?
I have been asked to show the number of users on the subscribe page.
In the documentation, I could not find a placeholder (like [LISTS]) that I can just paste in the html.

Would anyone know how to create one and in which file?

Thanks heaps!

Smiles,

Andy
Andy000
PL Nut
 
Posts: 18
Joined: 12:00am, Fri 27 Mar, 2009
Location: New Zealand South Island

Re: How to create a [USERS] placeholder to show Number of Users

Postby Dragonrider » 10:56am, Wed 20 May, 2009

This took a little working out, but I've got it to work!

You can see the result at Wharfedale Festival lists. Note though this only shows all subscribers in the phplist database, it does not differentate between multiple lists.

Create a new file, called query.php in a text editor (or download the file I'll attach) and save to your lists main folder, ie: in the same folder/directory with the admin folder etc.

Code: Select all
<?PHP

# what is your Mysql database server
$database_host = "localhost";

# what is the name of the database we are using
$database_name = "your_db_name";

# who do we log in as?
$database_user = "your_db_username";

# and what password do we use
$database_password = "your_db_password";

$prefix="list_"; # set to your phplist prefix

$table=$prefix . "user_user";


$connection=mysql_connect($database_host,$database_user,$db) or die ("Something's not right here!");
$dbase=mysql_select_db($database_name,$connection) or die ("Something's still not right");

$query= "SELECT * FROM ". $table . "";
$result=mysql_query($query);
$nrows = mysql_num_rows($result);  # $nrows now stores the number of users on your list(s)

?>



In the above coding, set the first five values to suit your own installation and save it.

Find lists/index.php. Make a backup copy NOW.

Now, open lists/index.php in a TEXT editor, line 406:
Code: Select all
 function subscribePage($id) {

Add immediately after so it looks like this:
Code: Select all
function subscribePage($id) {
include_once("query.php");


Now find line 525/527, this code should be within a line or so,
Code: Select all
$html .= '<p><input type=submit name="subscribe" value="'.$data["button"].'" onClick="return checkform();"></p>
    </form><br/><br/>'


Change this to:
Code: Select all
$html .= '<p><input type=submit name="subscribe" value="'.$data["button"].'" onClick="return checkform();"></p>
    </form><br/><br/>Number of Subscribers: '.$nrows.'


And that should be ok, save it.

With luck, you should have a line on your subscription form that shows the number of subscribers.
Attachments
query.zip
(445 Bytes) Downloaded 340 times
My sites:- http://wharfedalefestival.co.uk, http://ilkleygardeners.org.uk, http://emergencyaid.net, http://dragonrider.co.uk
Latest phpList version is now 3.0.12 (3 February 2015) and requires a minimum of MySQL 5.0 and PHP 5.3.x
Dragonrider
Moderator
 
Posts: 3460
Joined: 6:58am, Sun 02 Jul, 2006
Location: Ilkley, West Yorkshire, United Kingdom

Re: How to create a [USERS] placeholder to show Number of Users

Postby Andy000 » 11:34am, Wed 20 May, 2009

Hi Dragonrider,

Thank you so much, you're awesome!
And YES, it looks great on your site.
And it looks like someone's in the middle of a festie right now? :D

I will have to fiddle with the prefix as something's not quite right yet.
On a default install, the config.php has these two fields:

Code: Select all
$table_prefix = "phplist_";
$usertable_prefix = "phplist_user_";


Knowing this, could you advise what to plugin into your 5th variable to make it work with these lines of your code:
Code: Select all
$prefix="list_"; # set to your phplist prefix
$table=$prefix . "user_user";


Warm regards,

Andy
Andy000
PL Nut
 
Posts: 18
Joined: 12:00am, Fri 27 Mar, 2009
Location: New Zealand South Island

Re: How to create a [USERS] placeholder to show Number of Users

Postby Dragonrider » 12:14pm, Wed 20 May, 2009

Hi Andy,

Yep, middle of Festival at the mo, actually off to work to take a group down to perform as well any minute now!

Change varible 5 to phplist and that should fix your query.

You can always check this by visiting your website's cpanle, finding the myphpadmin link and checking the names of the user_user table in that.
My sites:- http://wharfedalefestival.co.uk, http://ilkleygardeners.org.uk, http://emergencyaid.net, http://dragonrider.co.uk
Latest phpList version is now 3.0.12 (3 February 2015) and requires a minimum of MySQL 5.0 and PHP 5.3.x
Dragonrider
Moderator
 
Posts: 3460
Joined: 6:58am, Sun 02 Jul, 2006
Location: Ilkley, West Yorkshire, United Kingdom

Re: How to create a [USERS] placeholder to show Number of Users

Postby CS2 » 1:25pm, Wed 20 May, 2009

You could probably simplifiy the query.php file to this, using PHPlist's DB layer:
Code: Select all
$query= spritnf("SELECT * FROM %s",$GLOBALS["tables"]["user"]);
$result=Sql_Query($query);
$nrows =Sql_Num_Rows($result);  # $nrows now stores the number of users on your list(s)
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Re: How to create a [USERS] placeholder to show Number of Users

Postby Dragonrider » 6:43pm, Wed 20 May, 2009

Wow CS, you've lost me, SQL is not my native language. Hats off to you! :)
My sites:- http://wharfedalefestival.co.uk, http://ilkleygardeners.org.uk, http://emergencyaid.net, http://dragonrider.co.uk
Latest phpList version is now 3.0.12 (3 February 2015) and requires a minimum of MySQL 5.0 and PHP 5.3.x
Dragonrider
Moderator
 
Posts: 3460
Joined: 6:58am, Sun 02 Jul, 2006
Location: Ilkley, West Yorkshire, United Kingdom

Re: How to create a [USERS] placeholder to show Number of Users

Postby Andy000 » 6:51pm, Wed 20 May, 2009

Hi Dragonrider,

Welcome back! Did you enjoy the last performance?

I have one more question. You pass a $db variable to the connect function in
Code: Select all
$connection=mysql_connect($database_host,$database_user,$db)


but $db it is not initialized.

I wonder if this is why I can't connect?

Warm regards,

Andy
Andy000
PL Nut
 
Posts: 18
Joined: 12:00am, Fri 27 Mar, 2009
Location: New Zealand South Island

Re: How to create a [USERS] placeholder to show Number of Users

Postby Andy000 » 7:05pm, Wed 20 May, 2009

Nevermind $db, I looked up the function and it looks like it should be the password.
Now the php connects to the database.
But it doesn't return the query result. We're close! Looking into that, will update.

Cheers!
Andy000
PL Nut
 
Posts: 18
Joined: 12:00am, Fri 27 Mar, 2009
Location: New Zealand South Island

Re: How to create a [USERS] placeholder to show Number of Users

Postby Andy000 » 7:22pm, Wed 20 May, 2009

Yeah! Working!

Here's the demo

So, for the record, here is a whole query.php script that will return the number of users if pasted in the root phplist directory.
However, htaccess must be modified (add the file query.php in the line with allowed files).

Code: Select all
    <?PHP
    $database_host = "localhost"; /* 1 ENTER YOUR HOST IF IT IS NOT localhost */
    $database_name = "mydatabase"; /* 2 ENTER YOUR DATABASE */
    $database_user = "myuser"; /* 3 ENTER YOUR USER NAME */
    $database_password = "mypassword"; /* 4 ENTER YOUR PASSWORD */
    $prefix="phplist_"; /* 5 ENTER YOUR TABLE PREFIX */

/* NO NEED TO TOUCH ANYTHING ELSE */

    $table=$prefix . "user_user";
    $connection=mysql_connect($database_host,$database_user,$database_password) or die ("Something's not right here!");
    $dbase=mysql_select_db($database_name,$connection) or die ("Something's still not right");

    $query= "SELECT * FROM ". $table ;
    $result=mysql_query($query);
    $nrows = mysql_num_rows($result);  # $nrows now stores the number of users on your list(s)

echo '<html><head><body><br>TABLE:';
echo $table;
echo '<br>ROWS:';
echo $nrows;
echo '<br>END';
echo '</html></head></body>';

    ?>


Thanks again Dragonrider, learned heaps here.

Wishing you a beautiful evening,

Andy
Andy000
PL Nut
 
Posts: 18
Joined: 12:00am, Fri 27 Mar, 2009
Location: New Zealand South Island

Re: How to create a [USERS] placeholder to show Number of Users

Postby CS2 » 8:40pm, Wed 20 May, 2009

Dragonrider wrote:Wow CS, you've lost me, SQL is not my native language. Hats off to you! :)


Thanks!

I've thought about it a bit more, and it would actually be better to do the code like this:
Code: Select all
$query= $result = Sql_Fetch_Row_Query(sprintf("SELECT COUNT(*) FROM %s",$GLOBALS["tables"]["user"]));
$nrows =$result[0];  # $nrows now stores the number of users on your list(s)


The difference is that "SELECT * FROM..." returns the entire table (which can result in both a CPU and memory hit on the server if the table is large) and "SELECT COUNT(*) FROM..." returns *only* the number of rows.

(The new code also contains a typo correction from the previous example - I should have put "sprintf" NOT "spritnf" :oops: )
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Re: How to create a [USERS] placeholder to show Number of Users

Postby Dragonrider » 8:55pm, Wed 20 May, 2009

Oh, sorry! $db is the database password, I just use a hidden file to pass the db password and this is passed, on my setup, as $db, so yes, you would substitute your db password in place of $db, or set it earlier.

I put it as an included file just to make it easy to add in without hacking the core files too much.

And yes thanks, had a great afternoon at the Festival, my school wiped the board by winning every class they entered, and clinched all the trophies on offer for those classes, and got a nomination to a National Festival Concert for the group class.

CS2, even I think this could be wrong?? "$query= $result =", surely one or the other?? You've lost me already, no need to confuse the others too. :D
My sites:- http://wharfedalefestival.co.uk, http://ilkleygardeners.org.uk, http://emergencyaid.net, http://dragonrider.co.uk
Latest phpList version is now 3.0.12 (3 February 2015) and requires a minimum of MySQL 5.0 and PHP 5.3.x
Dragonrider
Moderator
 
Posts: 3460
Joined: 6:58am, Sun 02 Jul, 2006
Location: Ilkley, West Yorkshire, United Kingdom

Re: How to create a [USERS] placeholder to show Number of Users

Postby Andy000 » 9:05pm, Wed 20 May, 2009

Hey Dragonfly,

Congratulations on your class wins! You must be having a great day.

And thank you again for helping me set this up. I love it.

For the record, in case anyone is on the same track, I didn't modify index.php because I display the number of users BEFORE hitting the subscribe link.
Instead, I made the query.php page the main sign-up page. After the code above, I echo'ed the html for the page, including $nrows.

Smiles,

Andy
Andy000
PL Nut
 
Posts: 18
Joined: 12:00am, Fri 27 Mar, 2009
Location: New Zealand South Island

Re: How to create a [USERS] placeholder to show Number of Users

Postby CS2 » 9:21pm, Wed 20 May, 2009

Dragonrider wrote:CS2, even I think this could be wrong?? "$query= $result =", surely one or the other?? You've lost me already, no need to confuse the others too. :D


D'oh! :oops: :shock: You're right, of course.
Code: Select all
$result = Sql_Fetch_Row_Query(sprintf("SELECT COUNT(*) FROM %s",$GLOBALS["tables"]["user"]));
$nrows =$result[0];  # $nrows now stores the number of users on your list(s)


I think it's right this time :P
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Re: How to create a [USERS] placeholder to show Number of Users

Postby Andy000 » 9:37pm, Wed 20 May, 2009

One little tweak to save memory on a large list:

Code: Select all
    $query= "SELECT id FROM ". $table ;


instead of

Code: Select all
    $query= "SELECT * FROM ". $table ;



This must be totally basic but I am new to SQL queries :)
Andy000
PL Nut
 
Posts: 18
Joined: 12:00am, Fri 27 Mar, 2009
Location: New Zealand South Island

Re: How to create a [USERS] placeholder to show Number of Users

Postby CS2 » 9:45pm, Wed 20 May, 2009

Andy,
Take a look at my previous post and use that example. "SELECT id FROM..." should work, but it still returns every single user ID, whereas "SELECT COUNT(*) FROM..." returns a single row with *only* the number of rows. Its also much faster.
CS2
PL Master
 
Posts: 216
Joined: 2:20am, Wed 04 Feb, 2009

Next

Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 1 guest