Thread

I have a pet project I’m working on related to a game in my office. I’ve got a site running on EE, and an iPhone webapp made (using a SAEF) so that as soon as we’re done with a game, I can enter the winner/loser, shots and score and post that to the site right from the iPhone.

I’m finding sports statistics to be tougher query-wise than I thought, in some cases 😊. I’m looking to do things like:

- hot player (player with the longest current winning streak)
- cold player (player with the longest current losing streak)
- Winning percentages (against other players, or in total)
- Other percentages (offensive percentage, defense percentage)
- Various averages (avg shots per game, avg goals per game)
- Eventually, would love to group games into “tournaments” (best of 7)

- will eventually put people in different user groups based on total number of games played

Has anyone else done a site that requires any of these types of stats, or come across any resources for this sort of thing that could be applied to EE? (PHP tournament scripts or any ideas on how to structure a tournament using weblogs, categories or any other method welcome here).

I’m fishing for resources right now - and if someone is a real SQL guru, maybe you should PM me as I’m willing to shell out a little for some quality help (again, PM about that so as to keep that conversation out of the forums…). I have some of the more basic queries worked out - but some of the more complicated averages, winning streaks, and the idea of tournaments are boggling my mind.

Thanks for any help or suggestions!

I would do a module for it honestly.  For one, you can’t work on the data right from EE.  As you tweeted, everything in the DB is a string, so you have to do some PHP in the templates to convert the numbers to integers as they are coming out.

I’m sure you could eventually wrangle EE to do what you want with the Weblog module but that requires too much brainpower for a forum post.  😉  I like my news digestible in 140 characters (yay Twitter) and my forum problems easy to solve.

Why not use this as an opportunity to screw around with CodeIgniter if you haven’t already?  Though you could do anything in EE that you could do with CI, I feel like CodeIgniter is better for quick and dirty coding because you don’t have to work around the EE API.

I am no guru or even a student when it comes to php but perhaps title and this article would give some ideas on php+mySQL and having database do the work while php the output….for what is worth.
All the best!

@John: I do have a feeling this type of site and these type of queries would be easier to work with with CI and/or EE 2.0. I think the two things most problematic for me are:

1) the fact that the data I need to work with isn’t really integers (though I could go in and change the type for that custom field) and:

2) I simply don’t know how to do some of these tougher queries. (Sample: Who is hottest player? Select every player who is in the member group 1, 2, or 3 whose last game was a ‘win’ (which may be a calculation in itself…) and then um, count every win all the way back to the first time you come to a loss for each of the selected players, then take that total count and order the results descending, limit 1.) Something like that - and I imagine that’s one of the easier queries. What about if I want to find out my average shots per game against a certain player, but only in games that I lost as the red team and were played in August 2008? (*cue sound of my brain breaking in half*).I really think the data is simple (all of the sample stuff I just proposed could be done…we log games as weblog entries) but the queries are tricky.

I would like to get a little time in with CodeIgniter…but I do know my strengths and weaknesses. If I could elucidate what I’m trying to achieve to someone who already knows CI, I’d be happier paying them for the help and being done with it than struggling for hours with that sort of monkey business - I’m a CSS/HTML guy, and I could use those hours to get better at my core stuff.

@lebisol: Thanks for the input. I do think this site would benefit from having the DB do query work behind the scenes, especially on nested(?) queries that need to be used over and over.

I would do a module for it honestly.

John, in suggesting this, are you suggesting that there might be an additional (more easily-queried) table created when the module is installed, in addition to the weblog data table, that I could insert data into when I submit a finished game?

Email sent.

I would do a module for it honestly.

John, in suggesting this, are you suggesting that there might be an additional (more easily-queried) table created when the module is installed, in addition to the weblog data table, that I could insert data into when I submit a finished game?

Ok, I will answer your question here too though…

I think created tables just for the purpose of this game would be helpful.  Of course, you could also create actual number data types for the data that needs them.  You could also hide away all the PHP needed to do the calculations in the module.  And then you can also create a control panel interface if needed. 

You could achieve approximately the same with weblog entries but it would be quite hackish.  You would need to convert the data and then do the operations on that data as it gets generated by the weblog tags.  Of course, there is also the problem that you are trying to stuff the weblog into doing something it really wasn’t designed to do.  At some point, it just gets easier to do it with a module.

I like CodeIgniter because it is much more open and airy.  There are also lots of great libraries in CodeIgniter which makes things just a little more fast and fun. 

Really, it depends on how exactly you want it to work though.

I’ll bet CI makes this sort of thing a lot easier. I’m trying to make sure I introduce myself to those familiar with CI as I become aware of them so I’ll be well positioned to benefit from their knowledge 😊

I think a created table might be the way to go as well. I don’ tknow, there are a few factors in this that are making it confusing, and I may just have to hire someone short term to help me think it through, and possibly help code the module/queries. Maybe I should spend some time on the CI forums, lurking around and learning.

You could achieve approximately the same with weblog entries but it would be quite hackish.  You would need to convert the data and then do the operations on that data as it gets generated by the weblog tags.  Of course, there is also the problem that you are trying to stuff the weblog into doing something it really wasn’t designed to do.  At some point, it just gets easier to do it with a module.

You should see the query module queries I have kicking right now…and the queries aren’t even coming out correctly (due to my lack of true mySQL skills). The most basic queries are coming out fine (total wins, total losses) but it quickly gets hairy.

Thanks for your thoughts on this, by the way.

Sure, generally you are going to create your database schema based on the needs of the application.  Of course, the weblog module schema is designed for general usage of a weblog type of system.  The farther you get from that type of application the more you have to work to get it to work right.  Also, it won’t be as efficient as a schema designed specifically for the application in question. 

So, I would do Module or CodeIgniter application.  Either way you go, at this point the system is a bit broad to cover the specifics of how it should be built in a discussion forum.

Cool, thanks for the advice. I think I might put together a “formal” or at least organized write up of what I’d like to keep in the DB, and what I’d expect to get back out on the template side. Just so long as I can still easily enter game data from my iPhone when a game is complete, I’ll be happy.

Um, considerations of architecture aside—why would you need to convert the data type in the EE table? MySQL has a perfectly good CAST function to cast strings to numbers on the fly in a query…

I’ve PM’d you—I’m not sure I’m a “guru” but I’ve got some SQL-fu.

Um, considerations of architecture aside—why would you need to convert the data type in the EE table? MySQL has a perfectly good CAST function to cast strings to numbers on the fly in a query…

I’ve PM’d you—I’m not sure I’m a “guru” but I’ve got some SQL-fu.

Sure, when you are talking about PHP and MySQL you have a huge number of options.  You also have a ton of options of what you can do with just the weblog module.  However, eventually you reach the point of silliness trying to force the weblog module into doing something it obviously wasn’t designed to do.  And even if you could do this with the weblog module, that doesn’t necessarily make it a good idea.  I have done some crazy things with the weblog module, but modules aren’t difficult to build and if I needed this in my site a module is likely the direction I would go.

Also, there is not yet enough information in this thread to say exactly how this should be built.  Until Ryan further details the needs of his application, this is all speculation. In other words, I think you guys both do freelance work, neither one of you would give someone a quote based on the info we have.  😉

Oh, sure. Which is why i said “all questions of architecture aside”. 😊

I was just pointing out that you don’t even need PHP to do the integer cast, it can be handled *directly in the query*. For instance, purely hypothetically:

SELECT CAST(field_id_8 AS UNSIGNED) AS my_winning_score,
CAST(field_id_9 AS UNSIGNED) AS my_losing_score,
CAST(field_id_8 - field_id_9 AS UNSIGNED) AS score_difference
FROM exp_weblog_data

I didn’t even know “CAST” existed, so you see, at least I know when I know I need help!

And John has a good point - the more I think about it, the more the weblog module doesn’t seem to ‘fit’ what I’m trying to do with this data. I always think of the weblog module as the be-all, end-all, but it is, after all, only another module.

Also, there is not yet enough information in this thread to say exactly how this should be built.  Until Ryan further details the needs of his application, this is all speculation. In other words, I think you guys both do freelance work, neither one of you would give someone a quote based on the info we have.

You are exactly right. I’m writing up everything now - as far as what data to store and how (wins, losses, etc etc) and what I want to come out of the data (the various queries, in English). Once I have that together, I’ll be in better shape to ask for more specific help. And alienne, I did get your PM, thanks.

Fair enough. Myself, i tend to try to do things with the weblog module mostly so i can take advantage of the various extensions and stuff that are available for Publish pages. But there are probably places where i should think about getting away from that, myself. 😊 Not so much of a PHP person, though, me—i can make things work, but it’s often not pretty, and so i tend to try to stick to more-travelled ground rather than building my own.

So, it’s just a matter of playing to one’s strengths, i guess. 😊 Whatever you decide to do as far as modules, I can certainly help out with querying. I <3 SQL!