vBaCMPS Most Recent Subscribers Module

[kall]

This will place a box on your vBa CMPS homepage showing the 10 most recent Paid Subscriptions.

It works by querying the subscriptionlog table for the expiry date of the subscription.

If you want it to show them by expiry date, or change the number shown, instructions are in the .zip.

*edit* They are now.

[fiber1]

There are no instructions in the zip?

[kall]

There are no instructions in the zip?
Oh, so there aren't.

Updated.

[misticjeff]

Hi,

Now there is nothing in the .zip file except instructions. Any chance of update the .zip file? I'd love to have this hack.

Jeff

[kall]

Hi,

Now there is nothing in the .zip file except instructions. Any chance of update the .zip file? I'd love to have this hack.

Jeff
That's just shocking. Where has my head been?

*fixes it*

[oldfan]

got a demo? :)

[kall]

got a demo? :)
http://www.nzboards.com - bottom right.

[Rich]

Hello,
Its pretty scary when the demo site has this written all over it:

Warning: chdir(): No such file or directory (errno 2) in /home2/nzboards/public_html/index.php on line 19

Warning: main(./global.php): failed to open stream: No such file or directory in /home2/nzboards/public_html/index.php on line 25

Fatal error: main(): Failed opening required './global.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home2/nzboards/public_html/index.php on line 25

[kall]

Hello,
Its pretty scary when the demo site has this written all over it:


*shakes head at the slowness of DNS propogation*

I am switching hosts at the moment.

Here's a screenshot:

[Chazz Layne]

Excellent, works flawlessly.

/me clicks install.

[steven s]

This worked very well, thanks.

But shouldn't 'subscribers_bit' be 'adv_portal_subsribers_bit' to conform with the other templates?

[alkatraz]

/me clicks install

thx!

[steven s]

Is it possible to sort by username?

[steven s]

Again, is it possible to sort by username and is it possible to make this block run in 2 columns?

[4number8]

if there is anything I could do to help you out in any way id be more than willing to my friend, thanks you once again for beiing such a great person you are, please If I run into troule would you be willing to help me out? I can ususally figure it out on my won but nothing like good company to keep you entertained, many thanks once again m8 and hope you and your familay are doing great, talk with you later on, take care and hope to hear from you soon ;)

[kall]

Again, is it possible to sort by username and is it possible to make this block run in 2 columns?
Probably.

Change

WHERE subscriptionlog.status = '1' ORDER BY regdate DESC LIMIT 10
");
to

WHERE subscriptionlog.status = '1' ORDER BY username DESC LIMIT 10
");


That's just me looking at it and doing the completely obvious thing of swapping regdate for username...I haven't tested it, but as I say, it's obvious, so it should work.

Just in case it crops up, swap DESC to ASC if you want the order reversed and LIMIT 10 to LIMIT * (where * is the number you want to display).

As for the columns, I wouldn't know. HTML is the devil.

[steven s]

Thanks but that doesn't work either.
The choices in subscription.status are

subscriptionlogid
subscriptionid
pusergroupid
regdate
expirydate

[kall]

Thanks but that doesn't work either.
The choices in subscription.status are

subscriptionlogid
subscriptionid
pusergroupid
regdate
expirydate
You are wrong. Doing as I said does work. Either you did not do as I said, or you are looking at a cached version, or something else strange is happening because that does work.

The choices you mention have nothing to do with what was asked, which was 'can we sort them by username?' As username was already being called from the db, it would seem really obvious that simply substituting regdate for username would work. And it does.

*shakes head* All you had to do was replace one word with another, and you couldn't manage it?

Examples below.

[steven s]

*shakes head* All you had to do was replace one word with another, and you couldn't manage it?
No need for sarcasim. :ermm:

Did you edit your original post?
In the email that vB sent me you clearly said to change regdate to userid.

In the text below the change you mention username.
I tried username and naturally it works. Thank you.

[kall]

No need for sarcasim. :ermm:

Did you edit your original post?
In the email that vB sent me you clearly said to change regdate to userid.

In the text below the change you mention username.
I tried username and naturally it works. Thank you.
Yes, I did edit that post..within seconds of posting it, but yours was posted over 2 hours after mine.

From that, I would have assumed that you would have *read* the post prior to trying the changes.

Also, given the total lack of a tick in the 'Supported' box above, you should be a little more appreciative of the support.

As it was such a simple change, may I suggest you take the time to learn a little about how mySQL queries work? If you had done this prior, it would have saved you a lot of time, and me having to post to point out that the table is already included, and all you have to do is change the name of the one it is sorted by.

[mholtum]

http://www.nzboards.com - bottom right.
That would be left...

[kall]

