Add all miles by user ID

The are several ways to do this depending on the overall objective and ones own personal coding style preferences.
What is the overall purpose of adding the trip miles to the users profile; is it a one off exercise to get a total or is this a recurring activity, i.e. every time the users profile is displayed you want to see the current total, or you want the total miles on the users profile for some other activity like payment for miles travelled.

1. The one off situation, I'd create a list page for the User Profile table with code in the List Page: Before Process event, that would loop through all the user profile records in the Trip Info Table total by user id and update each User Profile table record with the total miles. When the list page displays it will show the total miles for each User, depending on volumes this can take a while to execute.
   
2. To display the current total miles whenever the User profile is displayed does not require the total miles to be held on the user profile. In a custom view join the User Profile to the Trip Info table, as you have done, then modify the SQL to have SUM(Trip Info.statemiles) as user_total_miles, include a GROUP BY Profile Id. This will result in the total miles per user being calculated dynamically everytime the user profile is displayed.
   
3. To use the Users total miles in some other process like calculating expenses ore reimbursing for fuel usage, you would probably need a date in each Trip Info record, or record that the trip has been processed. As you haven't mentioned that we ignore that bit as it doesn't affect the basic solution. This solution uses a combination of the (a) and (<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=94014&image=1&table=forumreplies' class='bbc_emoticon' alt='B)' /> above. Assuming a list screen to display the users profile, include a calculated field (PHPRunner Help explains how to do this) in the before display event add code to loop through all the trip records for the user total the miles and store the result in the calculated field on the page. There is another way to do this. Define a calculated field and then on the Fields Tab properties column for the calculated field, View As Custom put the code to calculate the trip total miles.
   As an aside I've generally created my own dummy fields in these circumstances using CONCAT(" ") as My_Dummy_Field in the SQL for the custom view.
   Hope this helps.
   Regards

What I was thinking is just having that users miles they have driven for the company total miles on there profile. Not really needed but would be interesting. That way they know how many miles they have drove for the company. Something most job applications ask for before hiring you as a truck driver. Like I said it is just for fun and not really needed.

I have tried a few different ways but been a while since I played with PHPRunner and everything I have came up with I got database errors. LOL..Right now I tried in the view page before display I have:

global $strTableName, $conn;

$strSQLExists = "select sum(total) from trip_report where account_id = ".$values["id"];

$rsExists = DB::Query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);

if($data)

{

$values["total_miles"] = $data["total_miles"];

}

Then in the field I have it as Read Only with Default as $data["total_miles"]But that is empty.. Still trying though..

Okay, Hope someone might be able to help me out on this.

I have the 2 Table.

User Profile has

Table Name = account

User id = id

Total Miles Field = miles
Then to get the total miles driving all to gather

Table Name = trip_report

Trip report ID = report_id

Account Security ID = account_id

List of running miles = total
Here is the code I put in Event trip_report After record added:

What I am thinking this should do is update the account miles field with trip_report sum of all miles called total and matched up to users account id in there profile? They are not inter joined on the TABLES Tab in PHPRunner but think it should still work.

In the account miles field I have that set as varchar 100 and readonly in the field.
The problem is: It is not updating or putting anything in the miles. Maybe have it in the wrong spot?
Thank you very much for any help on this.

James

The general rule of the troubleshooting suggests the following:

1. Print the value of $_SESSION["userid"] on the page to make sue it populated
   
2. Print the whole SQL query on the page, run it manually using phpMyAdmin or a similar tool, make sure it does work.
   
3. If your SQL query doesn't work outside of PHPRunner, fix it manually in phpMyAdmin first and then paste it back to PHPRunner.

I got what I needed working.. Thanks