PHP/MySQL help

[Ben E Lou]

PROBLEM: There are several places in FOF where transactions do not take place in the "current" stage. For example, if I'm about to run FA1:4, when I import the files, all renegotiations, failed renegotiations, players released, and RFA signings/failed signings take place right then, before I have run the stage, in FA1:3.

DESIRE: Without having to code for every single possible case where a transaction takes place in the previous stage, I'd like to be able to show those transactions in a single-stage report with no manual intervention other than running DBUpdater.

CURRENT NON-IDEAL AUTOMATED SOLUTION: I have created a custom table (lasttransaction) that contains the last Transaction ID that was previously run. At the very end of my transaction php script, I am updating the Transaction ID in that table as follows:

INSERT INTO lasttransaction
SELECT MAX(t.index)+1, MAX(a.id)
FROM lasttransaction t, fof_transactions a

So then the WHERE clause of my main transaction query is....

WHERE type<>28 AND t.id>(SELECT max(transactionid) FROM lasttransaction)

The drawback to that solution is that I see the correct transactions when I load the transaction script once...but I can never load it again since it updates the last transaction at the end of the script.

ALTERNATE NON-IDEAL MANUAL SOLUTION: I can just remove the code from the end of the transaction script, and run it manually right before updating the database. This solves the issue of not being able to see the transactions again, but it has two other drawbacks that I can see:
1. I'd have to run it manually before updating the database every stage.
2. If I were to forget to run it manually before updating the DB, I'd have to go in, locate the right transaction ID, and
manually insert it into lasttransaction.





So, the question here is this: is there a way to do this, keep it completely automated, but still be able to view the last batch of transactions? What I'm thinking here is something along the lines of

IF ($transactiontablemaxtransactionid==$lasttransactionmaxtransactionid)
 {$query="{the stuff I need}
WHERE type<>28 AND t.id>(SELECT max(transactionid) FROM lasttransaction)";}
ELSE {$query=SELECT {the stuff I need}
WHERE type<>28 AND t.id>{however the heck you determine the penultimate value from the lasttransaction table";}

Am I on the right track there, or is there a better way to approach it? And if I am on the right track, how would I determine in a WHERE clause that penultimate value?

[lordscarlet]

OK, I was going to make a big, complicated SQL query, but it's probably best to just pull this out into the php a little bit. It's possible I have some syntax errors, but basically you want to do this:

When the page loads, check if max(lasttransaction.lasttransactionid) is the same as max(fof_transaction.id). If they are not the same, add your row to lasttransaction.

then, grab the most recent TWO rows from lasttransaction.

SELECT transactionid 
FROM lasttransaction 
ORDER BY transactionid DESC LIMIT 2

stuff transactionid from each of those rows into two variables, we'll call them $high and $low

then just do:

SELECT
  *
FROM
  fof_transactions t
WHERE
  t.id > $low AND t.id <= $high

There's probably a more elegant solution, but it's 9am on a day off.

[Ben E Lou]

Yeah. That makes sense. I was thinking something simple like that, but my own brain was a little foggy. Thanks!

[Ben E Lou]

Thanks again, ls. It's all coded, and based on testing, it looks like it will work perfectly. I'll know for sure on Monday when I put it into action.

[lordscarlet]

Glad I could help!

[Ben E Lou]

OK. I got another one here.

I want to determine a roster strength rating for each position group and for the roster as a whole. I've set up a table called positiongroupweights that contains exactly what it says, but it's a bit deeper than that. For example, here's what the first few lines look like...

grp	first	second	third	fourth	fifth	starters
1	1.4	0.9	0.2	0	0	1
2	1.15	0.85	0	0	0	1
3	0.6	0.2	0	0	0	1
4	1.05	0.5	0	0	0	1
5	1.25	1.25	0.9	0.75	0.4	2

So the idea is that the first QB is weighted at 1.4, and the second at .9. Looking at group 5, the first two WRs are weighted at 1.25, and WR3 is at .9. What I'd *like* to do here is query for the top five current-rated players on a given team, multiply them by the appropriate weights for where they stand, and add 'em all up to determine a team score. I'd also like to have individual team scores by position as well. And I'd rather not have to do 16 different queries to grab the position groups.

I can do this query...

SELECT tm.abbrev, sum(wt.first*rtg.cur) AS score
FROM playerratings rtg
JOIN fof_playerhistorical h ON h.firstname=rtg.first AND h.lastname=rtg.last
JOIN fof_playeractive a ON a.id=h.id
JOIN positiongroupweights wt ON wt.positiongroup=a.positiongroup
JOIN fof_teams tm ON tm.id=a.team
GROUP BY a.team
ORDER BY score DESC

..but the major drawback there is that it's multiplying every single player on the team by the starter weight. I thought doing this would be "good enough," but we have a team in WOOF that is proving me wrong:

WOOF: Las Vegas Ballas Roster

Because LVS has a bunch of guys in the 40 to 45 range in QB3/TE3/WR3/RB3 type positions, they're coming up as the #1 team in the league, roster-wise. Yes, they have great depth, but I don't want to take into account guys who are pretty unlikely to see the field. Yes, they have a 45/45 QB3, but it doesn't mean much when in all likelihood he won't play a single meaningful snap all year. Is there a way that I can do this in one query, or will I need to go position group by position group to accomplish what I want here?