Updated 2/23/13: 2.0.4: Need help with making this SQL query work right... Ty.

[MoreBloodWine]

Code Select Expand

echo '
<b><u><font size="3">Milestones</font></u></b><br />
';

$username="removed";
$password="removed";
$database="removed";

mysql_connect('localhost',$username,$password);
@mysql_select_db($database) or die ("Unable to select database");

$sql1 = "SELECT (id_member / 1000) as thousands, date_registered FROM smf_members AS milestones WHERE id_member % 1000 = 0 ORDER BY date_registered DESC";
$data1 = mysql_query($sql1) or die(mysql_error() . '<br /><br />Query: ' . $sql1);

echo '
<div style="overflow: auto; width: 200px; height: 34px; border: none; text-align: left; font-size: 13px;">
';
while($row1 = mysql_fetch_array($data1)){
echo date('n/j/Y', strftime($row1['date_registered'])), " - ", (int)$row1['thousands'], "k Unique Users<br />";
}
echo'
</div>
';

Doe sthis only work if id_member runs sequental and not have gaps from removed members ?

Ex. 1, 2, 3, 4, 8, 9, 10

See myu2sig.com "milestone" on the left of the page for what the code should do. What I am trying to do now is adapt it for SMF.

If it does however as written, rely on the id_member being sequential with no gaps, is there a way to rewrite it to ignore the non sequential numbering of the row id_member and go by the actual number of entries in the table smf_mmebers ? So lets say there's seven entries in smf_members but of those seven members id_member is numbered like this... 1, 2, 4, 5, 6, 7, 9

The code would ignore the row id_member and somehow count just those 7 entries in the table smf_member.

[MoreBloodWine]

bump

[MoreBloodWine]

The main work may as well be done, just need help making it work with SMF please.

[MoreBloodWine]

Wonder if I can count to a million before this gets solved lol

[emanuele]

You mean using SSI?
Don't know, something like that:

Code Select Expand

require_once('/path/to/forum/SSI.php');
echo '
<b><u><font size="3">Milestones</font></u></b><br />
';

$data1 = $smcFunc['db_query']('',
'SELECT (id_member / 1000) as thousands, date_registered
FROM {db_prefix}members AS milestones
WHERE id_member % 1000 = 0
ORDER BY date_registered DESC',
array()
);
echo '
<div style="overflow: auto; width: 200px; height: 34px; border: none; text-align: left; font-size: 13px;">
';
while($row1 = $smcFunc['db_fetch_assoc']($data1)){
echo date('n/j/Y', strftime($row1['date_registered'])), " - ", (int)$row1['thousands'], "k Unique Users<br />";
}
echo'
</div>
';

[MoreBloodWine]

Lost in the comment about SSI but I  suspect thats what you did is make it work with that compared to how I had it.

That said, will non sequential ordering for deleted members affect this in any way or does it ignore that and count that actual number of enteries ?

Ty.

I'm also curious about the date being correct for each "milestone" not sure how that works if the numbering is ignored.

You mean using SSI?
Don't know, something like that:

Code Select Expand

require_once('/path/to/forum/SSI.php');
echo '
<b><u><font size="3">Milestones</font></u></b><br />
';

$data1 = $smcFunc['db_query']('',
'SELECT (id_member / 1000) as thousands, date_registered
FROM {db_prefix}members AS milestones
WHERE id_member % 1000 = 0
ORDER BY date_registered DESC',
array()
);
echo '
<div style="overflow: auto; width: 200px; height: 34px; border: none; text-align: left; font-size: 13px;">
';
while($row1 = $smcFunc['db_fetch_assoc']($data1)){
echo date('n/j/Y', strftime($row1['date_registered'])), " - ", (int)$row1['thousands'], "k Unique Users<br />";
}
echo'
</div>
';

[Arantor]

I don't think it will properly adhere to non sequential numbering.

Seems to me you need to figure out:
1) What thousandth milestone you're on
2) What member that was (and thus their registered date)

