I'd suggest against doing this unless you want a copy of the database.
I actually wanted to see all data of the table 
Topic: Tool to run online SQL queries over Full Merit Data - page 7. (Read 23394 times)
This could spit out some interesting data what about gathering a certain rank like "hero members" and spitting out the usernames of the ones which have received at least 5 merits what sort of command would need to be put in?
I will have a look tomorrow and add the rank information per user in another table, perhaps LoyceV has already it available, in that case would be quite fast to include that in.
After that should not be a problem giving an answer to that question.
At the moment since there is no rank you can run this for all the users:
Link because the forum is blocking sql in the message:
https://pastebin.com/3dqMdeK0
This could spit out some interesting data what about gathering a certain rank like "hero members" and spitting out the usernames of the ones which have received at least 5 merits what sort of command would need to be put in?
I will have a look tomorrow and add the rank information per user in another table, perhaps LoyceV has already it available, in that case would be quite fast to include that in.
After that should not be a problem giving an answer to that question.
At the moment since there is no rank you can run this for all the users:
Link because the forum is blocking sql in the message:
https://pastebin.com/3dqMdeK0
if you didnt block some operations, do it fast
block
- INSERT
- UPDATE
- DELETE
for avoid trolls and vandals
block
- INSERT
- UPDATE
- DELETE
for avoid trolls and vandals
I mean, considering vod's database previously got hacked, I'd be surprised if those commands weren't already blocked.
I was interested in SELECT all query.
And...
This things are getting really interesting for me. May be I want to start learning how to scrape data
Good job mate.
PS: It took long time to pull-up the entire table (Letting you know only - hoping you can do something)
I'd suggest against doing this unless you want a copy of the database. Code:
SELECT * FROM MeritData;
And...
This things are getting really interesting for me. May be I want to start learning how to scrape data
Good job mate.
PS: It took long time to pull-up the entire table (Letting you know only - hoping you can do something)
Unless all commands run with more than a few seconds/minutes lag then don't do anything op.
This could spit out some interesting data what about gathering a certain rank like "hero members" and spitting out the usernames of the ones which have received at least 5 merits what sort of command would need to be put in?
little advice
use a different repo (and not one repo for all)
i sent you a pm
use a different repo (and not one repo for all)
i sent you a pm
All the changes are "local", the database cannot be modified really as is hosted in Github and there are no write permissions in there. If you make changes and then refresh the page the original data will be back.
Yes can be further improved for sure. I'll improve the data quality so can be used easily and more effectively.
Yes, that above was just to show the field in the table
Not much can be done there, prefer to keeping it "unchecked", everything run on local so no harm is done.
Nicely done there Piggy, i'm sure you'll build on it furthermore. Perhaps you could transform de unix date field into common date field for viewers who cannot do it themselves.
Yes can be further improved for sure. I'll improve the data quality so can be used easily and more effectively.
Code:
create table MeritData(
Date Float,
Merit Integer,
Msg TEXT,
FromID Float,
ToID Float,
Board TEXT,
SubBoard TEXT
)
Yes, that above was just to show the field in the table
I was interested in SELECT all query.
And...
This things are getting really interesting for me. May be I want to start learning how to scrape data
Good job mate.
PS: It took long time to pull-up the entire table (Letting you know only - hoping you can do something)
Code:
SELECT * FROM MeritData;
And...
This things are getting really interesting for me. May be I want to start learning how to scrape data
Good job mate.
PS: It took long time to pull-up the entire table (Letting you know only - hoping you can do something)
Not much can be done there, prefer to keeping it "unchecked", everything run on local so no harm is done.
if you didnt block some operations, do it fast
block
- INSERT
- UPDATE
- DELETE
for avoid trolls and vandals
block
- INSERT
- UPDATE
- DELETE
for avoid trolls and vandals
Table structure:
I have been creating your script in XAMPP, but have errors then I add a bit script "create table" and removes the symbol (""). and done created a table in DBCode:
MeritData(
"Date" Float,
"Merit" Integer,
"Msg" TEXT,
"FromID" Float,
"ToID" Float,
"Board" TEXT,
"SubBoard" TEXT
)
Code:
create table MeritData(
Date Float,
Merit Integer,
Msg TEXT,
FromID Float,
ToID Float,
Board TEXT,
SubBoard TEXT
)
Nicely done there Piggy, i'm sure you'll build on it furthermore. Perhaps you could transform de unix date field into common date field for viewers who cannot do it themselves.
I was interested in SELECT all query.
And...
This things are getting really interesting for me. May be I want to start learning how to scrape data
Good job mate.
PS: It took long time to pull-up the entire table (Letting you know only - hoping you can do something)
Code:
SELECT * FROM MeritData;
And...
This things are getting really interesting for me. May be I want to start learning how to scrape data
Good job mate.
PS: It took long time to pull-up the entire table (Letting you know only - hoping you can do something)
UPDATES:
This is a tool that allow you to run full SQL queries over the merit data and is based on SQLite. If you are familiar with SQL you know the possibilities are basically endless or limited to your SQL knowledge.
My previous similar tool (here) is perhaps more user friendly, but has some limitation: speed and research flexibility. So this is why i made this.
You can find more information about SQL syntax in here if you are interested to learn, i may also give a hand if somebody want to get out some particular information out, even though my SQL skills are a bit rusty.
Table structure:
How to use it:
Just type the query in the box and press Exceute, F5 or Ctrl-Enter to execute it.
Link:
https://albertoit.github.io/Merit-Explorer-SQL/
For those of you who are not familiar with SQL, beside the fact that you will get any result extremely fast, this is what it means:[/b]
We can easily get the Top Receiver for any local section simply using this:
or for any board:
Similarly we can find out the Top Giver:
or for any any board:
Find out the total merit awarded so far:
Want the full history for a particular user?
This is a tool that allow you to run full SQL queries over the merit data and is based on SQLite. If you are familiar with SQL you know the possibilities are basically endless or limited to your SQL knowledge.
My previous similar tool (here) is perhaps more user friendly, but has some limitation: speed and research flexibility. So this is why i made this.
You can find more information about SQL syntax in here if you are interested to learn, i may also give a hand if somebody want to get out some particular information out, even though my SQL skills are a bit rusty.
Table structure:
Code:
MeritData(
"Date" TEXT,
"Merit" Integer,
"Msg" TEXT,
"FromID" Integer,
"ToID" Integer,
"Board" TEXT,
"SubBoard" TEXT,
"TitleThread" TEXT
)
UserData(
UserId Integer PRIMARY KEY,
UserName TEXT,
Rank TEXT,
Trust TEXT,
Location TEXT
)
How to use it:
Just type the query in the box and press Exceute, F5 or Ctrl-Enter to execute it.
Link:
https://albertoit.github.io/Merit-Explorer-SQL/
For those of you who are not familiar with SQL, beside the fact that you will get any result extremely fast, this is what it means:[/b]
We can easily get the Top Receiver for any local section simply using this:
Code:
SELECT MAX(result.total) as "Total Merit", result.toid as "Top merit receiver", result.SubBoard as "Local board" FROM (
SELECT toid, SubBoard, SUM(Merit) AS total
FROM meritdata as m
WHERE m.Board in (SELECT Board FROM meritdata Where Board like "Local%" GROUP BY Board)
GROUP BY toid, Board
ORDER BY Board,total Desc) AS result
GROUP BY SubBoard
ORDER BY result.total Desc
or for any board:
Code:
SELECT MAX(result.total) as "Total Merit", result.toID as "Top merit receiver", result.SubBoard as "Board" FROM (
SELECT toid, SubBoard, SUM(Merit) AS total
FROM meritdata as m
WHERE m.SubBoard in (SELECT SubBoard FROM meritdata GROUP BY SubBoard)
GROUP BY toid, SubBoard
ORDER BY SubBoard,total Desc) AS result
GROUP BY SubBoard
ORDER BY result.total Desc;
Similarly we can find out the Top Giver:
Code:
SELECT MAX(result.total) as "Total Merit", result.fromid as "Top merit giver", result.SubBoard as "Local board" FROM (
SELECT fromid, SubBoard, SUM(Merit) AS total
FROM meritdata as m
WHERE m.Board in (SELECT Board FROM meritdata Where Board like "Local%" GROUP BY Board)
GROUP BY fromid, Board
ORDER BY Board,total Desc) AS result
GROUP BY SubBoard
ORDER BY result.total Desc;
or for any any board:
Code:
SELECT MAX(result.total) as "Total Merit", result.fromID as "Top merit giver", result.SubBoard as "Board" FROM (
SELECT fromid, SubBoard, SUM(Merit) AS total
FROM meritdata as m
WHERE m.SubBoard in (SELECT SubBoard FROM meritdata GROUP BY SubBoard)
GROUP BY fromID, SubBoard
ORDER BY SubBoard,total Desc) AS result
GROUP BY SubBoard
ORDER BY result.total Desc;
Find out the total merit awarded so far:
Code:
SELECT Sum(Merit) FROM MeritData;
Want the full history for a particular user?
Code:
SELECT * FROM MeritData WHERE toID=35 OR fromID=35 ORDER BY fromid,toid;
Jump to: