mlb php/sql database

**Maverick22** · 07-12-10, 06:25 PM

I wrote this really long response, and it didnt go through. The shorter version is this:

Visually it looks good. I would reword most of the shorthand. For any listing of a team. Put the whole team name. "Kansas City Royals" as opposed to "Kansas City" or "Royals". You dont consistently do this across the site. There are some small spelling errors. And some of the tables arent fully populating the dates. List the game numbers along the left hand site. (1,2,3,etc)

Functionality wise its pretty sound. I will ping you (if i forget, remind me) a list of programs that do what you have done and more. We do similar stuff at work. And i might know some applications you may appreciate.

You could add the ability to "filter" out games backed on the total/moneyline/spread. For example games where a particular team was a heavy favorite/dog. Or the total was really high/low. So the query would be like "how many units would i have won betting the phillies on games where they were no more than a -150 favorite.

take the Visitor Locations graphic off the main page. its cheesy. Make another page and put it there. I like it...but it shouldnt be on the main page

Also for every page, give a small one-two short description of what it does. Either on each page or some summary/faq page somewhere else.

Some of the team abbreviations are non standard. And they also give away where you got your data (if that matters)

I would resolve team abbreviates like "SLN" to "STL", and "NYM" to "NYN". I would likely go with whatever ESPN uses. This shouldnt be too hard, just a lookup function

(This is a not so important fix, just a small change for when you are bored)

Overall, I am very impressed with what you have done.

Also, you note "SQL" database... Which one?

**Maverick22** · 07-12-10, 06:34 PM

Oh yea... a cool add...

Seeing how many units could have been won doing multi team parlays... IE "how many units would I have won, betting x units on the Phillies, Padres, and Rangers" for every game day on a given season... And in the math/calculations when all the teams dont play, calculate it based on the teams that did. That would be pretty sweet too.

**gohabsgo** · 07-12-10, 08:00 PM

Originally posted by Maverick22

I wrote this really long response, and it didnt go through. The shorter version is this:

Visually it looks good. I would reword most of the shorthand. For any listing of a team. Put the whole team name. "Kansas City Royals" as opposed to "Kansas City" or "Royals". You dont consistently do this across the site. There are some small spelling errors. And some of the tables arent fully populating the dates. List the game numbers along the left hand site. (1,2,3,etc)

Functionality wise its pretty sound. I will ping you (if i forget, remind me) a list of programs that do what you have done and more. We do similar stuff at work. And i might know some applications you may appreciate.

You could add the ability to "filter" out games backed on the total/moneyline/spread. For example games where a particular team was a heavy favorite/dog. Or the total was really high/low. So the query would be like "how many units would i have won betting the phillies on games where they were no more than a -150 favorite.

take the Visitor Locations graphic off the main page. its cheesy. Make another page and put it there. I like it...but it shouldnt be on the main page

Also for every page, give a small one-two short description of what it does. Either on each page or some summary/faq page somewhere else.

Some of the team abbreviations are non standard. And they also give away where you got your data (if that matters)

I would resolve team abbreviates like "SLN" to "STL", and "NYM" to "NYN". I would likely go with whatever ESPN uses. This shouldnt be too hard, just a lookup function

(This is a not so important fix, just a small change for when you are bored)

Overall, I am very impressed with what you have done.

Also, you note "SQL" database... Which one?

Thanks for the reply i'll look int all this. I'm thinking about making queries to see how a specific team did against left or right handed pitchers.

To answer your question I purchased the database in excel format and converted it in a mysql database.

**Grind-It-Out** · 07-12-10, 08:07 PM

Use some CSS (or at least some tables) for the forms.

Get rid of the Units Per Bet field. It's a waste of a click. Just base everything off of 1 unit.

Allow more filtering options. Home favorite or home dog is pretty vague. How about "home team with line between -120 and +110."

Needs to be more user friendly.

Needs to have more useful data.

**gohabsgo** · 07-12-10, 08:19 PM

Originally posted by Grind-It-Out

Use some CSS (or at least some tables) for the forms.

Get rid of the Units Per Bet field. It's a waste of a click. Just base everything off of 1 unit.

Allow more filtering options. Home favorite or home dog is pretty vague. How about "home team with line between -120 and +110."

Needs to be more user friendly.

Needs to have more useful data.

Ok thanks i'll work on this. What do you mean by more usefull data? Beeing able to make more queries in the database? Feel free to suggest.

**Grind-It-Out** · 07-12-10, 08:35 PM

Originally posted by gohabsgo

Ok thanks i'll work on this. What do you mean by more usefull data? Beeing able to make more queries in the database? Feel free to suggest.

I guess what I mean is that instead of focusing on what you CAN show from your database, focus on what you SHOULD show.

Spend some time thinking of some tools that would be really helpful to YOU, because those same tools will probably also be helpful to others. If that means creating new tools that you don't yet have, and requires data that you don't yet have, then that gives you something to work towards.

For example, I really wanted to add a line history section to my website. While I do believe that line history is useful, I also recognize that a bunch of other sites (including SBR) have covered it really well. So, I plan on adding a Scalping section instead.

You only have so much time on your hands, so focus on the most unique, most useful things first.

**Grind-It-Out** · 07-12-10, 08:45 PM

Okay, finally a real suggestion lol.

As long as your database contains at least some useful information (which I can tell it does by what you are displaying on your site), I think it would be AWESOME if you could enter your own queries. If you could create an interface almost like PHPmyAdmin or something, and allow the user to see every aspect of the database and make their own queries, that would be sweet, and I would use it, even if it was only to dick around.

If you did this, though, you'd have to implement EXTREME security checks to safeguard yourself from unwanted SQL injections.