Once SSI.php is loaded, $modSettings['totalMembers'] will contain the total number of members.

Thus, $modSettings['totalMembers'] - ($modSettings['totalMembers'] % 1000) will be the number of thousands of users you've had.

From there it's fairly simple magic, deducting one to get the right member.

Code Select Expand

require('SSI.php');
$milestone = $modSettings['totalMembers'] - ($modSettings['totalMembers'] % 1000) - 1;

$data1 = $smcFunc['db_query']('',
'SELECT date_registered
FROM {db_prefix}members
ORDER BY date_registered DESC
LIMIT {int:limit}, 1',
array(
'limit' => $milestone,
)
);
list($timestamp) = $smcFunc['db_fetch_row']($data1);


Then you can display the member id, the name of that member and format the date in whatever fashion you want using $timestamp, be that date() or strftime() or whatever.

NOT TESTED.

[MoreBloodWine]

I don't think it will properly adhere to non sequential numbering.

Seems to me you need to figure out:
1) What thousandth milestone you're on
2) What member that was (and thus their registered date)
Once SSI.php is loaded, $modSettings['totalMembers'] will contain the total number of members.

Thus, $modSettings['totalMembers'] - ($modSettings['totalMembers'] % 1000) will be the number of thousands of users you've had.

From there it's fairly simple magic, deducting one to get the right member.

Code Select Expand

require('SSI.php');
$milestone = $modSettings['totalMembers'] - ($modSettings['totalMembers'] % 1000) - 1;

$data1 = $smcFunc['db_query']('',
'SELECT date_registered
FROM {db_prefix}members
ORDER BY date_registered DESC
LIMIT {int:limit}, 1',
array(
'limit' => $milestone,
)
);
list($timestamp) = $smcFunc['db_fetch_row']($data1);


Then you can display the member id, the name of that member and format the date in whatever fashion you want using $timestamp, be that date() or strftime() or whatever.

NOT TESTED.

Just thought of this but might be a PIA, but look at all the registered dates and take the thousanth oldest, so on and so forth for each thousandth milestone. Will try code you provided and post back, Thx.

I shouldnt have any members to worry about deleteing if my spam protection holds up. I just thought I'd see how the non sequential numbering would be handled should a spam member show up and then be deleted.

[Arantor]

Just thought of this but might be a PIA, but look at all the registered dates and take the thousanth oldest, so on and so forth for each thousandth milestone

That's pretty much what the code is doing, except saving you the effort of doing every thousandth.

[MoreBloodWine]

@Arantor: Code used as is gave this...

Fatal error: Cannot redeclare ssi_shutdown() (previously declared in /removed/SSI.php:198) in /removed/SSI.php on line 200

[Arantor]

That means you've put two requests for SSI.php into the page.

[MoreBloodWine]

That means you've put two requests for SSI.php into the page.

It's gotta be something with TP then because I can't find an existing call in any of my blocks. That said, ince you said that error meant it was already being called I decided to remove the call from your code since I suspect the exisint call i scoming from TP. In any case, removing the call from your code gives this error now.

Fatal error: Function name must be a string in /removed/Dust514/Sources/Load.php(2163) : eval()'d code(127) : eval()'d code on line 5

Sorry for not mentioning I use TP if that matter which it now seems it does.

Edit: Is there any way to reqrite the code to forget using SSI.php, kind of how it was originally posted just logging in to the DB ?

[Arantor]

TP uses SSI. You could take out the SSI line and replace it with global $smcFunc; instead.

Edit: Is there any way to reqrite the code to forget using SSI.php, kind of how it was originally posted just logging in to the DB ?

Aside from the performance concerns of doing that, you mean? There is a reason why we don't create more connections than absolutely necessary, it causes performance bottlenecks down the line for anything else using the server.

[MoreBloodWine]

TP uses SSI. You could take out the SSI line and replace it with global $smcFunc; instead.

So replace the require line with this...