That would be left...
Heh, I had to re-add it to see why on earth mySQL had decided to not recognise the username column.

Of course, it hadn't, I was right, and it was a case of 'following instructions contained in a 'new post' notification, rather than those that were on the page' (and had been for over 2 hours).

[steven s]

I made the edits based on the email in the morning before leaving for work.
When it didn't work I sent a reply. I thought I needed a field from subscription.status.
I am appreciative of the support. It is one of the most useful modules on my portal page. I bumped the message hoping someone would see it as a new post and if they new the answer they would reply. I also had that additional question. I realize support is not clicked so I subscribed to this thread for the day someone searched for this topic and could answer my question. Thanks again.

[kall]

That's cool. :)

Sorry I missed it, I had unsubscribed from all threads cos my inbox was filling up...and sorry for the way I came across in the last few posts, I'm hecticly busy with moving house and am getting a little stressed.

[steven s]

That's cool. :)

Sorry I missed it, I had unsubscribed from all threads cos my inbox was filling up...and sorry for the way I came across in the last few posts, I'm hecticly busy with moving house and am getting a little stressed.
Not a problem. I have several books on mySQL and php. I pick them up and put them down. Just can't grasp it. I find I learn more by copying code and asking questions. I help people where I can. Maybe not with vB but in other things. It all evens out in the end.

I still have unopened boxes from moving several years ago.
My wife won't let me throw them out. :squareeyed:

[mholtum]

Odd It works great on my site, yet when I try to do it on another I get some SQL Error.


Database error in vBulletin 3.0.7:

Invalid SQL:
SELECT user.userid,username
FROM user
LEFT JOIN subscriptionlog as subscriptionlog ON (subscriptionlog.userid = user.userid)
WHERE subscriptionlog.status = '1' ORDER BY regdate DESC LIMIT 10

mysql error: Table 'theflyfi_forums.user' doesn't exist

mysql error number: 1146

Date: Monday 02nd of May 2005 05:09:17 PM
Script: http://www.theflyfishingforum.com/
Referer:
Username:
IP Address:

[kall]

Very sorry .. I don't use TABLE_PREFIX on my sites..

Change the query in subscribers.php to $getusers = $DB_site->query("
SELECT user.userid,username
FROM " . TABLE_PREFIX . "user
LEFT JOIN " . TABLE_PREFIX . "subscriptionlog as subscriptionlog ON (subscriptionlog.userid = user.userid)
WHERE subscriptionlog.status = '1' ORDER BY regdate DESC LIMIT 10
");

[mholtum]

Database error in vBulletin 3.0.7:

Invalid SQL:
SELECT user.userid,username
FROM vb3_user
LEFT JOIN vb3_subscriptionlog as subscriptionlog ON (subscriptionlog.userid = user.userid)
WHERE subscriptionlog.status = '1' ORDER BY regdate DESC LIMIT 10

mysql error: Unknown table 'user' in field list

mysql error number: 1109

Date: Thursday 21st of July 2005 10:01:35 AM
Script: http://www.theflyfishingforum.com/
Referer:
Username: fshfanatic
IP Address: 68.99.209.240

[mholtum]

figured out a fix yours generated a error


SELECT user.userid,username
FROM vb3_user user
LEFT JOIN vb3_subscriptionlog as subscriptionlog ON (subscriptionlog.userid = user.userid)
WHERE subscriptionlog.status = '1' ORDER BY regdate DESC LIMIT 10

[Marco van Herwaarden]

In PHP the correct query should be:
$getusers = $DB_site->query("
SELECT user.userid,username
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "subscriptionlog as subscriptionlog ON (subscriptionlog.userid = user.userid)
WHERE subscriptionlog.status = '1' ORDER BY regdate DESC LIMIT 10
");


PS I always use a table prefix on my testsite, tou avoid this kind of problems.

[kall]

In PHP the correct query should be:
$getusers = $DB_site->query("
SELECT user.userid,username
FROM " . TABLE_PREFIX . "user AS user
LEFT JOIN " . TABLE_PREFIX . "subscriptionlog as subscriptionlog ON (subscriptionlog.userid = user.userid)
WHERE subscriptionlog.status = '1' ORDER BY regdate DESC LIMIT 10
");


PS I always use a table prefix on my testsite, tou avoid this kind of problems.

That looks so much like the fix I posted a few lines up, I can't even tell what's different.

But your point is well made. Table Prefixes always end up biting me in the ass. :)

[mholtum]

That looks so much like the fix I posted a few lines up, I can't even tell what's different.

But your point is well made. Table Prefixes always end up biting me in the ass. :)


FROM " . TABLE_PREFIX . "user AS user