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

Started by MoreBloodWine, January 28, 2013, 04:19:24 PM

Previous topic - Next topic

MoreBloodWine

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.
Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


MoreBloodWine

Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


MoreBloodWine

The main work may as well be done, just need help making it work with SMF please.
Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


MoreBloodWine

Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


emanuele

You mean using SSI?
Don't know, something like that:
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>
';


Take a peek at what I'm doing! ;D




Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

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.
Quote from: emanuele on February 11, 2013, 08:00:32 PM
You mean using SSI?
Don't know, something like that:
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>
';

Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


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.

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

Quote from: Arantor on February 11, 2013, 08:21:25 PM
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.

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.
Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


Arantor

QuoteJust 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

Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


Arantor

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

MoreBloodWine

Quote from: Arantor on February 11, 2013, 08:31:52 PM
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 ?
Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


Arantor

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

QuoteEdit: 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

Quote from: Arantor on February 11, 2013, 08:55:30 PM
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

Quote from: Arantor on February 11, 2013, 08:55:30 PM
QuoteEdit: 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.
Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


Arantor


MoreBloodWine

Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


Arantor

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

MoreBloodWine

Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


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.

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.
Want a sig like mine for your BTCGuild stats ? Then check this out: Spend-ur-Bits


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

Advertisement: