SQLite

If you need help building your website, this is the place to hang out. If you're an HTML, PHP, Perl or web database geek, check it out. / Si vous avez besoin d'aide pour construire votre site web, c'est le forum à regarder! Si vous êtes un expert en HTML, PHP, Perl ou en base de données, c'est le forum pour vous!
Foo
The SuperStar
Posts: 1566
Joined: Thu Dec 29, 2005 12:56 pm

Re: SQLite

Post by Foo »

I tested it, looks great so far and it is fast. I have 2 questions:

what's the difference with those tables : ProTeamLines and ProTeamLinesNumberonly ??

in the column StandingPlayoffTitle of ProTeamStats, will it enter the Wild Card in here since beginning of the season? if not, it would be much easier for us to construct a wild card standing page with a column that have those information in, no need for division name in it. Something like this:

C1D1P1
C1D1P2
C1D1P3
C1D2P1
C1D2P2
C1D2P3
C1WC1
C1WC2

C for Conference, D for Division and P for Position in the division. After that, very easy to do a wild card standing page. Thanks.
Image
Image
Image
SimonT
STHS Owner / Propriétaire du STHS
Posts: 14758
Joined: Sat Oct 08, 2005 4:18 pm
Location: Montreal, Canada
Contact:

Re: SQLite

Post by SimonT »

TeamProLinesNumberOnly is all number and this is now I keep in the STHS. This table might get use in the future if we build a STHS Web Client.
TeamProLines is the same value but instead of forcing you to multiple lookup in the PlayerInfo table to get the player name, I write the name directly. This way, you can show Team Lines page with 1 SQL query.

The StandingPlayoffTitle only show the E, X, Y, and Z title. If you want to build the ranking order, you need to check the Ranking Order table.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
Foo
The SuperStar
Posts: 1566
Joined: Thu Dec 29, 2005 12:56 pm

Re: SQLite

Post by Foo »

TeamProLineNumberOnly you mean it shows player id instead of names?

The RankingOrder Table is very low in information. Assuming TeamOrder is a number from 1 to 30? I need more stuff to build someting like this
http://www.nhl.com/ice/standings.htm?ty ... v-stn-conf

Like teamOwnDivision ranking and teamOwnConference ranking. Now they are overall from 1 to 30.
Image
Image
Image
RPHALabs
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Thu Sep 11, 2014 2:42 pm

Re: SQLite

Post by RPHALabs »

I disagree, there are numerous ways to use subqueries to get the ranking system you want. I would do something like this, I am sure you can modify this to do whatever you need:

Code: Select all

SELECT i.Name, r.TeamOrder AS OverallOrder, 
(
	SELECT COUNT(*) 
	FROM TeamProInfo AS i2 
	LEFT JOIN RankingOrder AS r2 ON r2.TeamProNumber = i2.Number 
	WHERE  r2.TeamOrder < r.TeamOrder 
	AND i2.Division = i.Division
) + 1 AS DivisionRank,  
(
	SELECT COUNT(*) 
	FROM TeamProInfo AS i2 
	LEFT JOIN RankingOrder AS r2 ON r2.TeamProNumber = i2.Number 
	WHERE  r2.TeamOrder < r.TeamOrder 
	AND i2.Conference = i.Conference
) + 1 AS ConferenceRank  
FROM TeamProInfo AS i 
LEFT JOIN RankingOrder AS r ON r.TeamProNumber = i.Number
ORDER BY i.Conference, i.Division, DivisionRank
Foo
The SuperStar
Posts: 1566
Joined: Thu Dec 29, 2005 12:56 pm

Re: SQLite

Post by Foo »

I did not say it was impossible, it is more work for nothing. We already have a table for that, why not add some easy info in it to have a simpler code.
Image
Image
Image
DaGreat1
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Mon May 09, 2011 12:29 pm

Re: SQLite

Post by DaGreat1 »

Found something in the PlayerInfo, I don't know if it has been reported yet, but injury condition (column Condition) is missing the decimal.

It's not that big of a deal, but i'm using decimals in my fatigue settings and I think, in a programmer point of view, I think it could be real quick fix to adjust for the next version (though I haven't seen your code, so I just assume :P )

Great addition by the way! Love it!

Thanks
SimonT
STHS Owner / Propriétaire du STHS
Posts: 14758
Joined: Sat Oct 08, 2005 4:18 pm
Location: Montreal, Canada
Contact:

Re: SQLite

Post by SimonT »

For the Condition column, I always output the full integer number in the CSV, XML so I continue with it in SQLite. In 2.3.2, I had a new column call ConditionDecimal with Data Type of Real.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
DaGreat1
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Mon May 09, 2011 12:29 pm

Re: SQLite

Post by DaGreat1 »

Is the Team News available somewhere? I looked in the CSV, XML and SQLite and didn't find it anywhere.

Would be great if it was in the SQLite

Thanks
Foo
The SuperStar
Posts: 1566
Joined: Thu Dec 29, 2005 12:56 pm

Re: SQLite

Post by Foo »

Also, free agent offers that can be stored in a CSV in the Automatic Free Agent window would be a nice add in SQLite.
Image
Image
Image
SimonT
STHS Owner / Propriétaire du STHS
Posts: 14758
Joined: Sat Oct 08, 2005 4:18 pm
Location: Montreal, Canada
Contact:

Re: SQLite

Post by SimonT »

I'll check what I can add in the next version.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
SimonT
STHS Owner / Propriétaire du STHS
Posts: 14758
Joined: Sat Oct 08, 2005 4:18 pm
Location: Montreal, Canada
Contact:

Re: SQLite

Post by SimonT »

I just add TeamNews in the SQLite.
For the free agents, it's tricky to add in there because everyone will be able to see them. It's fairly easy to download the SQLite file and read all the offers in there. I find this dangerous.
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
JimToupet
STHS Dynamic Website Contributor
Posts: 363
Joined: Wed Jul 02, 2014 10:55 am

Re: SQLite

Post by JimToupet »

SimonT wrote:I just add TeamNews in the SQLite.
For the free agents, it's tricky to add in there because everyone will be able to see them. It's fairly easy to download the SQLite file and read all the offers in there. I find this dangerous.
You can prevent download with a .htaccess file.
RPHALabs
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Thu Sep 11, 2014 2:42 pm

Re: SQLite

Post by RPHALabs »

Hey Simon, Trying to use the LeagueLog table in 2.3.1 and the entire table is empty. There is not one record in the table at all. Is this a bug of some sort? Am I missing something? I do see a change in the table schema as you have the color and text separated in the fields, but no records?
SimonT
STHS Owner / Propriétaire du STHS
Posts: 14758
Joined: Sat Oct 08, 2005 4:18 pm
Location: Montreal, Canada
Contact:

Re: SQLite

Post by SimonT »

How does your transaction html look like? It's empty or not?
-SimonT
Forum Administrator / Administrateur du Forum
STHS Owner / Propriètaire du STHS
English V2 & V3 Manual - Manuel V2 & V3 Français
RPHALabs
New in Town / Le Ptit Nouveau
Posts: 36
Joined: Thu Sep 11, 2014 2:42 pm

Re: SQLite

Post by RPHALabs »

HTML Transactions is populated - http://anhs.qc.ca/output_saison/ANHS-Transactions.html
CSV Transactions is populated - http://anhs.qc.ca/output_saison/csvxml/ ... action.csv
SQLite Table empty
Post Reply