First venture into databases

shamrock122 · 27-07-2014 9:26pm #1

Hello,
Long time lurker, first time poster here. Some really great advice and tips on this forum by the way. In the past few months i have slowly been learning html and css as i have always had an interest in learning about web development. So far i am really enjoying it

. However, i would like to be able to create a website that is based on database info and i'm looking for advice about this. I realize that i am probably biting off more than i can chew but its something that i really want to learn about.

Basically, i want to create a sports statistics based website which i would like to look something like procyclingstats.com (cant post links as i'm a new user). note that i hope to create a soccer database for a local league, not a cycling one. so i envisage that every player has a page which would display stuff like age, height, team, scoring history, total yellow cards, total red cards etc etc. The same page would also display the players match history, showing opponents, venue, the minutes played in each game and stuff like that. On the site i linked to, it seems to me that there is a html template for each cyclists page and then this template is populated with data from a database. is this assumption correct?

Now this is where i am kind of clueless. I *think* that this would require a database of some sort. What type of database format would be best suited for this purpose? I have used ms access in the past but i'd imagine it would not be suitable to use for this. Would mysql be best? Also, what languages would i need to learn to be able to get database data to display on a webpage?

I have been thinking about the tables that i would use in the database and so far i have this:

Player_info table: PlayerID, TeamID, PlayerName Age, Height (+maybe a few more)
Team_info table: TeamID, TeamName, HomeVenue, LeagueWins
Match_table: MatchID, MatchDate, MatchVenue, HomeTeam, AwayTeam, Result
Playerstats: PlayerID, MatchID, Position, JerseyNumber, MinutesPlayed, GoalsScored

I presume that i could then create forms to make putting info into the database easier?

Aplogies for the long post but any advice to help me would be much appreciated.
As i said, im still only learning but i think this would be a nice project to start to keep me entertained for the next few months.

counterpointaud · 27-07-2014 11:49pm

Interesting project. You are doing the right thing IMO thinking about the data model first. Will all players have the same stats or do you want to support different players having a different set of stats ?

I would suggest a goals table which would have match_id and player_id as well as time and possibly other stats about the goal, maybe even a goal_type table with a code indicating the type of play leading to the goal in broad terms.

Might be nice to have a record of players involved in the set-up for a goal too.

How are you getting this data ?

shamrock122 · 28-07-2014 12:42pm

counterpointaud wrote: »

Interesting project. You are doing the right thing IMO thinking about the data model first. Will all players have the same stats or do you want to support different players having a different set of stats ?

I would suggest a goals table which would have match_id and player_id as well as time and possibly other stats about the goal, maybe even a goal_type table with a code indicating the type of play leading to the goal in broad terms.

Might be nice to have a record of players involved in the set-up for a goal too.

How are you getting this data ?

Thanks for the quick reply

i think initially all players will have the same stats (goals scored, red cards, yellow card etc) but down the line i might think about adding goalkeeper stats (e.g number of saves, cleansheets etc), and maybe a tackle stat for defenders and midfielders.

I'll be getting the data myself, there are only a few games on per week and i normally take notes on them anyway so it wont be too difficult to process hopefully.

I understand how to store this info in a ms access database but what puzzles me is how do i get the data from the database to display online on each players page? Will i have to run a databse query which will return all the player data from the database and then output the result in html format? Do you think ms access would be suitable for this? There are 12 teams in the league so that is about 200/250 players (roughly). I plan on building a html template and i then hope to autopopulate this template with data from the database if that makes sense.

Apologies for all the questions, as i said i am only begining to learn about using databases on websites

counterpointaud · 28-07-2014 12:52pm

You are probably going to have to use some sort of server side language / framework, are you using one already ? PHP, ASP.Net, Java, Ruby, Node etc. ?

padraig_f · 28-07-2014 3:19pm

shamrock122 wrote: »

I understand how to store this info in a ms access database but what puzzles me is how do i get the data from the database to display online on each players page? Will i have to run a databse query which will return all the player data from the database and then output the result in html format? Do you think ms access would be suitable for this? There are 12 teams in the league so that is about 200/250 players (roughly). I plan on building a html template and i then hope to autopopulate this template with data from the database if that makes sense.

Apologies for all the questions, as i said i am only begining to learn about using databases on websites

Some frameworks will help you out here. I like to use Python for this stuff, so Django and Flask are two options for that.

They'll provide a framework for what you want to do above...access the database, and render html pages using that data.

Both work with SQLite and MySQL (and probably PostgreSQL). SQLite is not a real production database, but is good enough and has benefits that it exists as a single file.

So you can work initially with SQLite and change over to MySQL or something for production. I wouldn't recommend MS Access, it's not production quality for a website and I'm not sure all the open source frameworks have access to it. For a file-based database, SQLite will be a better option. For server based, MySQL or PostgreSQL will be better.

Django tutorial

Flask tutorial

Buford T Justice · 28-07-2014 4:33pm

If its something you want publicly available to other individuals, then I'd suggest learning something like php and mySql. Host the db online, use php to query it and return the data to to a html page.

There'll probably be a few better suggestions than that one tho.

BrokenArrows · 28-07-2014 5:16pm

shamrock122 wrote: »

Player_info table: PlayerID, TeamID, PlayerName Age, Height (+maybe a few more)
Team_info table: TeamID, TeamName, HomeVenue, LeagueWins
Match_table: MatchID, MatchDate, MatchVenue, HomeTeam, AwayTeam, Result
Playerstats: PlayerID, MatchID, Position, JerseyNumber, MinutesPlayed, GoalsScored

I cant really advise on the technology you can use to access this information via html but I do a lot of work with databases.

So far your database layout is looking good. One thing I love to see is that Primary and Foreign key column names have very obvious links. (You would be supprised how many people give Primary/Foreign Key columns different names in different tables, drives me mental) In your case I assume your HomeTeam and AwayTeam columns would contain the ID's of those teams rather than the actual names.
If I was looking at the tables id like to see them called HomeTeamID and AwayTeamID to make it obvious that it can be linked to the TeamID column in the Team_Info table.

Edit: From a Data point of view you might also want a referee's table with a RefereeID and a link to that in the Match_Table. You might get to see a pattern of dodgy refereeing. lol.

shamrock122 · 29-07-2014 10:42am

padraig_f wrote: »

Some frameworks will help you out here. I like to use Python for this stuff, so Django and Flask are two options for that.

They'll provide a framework for what you want to do above...access the database, and render html pages using that data.

Both work with SQLite and MySQL (and probably PostgreSQL). SQLite is not a real production database, but is good enough and has benefits that it exists as a single file.

So you can work initially with SQLite and change over to MySQL or something for production. I wouldn't recommend MS Access, it's not production quality for a website and I'm not sure all the open source frameworks have access to it. For a file-based database, SQLite will be a better option. For server based, MySQL or PostgreSQL will be better.

Thanks for this. As you suggested, i think it is better to do this correctly with mysql rather than using access which would probably not work down the line. A good start is half the battle and all that.

Buford T Justice wrote: »

If its something you want publicly available to other individuals, then I'd suggest learning something like php and mySql. Host the db online, use php to query it and return the data to to a html page.

There'll probably be a few better suggestions than that one tho.

Ya i hope to have this available online so that players/managers/supporters etc can look it up to see how their team is doing. My plan of action is to build a mysql database with info for just a few games first and then after that to test it out with php. If everything works i will then move on to full scale

BrokenArrows wrote: »

I cant really advise on the technology you can use to access this information via html but I do a lot of work with databases.

So far your database layout is looking good. One thing I love to see is that Primary and Foreign key column names have very obvious links. (You would be supprised how many people give Primary/Foreign Key columns different names in different tables, drives me mental) In your case I assume your HomeTeam and AwayTeam columns would contain the ID's of those teams rather than the actual names.
If I was looking at the tables id like to see them called HomeTeamID and AwayTeamID to make it obvious that it can be linked to the TeamID column in the Team_Info table.

Edit: From a Data point of view you might also want a referee's table with a RefereeID and a link to that in the Match_Table. You might get to see a pattern of dodgy refereeing. lol.

Thanks for this, good to hear that i am starting correctly as this is my very first attempt at using databases. I want to keep things as simple as possible so i am giving everything really simple names to avoid problems in the future. Ya, HomeTeam and AwayTeam should be named "HomeTeamID" and "AwayTeamID" as you suggested, I'll change that now. Good suggestion to add a referee table, may be useful to see if their is any biased referees in the league!

amen · 29-07-2014 1:42pm

From the technology side, you should also consider MS SQL Express and .Net (Maybe a MVC approach would suit you)

They are free and there are loads of beginner articles available. The tools are integrated in a really nice IDE with full debugger support.

Re you data model there a few things you should change:

1: Player_Info table. Remove the TeamID. Each player will play for different teams during their career
2: I would add a PlayerType to the Player_Infor table (Goalkeeper,striker etc). A new lookup table should be created for the PlayerTypes.
3: Create a new PlayerTeamInfo table which has PlayerID,TeamID,ContractStart,ContractEnd columns. This table relates the player to the teams he has played on
4:PlayerStats should have unique primary key, PlayerStatsID

Player_info table: PlayerID, TeamID, PlayerName Age, Height (+maybe a few more)
Team_info table: TeamID, TeamName, HomeVenue, LeagueWins
Match_table: MatchID, MatchDate, MatchVenue, HomeTeam, AwayTeam, Result
Playerstats: PlayerID, MatchID, Position, JerseyNumber, MinutesPlayed, GoalsScored

The Corinthian · 30-07-2014 10:08am

shamrock122 wrote: »

Now this is where i am kind of clueless. I *think* that this would require a database of some sort. What type of database format would be best suited for this purpose? I have used ms access in the past but i'd imagine it would not be suitable to use for this. Would mysql be best? Also, what languages would i need to learn to be able to get database data to display on a webpage?

You have various options, with various Web language-database combos. MySQL is a popular database for sub-enterprise level Web sites.

I would recommend though getting an admin interface for whatever database you choose, as much of database development is understanding normalization, indexing, constraints, triggers and so on - not to mention SQL - and such interfaces make it easier to interact with them as you're developing and/or learning.

Also avoid frameworks as they specifically are designed to hide a lot of the database interactions, which you don't want if you want to learn something.

Match_table: MatchID, MatchDate, MatchVenue, HomeTeam, AwayTeam, Result

Just a suggestion; add a Venue_table: VenueID, VenueName, VenueTeamID.

This way, you could then change your Match_table so that MatchVenue becomes MatchVenueID and do away with the need to specify home or away teams as the former can be identified via the Venue table.

I presume that i could then create forms to make putting info into the database easier?

Yes. This can be platform/language dependent and even then it's a whole topic onto itself.

Aplogies for the long post but any advice to help me would be much appreciated.

A long, well thought out post is a lot better than a short, vague question that lacked any attempt at prior research

amen wrote: »

From the technology side, you should also consider MS SQL Express and .Net (Maybe a MVC approach would suit you)

+1

1: Player_Info table. Remove the TeamID. Each player will play for different teams during their career

The importance of analysis when designing a database. QED.

shamrock122 · 30-07-2014 2:02pm

The Corinthian wrote: »

Just a suggestion; add a Venue_table: VenueID, VenueName, VenueTeamID.

This way, you could then change your Match_table so that MatchVenue becomes MatchVenueID and do away with the need to specify home or away teams as the former can be identified via the Venue table.

I was originally thinking of doing this but then i remembered that some games are played at neutral venues (league final/playoffs for example). So i created a slight variation on your suggestion above. I created a Venue table with just VenueID and VenueName. I ommited VenueTeamID for the case where games are played in a neutral venue.

I got started using mysql workbench yesterday and almost have the database set up.

The interface is quite different to ms access and some tools which were in access dont appear to be in mysql workbench. One thing in particular that i miss is the lookup wizard that came with access which allowed me to create drop down menus of the options available as foreign keys when filling out forms.

Google tells me that i can do this with php but i haven't got that far yet

shamrock122 · 01-08-2014 5:00pm

Just to update people on my progress: i started running a few querys on the database today after i put in some data for two of the teams. It was only basic querys for stuff like top goalscorer/most yellow cards etc but i can already see that databases are a much more efficient way of storing data compared to logging it all in excel or some other format.

Cant wait to get stuck into php now so that i will be able to display it online

Thanks to everyone who has helped so far

shamrock122 · 27-08-2014 11:12am

Hi Lads,
I have been working away at this for the last few weeks and things have been going reasonably well. However, i am now trying to deal with the issue of sql injection.
In the PHP code i am using, i use the GET function to get the page id e.g. ww.example.com/team.php?id=20. I then use this in my sql queries.

However, i have been reading about sql injection and I am struggling to understand how to prevent it when using the GET function. I was using mysql_escape_string but according to google it is now an outdated way of preventing sql injection? Then i started reading about paramaterized queries and it all got a bit confusing at that point.

Anyway, here is the code i am using at the moment:
$pageid = $_GET;
$pageid = mysql_escape_string($pageid);

$playerdetails = mysqli_query($connect,"SELECT playerdetails.playername, playerid, position, club, clubdetails.clubName from playerdetails
inner join clubdetails on playerdetails.clubid=clubdetails.clubid where playerdetails.playerid=$pageid")

i then output the results into a table.
Could anyone tell me if this is vunerable to sql injection?

Note that the page id will always be an integer so i was thinking, is it possible to just limit the GET function to select an integer and if the id is not an integer then it returns an error e.g using ww.example.com/team.php?id=20 it will select 20 but if the address was ww.example.com/team.php?id=1=1 then it would return an error?

Steviemoyne · 27-08-2014 11:27am

I don't know a whole lot about databases, just enough to get by, but at the moment I'm dealing with a c# application that stores data in a microsoft sql database. The database has a gigantic amount of stored procedures which when combined with paramterized queries prevents sql injection.

Following website probably explains it better than I could: http://blogs.msdn.com/b/brian_swan/archive/2011/02/16/do-stored-procedures-protect-against-sql-injection.aspx

counterpointaud · 27-08-2014 11:50am

shamrock122 wrote: »

Hi Lads,
I have been working away at this for the last few weeks and things have been going reasonably well. However, i am now trying to deal with the issue of sql injection.
In the PHP code i am using, i use the GET function to get the page id e.g. ww.example.com/team.php?id=20. I then use this in my sql queries.

However, i have been reading about sql injection and I am struggling to understand how to prevent it when using the GET function. I was using mysql_escape_string but according to google it is now an outdated way of preventing sql injection? Then i started reading about paramaterized queries and it all got a bit confusing at that point.

Anyway, here is the code i am using at the moment:
$pageid = $_GET;
$pageid = mysql_escape_string($pageid);

$playerdetails = mysqli_query($connect,"SELECT playerdetails.playername, playerid, position, club, clubdetails.clubName from playerdetails
inner join clubdetails on playerdetails.clubid=clubdetails.clubid where playerdetails.playerid=$pageid")

i then output the results into a table.
Could anyone tell me if this is vunerable to sql injection?

Note that the page id will always be an integer so i was thinking, is it possible to just limit the GET function to select an integer and if the id is not an integer then it returns an error e.g using ww.example.com/team.php?id=20 it will select 20 but if the address was ww.example.com/team.php?id=1=1 then it would return an error?

Prepared statements (parameterised queries) are better practice. Basically it allows the skeleton of the query to be presented to the database first, with placeholders for the variables, so an attacker cannot fool the system into thinking their string inputs are SQL.

amen · 27-08-2014 1:15pm

Apart from sql injection you have a different issue; using ww.example.com/team.php?id=20

what is to stop someone figuring out your numbering system and entering various different urls and thus retrieving data that may not be theirs
ww.example.com/team.php?id=20
ww.example.com/team.php?id=21
ww.example.com/team.php?id=50

etc

shamrock122 · 27-08-2014 2:45pm

amen wrote: »

Apart from sql injection you have a different issue; using ww.example.com/team.php?id=20

what is to stop someone figuring out your numbering system and entering various different urls and thus retrieving data that may not be theirs
ww.example.com/team.php?id=20
ww.example.com/team.php?id=21
ww.example.com/team.php?id=50

etc

Thanks for the reply

I think i get what you are saying but i dont really see what the problem is? If the user changes the number all that will happen is that they will see the stats for a different team. The website will be free to access so i dont see the issue? The numbers only go up to 20 so if a bigger number is entered then nothing shows up
Apologies if i am missing something obvious here

Buford T Justice · 28-08-2014 1:37am

shamrock122 wrote: »

Hi Lads,
I have been working away at this for the last few weeks and things have been going reasonably well. However, i am now trying to deal with the issue of sql injection.
In the PHP code i am using, i use the GET function to get the page id e.g. ww.example.com/team.php?id=20. I then use this in my sql queries.

However, i have been reading about sql injection and I am struggling to understand how to prevent it when using the GET function. I was using mysql_escape_string but according to google it is now an outdated way of preventing sql injection? Then i started reading about paramaterized queries and it all got a bit confusing at that point.

Anyway, here is the code i am using at the moment:
$pageid = $_GET;
$pageid = mysql_escape_string($pageid);

$playerdetails = mysqli_query($connect,"SELECT playerdetails.playername, playerid, position, club, clubdetails.clubName from playerdetails
inner join clubdetails on playerdetails.clubid=clubdetails.clubid where playerdetails.playerid=$pageid")

i then output the results into a table.
Could anyone tell me if this is vunerable to sql injection?

Note that the page id will always be an integer so i was thinking, is it possible to just limit the GET function to select an integer and if the id is not an integer then it returns an error e.g using ww.example.com/team.php?id=20 it will select 20 but if the address was ww.example.com/team.php?id=1=1 then it would return an error?

Firstly, you can change the method of the form to POST instead of GET, which will remove the integer from being passed as part of the url.
instead of loading variables from the _$GET array:

$page_id = $_GET['id'];

you load them from the $_POST array.

$page_id = $_POST['id'];

and the URL will remain as ww.example.com/team.php regardless of the page selected. I'm assuming these page number submissions will be generated by the page itself, and not via user submission?

If you are using data from a form to submit to a database like a name or something like that, then they would be vulnerable to an SQL injection.
In that case you would want to go down the road of prepared statements and paramaterised queries to prevent malicious SQL injections.

Have a look at this post.

I would also recommend reading something like PHP & MySql - Novice to Ninja. It covers all of this quite well

The Corinthian · 28-08-2014 9:30am

This is better:

$page_id = isset($_POST['id']) ? (int) $_POST['id'] : 0;

And handle invalid requests, where $page_id is zero, accordingly.

oscarBravo · 28-08-2014 12:54pm

Buford T Justice wrote: »

Firstly, you can change the method of the form to POST instead of GET, which will remove the integer from being passed as part of the url.

I'm not sure I'd agree. I've trained myself to use POST requests for methods that modify data, and GET requests for methods that simply retrieve it.

One reason that GET methods can be useful is that it allows (say) sending someone a link to a page for a specific team - ww.example.com/team.php?id=20 - whereas that can't be done with a POST method.

Buford T Justice · 28-08-2014 1:17pm

oscarBravo wrote: »

I'm not sure I'd agree. I've trained myself to use POST requests for methods that modify data, and GET requests for methods that simply retrieve it.

One reason that GET methods can be useful is that it allows (say) sending someone a link to a page for a specific team - ww.example.com/team.php?id=20 - whereas that can't be done with a POST method.

Fair point, the user wouldn't be able to bookmark a specific page then.

I'd rekon the route to go down would be prepared Statements then.

oscarBravo · 28-08-2014 1:20pm

Buford T Justice wrote: »

I'd rekon the route to go down would be prepared Statements then.

Yeah, that's a route I'd go down anyway. Using a POST method doesn't prevent someone sending you dodgy data, it just makes it marginally less straightforward to do so.

The Corinthian · 28-08-2014 1:27pm

oscarBravo wrote: »

One reason that GET methods can be useful is that it allows (say) sending someone a link to a page for a specific team - ww.example.com/team.php?id=20 - whereas that can't be done with a POST method.

Actually, after I wrote that line of code above, I was thought that.

Why would you want to hide the 'page id' from view anyway? Even if you send it as a HTTP POST, you're not really hiding it from anyone who's actively looking for it. About the only reason I can think of is that you want to stop people harvesting your content, but if they've bots to do this, using a POST instead of a GET won't present much of an obstacle to them.

amen · 28-08-2014 2:28pm

If you are going the route of prepared statements you should still check the input to the prepared statements on the server side.

Never trust anything from the client.

TonyStark · 28-08-2014 5:51pm

Personally I use SQLMAP for helping to guard against very common vuberabilities. It does require a python install but we'll worth it and exploring it as a tool to proof your approach and pretty much guard against some of the more boiler plate attacks!

justjustin · 29-08-2014 9:11am

I'd stick with PHP and get the basics but really I think some kind of framework would be more suitable. I've started using Laravel a little myself and find it great and very quick to get prototypes/small apps up and running in no time.

I can't recommend it highly enough and will work with any database type you throw at it, no trouble at all getting MySQL or MSSQL Server playing nicely with it either. I think it's good to know some PHP but, in reality, it's a bit of a mess of a language and once you get comfortable with an MVC framework I don't think you'll look back.

Journeyman_1 · 02-09-2014 4:58pm

This is a bit late but anyway.

mysql_real_escape_string() is outdated, but you can use mysqli_real_escape_string();

in your code it should look something like:

$pageid = mysqli_real_escape_string($connect, $pageid);

The line you have before that is also a little redundant, i'd put it all in one line mostly:

$pageid = mysqli_real_escape_string($connect, $_GET['id']);

I also usually use $_POST[] for everything, but GET should be fine.

oscarBravo · 02-09-2014 6:08pm

Journeyman_1 wrote: »

I also usually use $_POST[] for everything...

How do you deal with the bookmarking problem discussed earlier?

Journeyman_1 · 02-09-2014 6:40pm

oscarBravo wrote: »

How do you deal with the bookmarking problem discussed earlier?

Well, tbh, I only skimmed the thread before posting and didn't see anybody say anything about mysqli_r_e_s(); so I wanted to suggest it. I completely forgot about that issue. In any of the (admittedly few) projects i've done, users were always submitting data to be stored in a DB, so using GET didn't make sense.

shamrock122 · 04-09-2014 12:17pm

wow, i have just seen all the replies to the thread.
all i can say is thanks a million to everyone who replied, a really brilliant source of help for a noob like me!

ChRoMe · 05-09-2014 12:18pm

This thread is a magnificent example of how rubbish PHP is.

First venture into databases

Comments