global $smcFunc;

I know thats more or les what you aid, I just wana make sure were on the same page lol

Edit: Is there any way to reqrite the code to forget using SSI.php, kind of how it was originally posted just logging in to the DB ?

Aside from the performance concerns of doing that, you mean? There is a reason why we don't create more connections than absolutely necessary, it causes performance bottlenecks down the line for anything else using the server.

Good point.

Ty for everythin til now.

[Arantor]

Yes, that's exactly what I said. Does it work now?

[MoreBloodWine]

It does this.

[Arantor]

That would be a JavaScript error, unrelated as far as I know to the code I wrote...

[MoreBloodWine]

That would be a JavaScript error, unrelated as far as I know to the code I wrote...

Hmm.

[MoreBloodWine]

Interesting breakthrough, using emanuele's code with global $smcFunc; seems to work fine without error.

I changed the two instanced of 1000 to diff numbers, with the lat test using just 5 and it seems to be working as intended.

Code Select Expand

echo '
<center><b><u><font size="3">Milestones</font></u></b></center><br />
';

global $smcFunc;
$data1 = $smcFunc['db_query']('',
'SELECT (id_member / 5) as thousands, date_registered
FROM {db_prefix}members AS milestones
WHERE id_member % 5 = 0
ORDER BY date_registered DESC',
array()
);
echo '
<div style="overflow: auto; width: 200px; height: 34px; border: none; text-align: left; font-size: 13px;">
';
while($row1 = $smcFunc['db_fetch_assoc']($data1)){
echo date('n/j/Y', strftime($row1['date_registered'])), " - ", (int)$row1['thousands'], "k Unique Users<br />";
}
echo'
</div>
';

echo'
<br />
';


That said, how would his code be modified to ignore the sequential thing like you worked in to your code where what's looked at is the registered date and then taking that as the "thousandth" user or in this test case, ever 5th user.

5, 10, 15, 20 etc.

[Arantor]

Oh, it wasn't clear you wanted it for each thousand users. Ignore everything I said, then, because there's just no efficient way to do it the way I posted short of doing a query for every thousandth, which is inefficient on the server.

* Arantor gets what he deserves for trying to help without reading *everything* in detail

[MoreBloodWine]

Oh, it wasn't clear you wanted it for each thousand users. Ignore everything I said, then, because there's just no efficient way to do it the way I posted short of doing a query for every thousandth, which is inefficient on the server.

* Arantor gets what he deserves for trying to help without reading *everything* in detail

Ya for each thouand heh. In any event, how can, if not already, emanuele's code me modified to ignore the numbering and just look at the registered date of every x user ?

I guess like what I thought of that you said was in your code.

[Arantor]

You basically can't. There is simply no efficient way to do what you're trying to do without having something go through every single row. My way got the server to do it efficiently making use of indexes on the table, anything else will be slower (much slower by comparison)

What was in my code is exactly what I said it was - getting the milestone based on the highest number of thousandths of members. I even specifically said it skipped everything else on the way.

[MoreBloodWine]

You basically can't. There is simply no efficient way to do what you're trying to do without having something go through every single row. My way got the server to do it efficiently making use of indexes on the table, anything else will be slower (much slower by comparison)

What was in my code is exactly what I said it was - getting the milestone based on the highest number of thousandths of members. I even specifically said it skipped everything else on the way.

Ok, and there's not way to try and get your code to not do that bug showed in the image which you aid looked like a JS thing but also get it to work for every thousand users ?

Where from if I understood your one reply right only currently lists the first thousand user milestone mark.

Sorry if I am being thick headed ;-/

[Arantor]

you aid looked like a JS thing

That thing is a JS bug. More than that I have no idea.

Where from if I understood your one reply right only currently lists the first thousand user milestone mark.

You misunderstood it.

If you have 6500 users, it would return the registered date of the 6000th user, i.e. the last milestone.

[MoreBloodWine]

