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

[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 ]