Also, if you don't want the average joe to steal your data, you could print it to an image, and display the image so it can't be copied and pasted.

**gohabsgo** · 07-12-10, 08:55 PM

I created this website to be able to make queries in a baseball database without the user having to write in a pseudo sql language like on this site http://sportsdatabase.com/, whitch isn't very user friendly.
I'll give an exemple of another query that i might add. Show units won/lost when team X is playing left or right handed pitcher.
Grind-it-out, thanks for the advice. I won't be creating an interface phpmyadmin like, i haven't got my life to put on this project. This project is a hobby.

I also recognize that a bunch of other sites (including SBR) have covered it really well

I agree that the data is the same but show me a mlb database that you can make queries into it and that is user friendly.
I don't think the data is the issue here, it is about how to manipulate it.

**gohabsgo** · 07-12-10, 09:00 PM

Originally posted by Grind-It-Out

Okay, finally a real suggestion lol.

As long as your database contains at least some useful information (which I can tell it does by what you are displaying on your site), I think it would be AWESOME if you could enter your own queries. If you could create an interface almost like PHPmyAdmin or something, and allow the user to see every aspect of the database and make their own queries, that would be sweet, and I would use it, even if it was only to dick around.

If you did this, though, you'd have to implement EXTREME security checks to safeguard yourself from unwanted SQL injections.

Also, if you don't want the average joe to steal your data, you could print it to an image, and display the image so it can't be copied and pasted.

If you guys tell me all the queries that would be usefull, i'll program them in php/sql and it will be on the web site soon.

That why i started this thread, sort of a focus group

**Grind-It-Out** · 07-12-10, 09:02 PM

Originally posted by gohabsgo

If you guys tell me all the queries that would be usefull, i'll program them in php/sql and it will be on the web site soon.

That why i started this thread, sort of a focus group

It's impossible to request queries without knowing your database structure.

If you post the CREATE table syntax for each of your tables we'll know better what you're working with.

**gohabsgo** · 07-12-10, 09:02 PM

If you did this, though, you'd have to implement EXTREME security checks to safeguard yourself from unwanted SQL injections.

Thats is why i didn't allow the user to enter lines intervals in this version of the site. SQL injections...

**Grind-It-Out** · 07-12-10, 09:04 PM

Originally posted by gohabsgo

Thats is why i didn't allow the user to enter lines intervals in this version of the site. SQL injections...

You can use mysql_real_escape_string() string to very safely guard against injections in that case.

**gohabsgo** · 07-12-10, 09:07 PM

Originally posted by Grind-It-Out

It's impossible to request queries without knowing your database structure.

If you post the CREATE table syntax for each of your tables we'll know better what you're working with.

Pretty simple database: 1 table 10 fields: Date, Away team, home team, away score, home score, away pitcher, home pitcher, home line, away line, O/U, home pitcher throwing hand, away pitcher throwing hand.

**gohabsgo** · 07-12-10, 09:08 PM

Originally posted by Grind-It-Out

You can use mysql_real_escape_string() string to very safely guard against injections in that case.

Thanks!

**Grind-It-Out** · 07-12-10, 09:30 PM

One thing I can think of is trying to find a correlation between moneylines and totals. ie, if certain teams tend to go UNDER when they win, then a parlay of the team and UNDER could be very successful.

very simple, untested query:
SELECT count(a_score + h_score > total) as overs, count(a_score + h_score < total) as unders FROM (SELECT * FROM table where (home_team = 'Texas Rangers' AND h_score > a_score) OR (away_team = 'Texas Rangers' AND a_score > h_score)) as query

That would tell you something like,

In the Rangers 84 wins on the season, 51 of the games have gone over and 33 of the games have gone under.

**gohabsgo** · 07-12-10, 09:33 PM

Originally posted by Grind-It-Out

One thing I can think of is trying to find a correlation between moneylines and totals. ie, if certain teams tend to go UNDER when they win, then a parlay of the team and UNDER could be very successful.

very simple, untested query:
SELECT count(a_score + h_score > total) as overs, count(a_score + h_score < total) as unders FROM (SELECT * FROM table where (home_team = 'Texas Rangers' AND h_score > a_score) OR (away_team = 'Texas Rangers' AND a_score > h_score)) as query

That would tell you something like,

In the Rangers 84 wins on the season, 51 of the games have gone over and 33 of the games have gone under.

Good idea, i'll program this soon

**gohabsgo** · 07-13-10, 06:03 PM

Originally posted by Grind-It-Out

Allow more filtering options. Home favorite or home dog is pretty vague. How about "home team with line between -120 and +110."

Changes made, it's now possible to see how many units would have been won or lost betting on a team when a team odds are between a range that the user choose.

Here's the link:

http://mlbdb.com/index8.html

**Grind-It-Out** · 07-13-10, 06:54 PM

Nice.

How are the teams ordered in the select boxes? It appears random to me, but maybe I'm missing something. I think sorting them alphabetically would be helpful.

**gohabsgo** · 07-13-10, 07:24 PM

Originally posted by Grind-It-Out

Nice.

How are the teams ordered in the select boxes? It appears random to me, but maybe I'm missing something. I think sorting them alphabetically would be helpful.

Thanks!
Will work on it little later...
Got other stuff to do now

**gohabsgo** · 07-13-10, 09:14 PM

Originally posted by Grind-It-Out

Nice.

How are the teams ordered in the select boxes? It appears random to me, but maybe I'm missing something. I think sorting them alphabetically would be helpful.

Ok it's in alphabetical order now, should less of pain to use the tool...
I'm gonna start working on what you were talking about yesterday, correlation between wins and over/under for individual teams for potential profitable parlays.