Well, I gues I will use emanueles code since it works and cross the bridge of what to do if and when I need to remove a spam member should one arise.

Only thing I can think of would be to delete it then just renumber the members row or something.

[Arantor]

You'd have to renumber the members row and every other table that references it if you delete a row.

[MoreBloodWine]

Well, so I can at least say that I asked and maybe save the answer for future reference.

Lets say I have 2kl members and I deleted 20 that were spam. What would need to be done to renumber everything to play nice so that on the next new signup the user is properly assigned as member 1981 since we removed 20 from the previous 2k ?

[Arantor]

Change every single table that has an id_member in it. And change every single row that references those members.

Simply put: don't go there.

[MoreBloodWine]

Code Select Expand

echo '
<center><b><u><font size="3">Milestones</font></u></b></center>
';

global $smcFunc;
$data1 = $smcFunc['db_query']('',
'SELECT (id_member / 25) as thousands, date_registered
FROM {db_prefix}members AS milestones
WHERE id_member % 25 = 0
ORDER BY date_registered DESC',
array()
);
echo '
<div style="overflow: auto; width: 220px; height: 34px; border: none; text-align: left; font-size: 13px;">
';
while($row1 = $smcFunc['db_fetch_assoc']($data1)){
echo date('n/j/Y', strftime($row1['date_registered'])), " - ", number_format((int)$row1['thousands']), " Users Gained !<br />";
}
echo'
</div>
';

So instead of reading: 2/19/2013 - 25 Users Gained !

It reads: 2/19/2013 - 1 Users Gained !

What's wrong with the code... since user gain is slow I want to start it out with ever 25 users so it should read something like this as time goes by with the 25 user setting.

2/19/2013 - 25 Users Gained !
2/19/2013 - 50 Users Gained !
2/19/2013 - 75 Users Gained !
2/19/2013 - 100 Users Gained !
2/19/2013 - 125 Users Gained !
2/19/2013 - 150 Users Gained !

So on and so forth, of course wit hthe appropriate date shown which I was just too lazy to change for purposes of the example.

Ty.

[MoreBloodWine]

I tried figuring this out by SQL queries just aren't my cup of tea as the saying goes.

[MoreBloodWine]

I feel like a complete ass, the code does what it's supposed to. When I was using it per thousand on my non SMF site it read off like this...

1k users gained
2k users gained

etc. etc. etc.

So the 1 it's displaying now is fine given current code design. I guess what I should be asking is this.

Code Select Expand

echo '
<center><b><u><font size="3">Milestones</font></u></b></center>
';

global $smcFunc;
$data1 = $smcFunc['db_query']('',
'SELECT (id_member / 25) as thousands, date_registered
FROM {db_prefix}members AS milestones
WHERE id_member % 25 = 0
ORDER BY date_registered DESC',
array()
);
echo '
<div style="overflow: auto; width: 220px; height: 34px; border: none; text-align: left; font-size: 13px;">
';
while($row1 = $smcFunc['db_fetch_assoc']($data1)){
echo date('n/j/Y', strftime($row1['date_registered'])), " - ", number_format((int)$row1['thousands']), " Users Gained !<br />";
}
echo'
</div>
';

What needs to be changed so it reads off for every 25 users which is what's currently set ?

Ex.

2/19/2013 - 25 Users Gained !
3/29/2013 - 50 Users Gained !
6/17/2013 - 75 Users Gained !

So on n so forth... where as in it's current format it would read off like this.

2/19/2013 - 1 Users Gained !
3/29/2013 - 2 Users Gained !
6/17/2013 - 3 Users Gained !

Sorry for taking soo long to catch my own goof.

[emanuele]

If I got what you want to do, this should be enough:

Code Select Expand

echo '
<center><b><u><font size="3">Milestones</font></u></b></center>
';

