tracking changes

[danielf]

Sorry for the obscure title, I couldn't think of anything better.

I'm having a problem updating fields when making changes. Let's assume I have a database with different types of members, call them bronze, silver and gold members. I also have a table that has counts for the different user types so I can bring up a browse screen that shows how many members of each type there are. When a new member is created, I read the membership level from the edit screen. I then run a query on the database to find all the members with that membership level, extract the count and update the relevant table. This is done in the 'after save' section.

Code: Select all

$client_membership_level = $_POST["client-membership_level"];
$sc_query = "SELECT * FROM members WHERE client_membership_level = '$client_membership_level'";
$result = nuRunQuery($sc_query);
$num_rows = mysql_num_rows($result);
$sc_query = "UPDATE membership_levels SET membership_count = '$num_rows' WHERE membership_level = '$client_membership_level'";
nuRunQuery($sc_query);

This works fine when creating new users, but I'm having trouble updating the counts when a member changes his membership level. I have no problem using the above code to update the counts for the revised level, but I no longer have access to the id of the previous level, so I can't update that.

What I'm currently doing is to create a text field (and table entry) that I use to store a copy of the membership level before update, and then run a similar query for the previous level. This works, but it seems rather cumbersome, so I'm wondering if there is a more elegant solution.

Personally I can think of two solutions:

The first (which I don't think is possible) is to read the current value in the 'before open' section and somehow pass it to the 'after save' section.
The second would be to not bother with reading the relevant value, but to simply iterate over all values of the (finite) list of membership levels. This seems the most elegant solution, as I could simply do this once when I call up the browse screen that shows the counts.

Presumaby, in the sql code of the browse screen, I should be able to select everything from the membership levels, join the members on the membership levels and then update the counts in the membership table based on how many members are in each category, but I'm stumped as to how I would do it. Basically, I need to iterate over the rows in the membership_levels table, but I don't know how to do it. I can successfully do it in a report where I can use a listbox to create the list to iterate over, but I'd really like to be able to generate the counts for a browse screen...

Any help would be greatly appreciated, if all this makes sense.

Dan.

[massiws]

Dan,
I read several times you post and I hope I understand: sossy if it's not so!

First: why store the membership count in a db table? Can't you get this value in browse screen using a SELECT COUNT() in display field?

anyway, I may think this solution:
- create on edit-form a hidden object, old_membership_level to save the old value;
- in CustomCode > Javascript > insert the onLoadThis() function with a bit of JavaScript to copy the membership_level value in hidden old_membership_level object;
- in CustomCode > BeforeSave enter PHP code needed to update your tables with both new and old values.

I hope it can helps.

[danielf]

Hi Massiws,

Thanks for your response.

I'm not sure how SELECT COUNT() would work on the table of members if I just want a browse screen that shows just the membership levels and their respective counts. It's quite possible that I'm overlooking something obvious here though.

Your other solution is actually what I'm using now, and this works, but I need to repeat it for a couple of other fields, so I'm looking for a solution that's a bit simpler/more generic. My thinking was to just generate the counts for all levels when I pull up the browse screen or in the 'after save' of the edit screen, but this would involve iterating over the different levels, and I'm not sure how to do that.

Dan.

[danielf]

Cracked it

I thought I'd share in case it is helpful for anyone else facing the same thing. I think iterating over all categories is a bit more elegant than sticking in an extra hidden text field to read a value, populating that field using javascript, and then write the code to update both fields. Using this solution you just write one bit of code that can easily be adapted should you want to carry out the same trick elsewhere. It's only three lines longer than the code to update a single field.

Code: Select all

$query = "SELECT * FROM membership_levels"; //read in the table
$result = nuRunQuery($query);
WHILE ($row = mysql_fetch_array($result)){ //loop over all rows in the table
	$name = $row['membershiplevel_name']; //extract the item to update and stick it in a variable	
	$query1 = "SELECT * FROM members WHERE membership_level = '$name'"; //select values from foreign table that match the key.
	$result1 = nuRunQuery($query1);
	$num_rows = mysql_num_rows($result1);  //get the counts
	$query2 = "UPDATE membership_levels SET memberbershiplevel_count = $num_rows WHERE membershiplevel_name = '$name'"; //update relevant field
nuRunQuery($query2);
}

[massiws]

Dan,

if you have a form to show the membership_level you can put in SQL field this code:

Code: Select all

SELECT * FROM membership_level INNER JOIN member ON mb_ml_id = ml_id GROUP BY ml_name order by ml_id

In the browse tab add a field to display a count column with this:

Code: Select all

count(mb_ml_id) 

See attached image to see what I get.

Is that what you were looking for?

[danielf]

Thanks, Massiws.

Yes, this is what I was after, and your solution looks far more elegant than what I ended up doing. By the looks of it, your sql statement does in one single line all the work that I did by creating special tables. Membership level is actually in a list to be used for a dropdown box, so I should be able to just grab it from there and compute the rest on the fly using SQL rather than create a separate table for it. SQL does look very powerful once you manage to get your head round it.

Once again, many thanks. Your post has given me lots of food for thought, and cause for experimentation.

Dan.

[admin]

.