Dilly of a pickle...

[DaVuLf]

Okay, so I've just stumbled upon a huge problem with what I'm programming. I need to figure out how to remove obsolete transactions. I'll explain a little:

[b]Insert.php[/b]
This file is what adds or subtracts from everything. This is where all the math and most of the action happens. This is where I would need the query.

[b]History.php[/b]
This contains the transaction history by ClientID.

[b]Portfolio.php[/b] (not created yet)
This will contain what the Client has in their portfolio. I intend to have rows that are summed based off of the History.php table. 4 columns; ClientID, StockName, AvgPrice, AggregateValue.

[b]Program Structure:[/b]
This 'program' (collection of php and mysql) is something I'm making for a club on my campus at University. We run a competition every year, and I'm trying to make the back-end more sophisticated and workable, with more features. There are competitors, and there are brokers. The competitors tell the brokers which information to input. The brokers then input the TeamName, StockName, TransactionType, and Quantity. TransactionType can be either 'Buy', 'Sell', 'Short', or 'Cover'. I won't go into detail on the short and cover, because I'm not at that stage yet. Right now, I'm working on the 'Sell' feature. The problem I'm having is basically, how do I work a sell? Well, there are several steps:

1. Determine how much of that stock the team currently owns.
-This is found through the portfolio table.
-The portfolio table is made by summing and averaging certain rows of the transactionHistory table.
2. Verify that the quantity is either below, or equal to the determined number.
3. Determine what value the stock currently sells for.
4. Multiply the value of the stock, by the quantity.
5. Modify team balance.
6. Subtract the number sold from the portfolio (???).
7. Add transaction to transaction history.

[b]Problem 1:[/b]
If I add the transaction to the transactionHistory, my portfolio table will ADD those sold shares instead of subtracting them. I was thinking about adding a minus sign when I add the number tothe table, but then it wouldn't make sense when reading it. Is there a workaround?

[b]Problem 2:[/b]
How to incorporate an undo function. This would be stored in its own .php, and accessed via a link supplied on the transaction confirmation page. With the 'Buy' function it was simply a matter of dropping the last row from the transaction history table. I'm not sure if this would work for the sell function because the balance would have changed.

[b]Problem 3:[/b]
When I'm updating the values in the portfolio table, is there a way to have it UPDATE the row corresponding to the TeamName if it exists, but to create a new row if it doesn't exist already?

Thanks for any and all help. I know these are toughies.

[AndyB]

Maybe I'm misreading problem #2, but my approach would be to only take database actions AFTER the confirmation was received. That way, there's no need for an undo (not only did the user ask to do 'something' but they confirmed it as well - that's enough chances, surely).

As for #3 - do a SELECT whatever WHERE teamname = '$this_team'. Check the number of rows affected. If it's zero go ahead and INSERT a record, otherwise UPDATE a record.

[DaVuLf]

[!--quoteo(post=375637:date=May 20 2006, 10:47 PM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ May 20 2006, 10:47 PM) [snapback]375637[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Maybe I'm misreading problem #2, but my approach would be to only take database actions AFTER the confirmation was received. That way, there's no need for an undo (not only did the user ask to do 'something' but they confirmed it as well - that's enough chances, surely).

As for #3 - do a SELECT whatever WHERE teamname = '$this_team'. Check the number of rows affected. If it's zero go ahead and INSERT a record, otherwise UPDATE a record.
[/quote]

Nice solution for #3. I'll definately go with that one. I was thinking the same thing for 2, I just have to figure out how to do it. Luckily, that should be easy to do with Google.

[!--sizeo:1--][span style=\"font-size:8pt;line-height:100%\"][!--/sizeo--]I just noticed you're from Toronto. I'm from Hamilton myself. Hello, neighbour :).[!--sizec--][/span][!--/sizec--]

[AndyB]

[!--quoteo(post=375638:date=May 20 2006, 10:50 PM:name=DaVuLf)--][div class=\'quotetop\']QUOTE(DaVuLf @ May 20 2006, 10:50 PM) [snapback]375638[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Nice solution for #3. I'll definately go with that one. I was thinking the same thing for 2, I just have to figure out how to do it. Luckily, that should be easy to do with Google.

[!--sizeo:1--][span style=\"font-size:8pt;line-height:100%\"][!--/sizeo--]I just noticed you're from Toronto. I'm from Hamilton myself. Hello, neighbour :).[!--sizec--][/span][!--/sizec--]
[/quote]

I use a javascript confirm as the simplest way to get people to confirm something.

[!--sizeo:1--][span style=\"font-size:8pt;line-height:100%\"][!--/sizeo--]... if you post the tougher MySQL questions in the MySQL forum, you'll likely get a high quality answer from Fenway who's also from the centre of the universe :)[!--sizec--][/span][!--/sizec--]

[DaVuLf]

[!--quoteo(post=375749:date=May 21 2006, 08:19 AM:name=AndyB)--][div class=\'quotetop\']QUOTE(AndyB @ May 21 2006, 08:19 AM) [snapback]375749[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I use a javascript confirm as the simplest way to get people to confirm something.

[!--sizeo:1--][span style=\"font-size:8pt;line-height:100%\"][!--/sizeo--]... if you post the tougher MySQL questions in the MySQL forum, you'll likely get a high quality answer from Fenway who's also from the centre of the universe :)[!--sizec--][/span][!--/sizec--]
[/quote]

I'll look into the JS, thanks.

This is the mySQL forum :).

[fenway]

[!--quoteo(post=375632:date=May 20 2006, 10:29 PM:name=DaVuLf)--][div class=\'quotetop\']QUOTE(DaVuLf @ May 20 2006, 10:29 PM) [snapback]375632[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[b]Problem 1:[/b]
If I add the transaction to the transactionHistory, my portfolio table will ADD those sold shares instead of subtracting them. I was thinking about adding a minus sign when I add the number tothe table, but then it wouldn't make sense when reading it. Is there a workaround?
[/quote]
Sure -- I assume your transaction table contains the transaction type, you when you calculate the portfolio, simply use something like "SUM(IF(type='buy',shares,IF(type='sell',-shares,0))" and you shouldn't have a problem. Never actually tried it, but it should work... if not, there are other ways to do this as well, but you get the idea.

[!--quoteo(post=375632:date=May 20 2006, 10:29 PM:name=DaVuLf)--][div class=\'quotetop\']QUOTE(DaVuLf @ May 20 2006, 10:29 PM) [snapback]375632[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[b]Problem 2:[/b]
How to incorporate an undo function. This would be stored in its own .php, and accessed via a link supplied on the transaction confirmation page. With the 'Buy' function it was simply a matter of dropping the last row from the transaction history table. I'm not sure if this would work for the sell function because the balance would have changed.
[/quote]
The problem is that you actively have to to maintain the balance, instead of calculating it dynamically. If you did the latter, then simply removing the transaction record would be sufficient.

[!--quoteo(post=375632:date=May 20 2006, 10:29 PM:name=DaVuLf)--][div class=\'quotetop\']QUOTE(DaVuLf @ May 20 2006, 10:29 PM) [snapback]375632[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[b]Problem 3:[/b]
When I'm updating the values in the portfolio table, is there a way to have it UPDATE the row corresponding to the TeamName if it exists, but to create a new row if it doesn't exist already?
[/quote]
AndyB's answer is correct -- alternatively, you can use an INSERT ... ON DUPLICATE KEY UPDATE statement, provided that you have a useful primary key to use.

Hope that helps.