global $smcFunc;
$increments = 25;
$data1 = $smcFunc['db_query']('',
'SELECT date_registered
FROM {db_prefix}members AS milestones
WHERE id_member % {int:increments} = 0
ORDER BY date_registered DESC',
array(
'increments' => $increments,
)
);
echo '
<div style="overflow: auto; width: 220px; height: 34px; border: none; text-align: left; font-size: 13px;">
';
$count = $increments;
while ($row1 = $smcFunc['db_fetch_assoc']($data1))
{
echo date('n/j/Y', strftime($row1['date_registered'])), " - ", number_format($count), " Users Gained !<br />";
$count += $increments;
}
echo'
</div>
';

[MoreBloodWine]

2/19/2013 - 5 Users Gained !
2/7/2013 - 10 Users Gained !
2/2/2013 - 15 Users Gained !
1/26/2013 - 20 Users Gained !
1/20/2013 - 25 Users Gained !

Tested with a setting of 5 to get a few readouts, works prfect but the 5-25 is backwayrds. The attached dates however are right if that makes sense.

Basically instead of that above, it should be reading as follows.

2/19/2013 - 25 Users Gained !
2/7/2013 - 20 Users Gained !
2/2/2013 - 15 Users Gained !
1/26/2013 - 10 Users Gained !
1/20/2013 - 5 Users Gained !

[emanuele]

Code Select Expand

echo '
<center><b><u><font size="3">Milestones</font></u></b></center>
';

global $smcFunc;
$increments = 25;
$data1 = $smcFunc['db_query']('',
'SELECT date_registered
FROM {db_prefix}members AS milestones
WHERE id_member % {int:increments} = 0
ORDER BY date_registered DESC',
array(
'increments' => $increments,
)
);
$dates = array();
while ($row1 = $smcFunc['db_fetch_assoc']($data1))
$dates[] = $row1;

$total = $increments * count($dates);
echo '
<div style="overflow: auto; width: 220px; height: 34px; border: none; text-align: left; font-size: 13px;">
';
foreach($dates as $row1)
{
echo date('n/j/Y', strftime($row1['date_registered'])), " - ", number_format($total), " Users Gained !<br />";
$total -= $increments;
}
echo'
</div>
';

* emanuele thinks

[MoreBloodWine]

Code Select Expand

echo '
<center><b><u><font size="3">Milestones</font></u></b></center>
';

global $smcFunc;
$increments = 25;
$data1 = $smcFunc['db_query']('',
'SELECT date_registered
FROM {db_prefix}members AS milestones
WHERE id_member % {int:increments} = 0
ORDER BY date_registered DESC',
array(
'increments' => $increments,
)
);
$dates = array();
while ($row1 = $smcFunc['db_fetch_assoc']($data1))
$dates[] = $row1;

$total = $increments * count($dates);
echo '
<div style="overflow: auto; width: 220px; height: 34px; border: none; text-align: left; font-size: 13px;">
';
foreach($dates as $row1)
{
echo date('n/j/Y', strftime($row1['date_registered'])), " - ", number_format($total), " Users Gained !<br />";
$total -= $increments;
}
echo'
</div>
';

* emanuele thinks

Looks good, you rock emanuele !

[MoreBloodWine]

Hey emanuele, was wondering if you could help me once again. Seems 2.0.5 doesnt like the way this is coded when used in a TP block. I say TP block because I got no where else to really use it on the site.

The post about the issue with the code: http://www.simplemachines.org/community/index.php?topic=511110.msg3607955#msg3607955

The code as it is currently used: http://www.simplemachines.org/community/index.php?topic=511110.msg3608029#msg3608029

Ty.

[emanuele]

Seems 2.0.5 doesnt like the way this is coded when used in a TP block.

I don't think so: from 2.0.4 and 2.0.5 there is nothing that would affect this. So if it was working on 2.0.4 it shall work on 2.0.5 and the issue is somewhere else.

[MoreBloodWine]

This oughta be fun lol, but ty for the reply...

Think I might save this tasks for tomorrow / later though... it's 10:30am and I have yet to sleep

