nikkicade

Just tried it with a new table called alltotals. Used this :- update alltotals g1 SET GameTotal= (SELECT SUM(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts) FROM game20sep g2 WHERE g2.username = g1.username GROUP BY g2.username); and it worked a treat :-) Thanks again for your help

I will create a new table for totals and see if I can get it to work. Thanks for your help

Tried that and got an error "You can't specify target table 'g1' for update in FROM clause". As I have a similar problem with the monthly totals and running totals for the year - maybe it would be better to create a totals table and have a TotalPts for each game as well as a monthly total and a running total and then use something like Update totalsable g1 set Game1TotalPts=(SELECT SUM(your columns) FROM game20sep g2 WHERE g2.username = g1.username GROUP BY g3.username); Do you think this would be better?

Hi Corbin I tried that but it said Invalid use of group function Tried this :- select sum(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts) AS TotalPts from game20sep group by Username; and it shows the total In the TotalPts column but it still does not write it to the column.

Hi Corbin Tried your code but came back with "Column 'TotalPts' in field list is ambiguous". Not used JOIN before but tried adding g1. before TotalPts and it came back with "Invalid use of Group function". The Username is in the same table as the results and points so do I need Join as I thought this linked or joined tables? All I need to do is write the results of the sum into the TotalPts column but do it by table row as the Username is unique and therefore there is only 1 row per username. If you use:- insert into game20sep (TotalPts) Value (SUM(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts)) on duplicate key update TotalPts=SUM(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts); you get an error "Invalid use of Group function" similar to the update command in first post and if you add 'GROUP BY Username' on the end it says there is an error in the syntax. I am lost with this as I can get the Sum to work but can't seem to write it into the column in the table. Thanks Nikki

Hi I have a table with reults and points in. I want to add all the points and then insert the total in a column called TotalPts. I can get the total worked out using the following:- Select Username, sum(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts) from game20sep group by Username; This displays the username and TotalPts for each user but rather than me editing the TotalPts column for each user is there a simple way of inserting the TotalPts for each user? I have tried:- Update game20sep set TotalPts=sum(FormationPts+GoalkeeperPts+RDefenderPts+CDefender1Pts+CDefender2Pts+CDefender3Pts+LDefenderPts+RMidfieldPts+CMidfield1Pts+CMidfield2Pts+CMidfield3Pts+LMidfieldPts+Striker1Pts+Striker2Pts+Striker3Pts+TacticalSubPts+Sub1Pts+Sub2Pts+Sub3Pts+Sub4Pts+Sub5Pts+ResultsPts+HalfTimeLPts+FullTimeLPts+Scorer1Pts+Card1Pts+GatePts+ShotsTargetPts+CornersPts+PossessionPts+TimeGoalPts+TotalYellowPts+TotalRedPts+TotalFoulsPts+Foul1Pts+TimeofCardPts+TimeofSubPts) group by Username; but this comes back with 'error in your SQL syntax'. mySql version 5.0.19 Does anyone have any ideas? Thanks

Hi I am new to this but have a database with a stored procedure to add a new product to the database. I have a form in which the user enters the new product information and click a submit button. The submit button then opens a new php page which calls the store procedure. I know everything is working as it should because the users have to login in order to get to the page with the form to add the new product. The form works fine and I can define the variables from the form and echo them out to the page called from the submit button using $ProductName = $_POST['ProductCode']; and echo $ProductName;. The problem I have is binding the parameters for the stored procedure. Does anyone know the correct method of binding the parameters to use with $query? Is it that I have to prepare, bind_param and execute? I have found the syntax for mysqli and have tried this $stmt = $mysqli->prepare("call usp_NewProduct(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); $stmt->bind_param('ssssssssissdi', $ProductName, $ProductSubCategory, $ProductSubCategory2, $ProductSubCategory3, $ProductDesign, $ProductColour, $ProductSize, $ProductFragrance, $DeliveryBand, $ProductCode, $ProductDescription, $ProductPrice, $StockLevel); $ProductName = $_POST['ProductName']; $ProductSubCategory = $_POST['ProductSubCategory']; $ProductSubCategory2 = $_POST['ProductSubCategory2']; $ProductSubCategory3 = $_POST['ProductSubCategory3']; $ProductDesign = $_POST['ProductDesign']; $ProductColour = $_POST['ProductColour']; $ProductSize = $_POST['ProductSize']; $ProductFragrance = $_POST['ProductFragrance']; $DeliveryBand = $_POST['DeliveryBand']; $ProductCode = $_POST['ProductCode']; $ProductDescription = $_POST['ProductDescription']; $ProductPrice = $_POST['ProductPrice']; $StockLevel = $_POST['StockLevel']; /* execute prepared statement */ $stmt->execute(); /* close statement and connection */ $stmt->close(); but cannot get it to work. Any help would be gratefully received. Thanks Nikki