Modification Packages — Installed  Mod Name Version 
1. Users Online Today 2.0.3   [ Uninstall ] [ List Files ] [ Delete ] 
2. BK-SMF Sub-Board 1.5   [ Uninstall ] [ List Files ] [ Delete ] 
3. TinyPortal 1.107   [ Uninstall ] [ List Files ] [ Delete ] 
4. Add Facebook Like, Tweet, and Google +1 1.0.3a   [ Uninstall ] [ List Files ] [ Delete ] 
5. Board Viewers Mod 1.2.1.1b   [ Uninstall ] [ List Files ] [ Delete ] 
6. Tapatalk SMF 2.0 RC5/Final Plugin 3.5.0   [ Uninstall ] [ List Files ] [ Delete ] 
7. Redirect on Login and/or Logout mod 0.4   [ Uninstall ] [ List Files ] [ Delete ] 
8. Birthday Posts 0.13 Beta   [ Uninstall ] [ List Files ] [ Delete ] 
9. Aeva ~ Auto-Embed Video & Audio 7.2   [ Uninstall ] [ List Files ] [ Delete ] 
10. Password Protect Boards 1.2   [ Uninstall ] [ List Files ] [ Delete ] 
11. Last Active On MemberList 1.4   [ Uninstall ] [ List Files ] [ Delete ] 
12. Sorted Package Manager Listing 1.0.2   [ Uninstall ] [ List Files ] [ Delete ] 
13. AvatarSelect 1.2.1   [ Uninstall ] [ List Files ] [ Delete ] 
14. Slashout Banned Members 1.2   [ Uninstall ] [ List Files ] [ Delete ] 
15. SMF 2.0.5 Update 1.0   [ Uninstall ] [ List Files ] [ Delete ] 
16. Join date and Location in Posts 1.3   [ Uninstall ] [ List Files ] [ Delete ] 
17. Stop Forum Spam 1.0   [ Uninstall ] [ List Files ] [ Delete ] 
18. Ad Managment 3.0.1   [ Uninstall ] [ List Files ] [ Delete ] 
19. Activity in Profile 1.1   [ Uninstall ] [ List Files ] [ Delete ] 
20. Stop Spammer 2.3.9   [ Uninstall ] [ List Files ] [ Delete ] 
21. SMF Gallery Lite 3.1.4   [ Uninstall ] [ List Files ] [ Delete ] 
22. Activity Bar 1.1   [ Uninstall ] [ List Files ] [ Delete ] 
23. Country Flags 2.1   [ Uninstall ] [ List Files ] [ Delete ] 
24. Add Favicon.ico Support 1.101   [ Uninstall ] [ List Files ] [ Delete ] 

Modification Packages — Installed - Not Uninstallable (no uninstall section for this version of SMF)  Mod Name Version 
1. Reason For Editing Mod 2.3.2   [ List Files ] [ Delete ] 
2. SMF 2.0.4 Update 1.0   [ List Files ] [ Delete ] 
3. Double Check your Email in Registration by SmfSimple.com 1.0   [ List Files ] [ Delete ] 
4. Profile Moderator Management 2.0   [ List Files ] [ Delete ] 

[MoreBloodWine]

Now I am confused... on my other site that has the following installed it does the same thing as mentioned in the other thread. Not sure what to do now because none of these mods conflict. Funny thing is, the oher site these mods are pulled from is a fresh file replace I did a cpl weeks back. So this site may as well be brand new with like 4 theme edits and these mods, and the theme edits were just css stuff for header aligning.

Modification Packages — Installed  Mod Name Version 
1. TinyPortal 1.107   [ Uninstall ] [ List Files ] [ Delete ] 
2. Sorted Package Manager Listing 1.0.2   [ Uninstall ] [ List Files ] [ Delete ] 
3. Ad Managment 3.0.1   [ Uninstall ] [ List Files ] [ Delete ] 
4. Country Flags 2.1   [ Uninstall ] [ List Files ] [ Delete ]