Need help figuring out a graceful solution..

[vray10]

So here is my problem/challenge..

I have a database of team members. When TeamMember_1 logs in, I want a form created which gives him the the ability to review and comment on all the other team members. What I am basically trying to do is load one table with a few text fields per team member into one form, excluding the team member who pulling the page and doing the review. My question would be concerning the form field names. If I load one template with a table and text fields multiple times, how do I create different form field names defined per team member to be reviewed? For example, if the template has 3 text fields, the field names for the first team member loaded could be say teammem1_question1, teammem1_question2, teammem1_question3 etc, for the second team member it would be teammem2_question1, teammem2_question2 and so on. I guess I could create a table to store these field names and load them into the template when that team member is invoked, but there has to be a more graceful way - create the field names on the fly. Or no?

Any suggestions, assistance and brainstorming would be much appreciated. If the above does not make sense, I would be happy to clarify...

Using mysql 3.23.54 and php 4.2.2

Thanks.

[onion2k]

2 database tables:

table 1: Team members -
member_id
name

table 2: Comments -
comment_id
member_id
comment_on_id
comment

When someone logs in you pull all the team members out who aren't that teammember and create a form on the fly like this:

while ($record = mysql_fetch_object($result)) {
echo "<textarea name=\"c_".$record->member_id."\"></textarea>";
}

when the form is submitted $_POST will have a bunch of variables in called things like c_2, c_3, c_6 and so on. These are the comments on the team members corresponding to each id.

To store the comments pull out the same recordset used to generate the form, that is the teammembers that aren't the current user, and use a reference to put them into the database:

while ($record = mysql_fetch_object($result)){

$commentVar = "c_".$record->member_id;
$sql = "insert into comments (member_id, comment_on_id, comment) values (";
$sql .= "'".$_SESSION['logged_in_id']."', ";
$sql .= "'".$record->member_id."', ";
$sql .= "'".$$commentVar."') ";

So if I'm user 1 and I'm logged in, the code will loop through the other users 2, 3, 4 and 5, and store my id, the member id of each other person, and the variable c_2, c_3, c_4 and c_5 in 4 seperate records in the comments table.

That should get you started..

[vray10]

Thank you very much. I will give this approach a try this evening. I am sure i will have more questions ;-)

[vray10]

Ok, so it took me longer than an evening... I expanded the code from onion2k to include all the fields I needed, posted below.

/INSERT VARIABLES INTO DB

$query = "SELECT * FROM user WHERE user_name != '$user_name' AND team_id = 1";
$result = mysql_query($query);
while ($record = mysql_fetch_object($result))
{
$teamworkVar = $POST["r1".$record->user_id];
$attitudeVar = $POST["r2".$record->user_id];
$prod_knowVar = $POST["r3".$record->user_id];
$productivityVar = $POST["r4".$record->user_id];
$communicationVar = $POST["r5".$record->user_id];
$professionalismVar = $POST["r5".$record->user_id];
$commentVar = $POST["c".$record->user_id];
$sql = "INSERT INTO review (review_id, user_id, comment_on_id, teamwork, attitude, prod_know, productivity, communication, professionalism, comment, review_date)
VALUES (";
$sql .= "NULL, ";
$sql .= "'$reviewerid', "; //user_id of reviewer
$sql .= "'".$record->user_id."', "; //user_id of the victim
$sql .= "'".$$teamworkVar."', ";
$sql .= "'".$$attitudeVar."', ";
$sql .= "'".$$prod_knowVar."', ";
$sql .= "'".$$productivityVar."', ";
$sql .= "'".$$communicationVar."', ";
$sql .= "'".$$professionalismVar."', ";
$sql .= "'".$$commentVar."', "; //Review Comment
$sql .= "NOW())";
}

[vray10]

I am sure the formatting of the code to insert data is incorrect. If anyone would help me sort it out, i'd be much appreciative.

[Roger_Ramjet]

This is another case where a temporary table would provide an elegant solution. Simply create a temp table and populate it with all the other team members, and any comments made by the active user. Since temporary tables are per connection you can use the same table name and field names, simplifying your code no end. see: http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html

Then simply select all from the temp table and display as you like. Add, edit and delete comments are simple operations performed with the row id of the record. To save the session you just have to update from the temp table to the permanent when the user clicks the save button.

This use of temp tables is a standard solution in mainstream databases, less common amongst web programmers who usually have less experience of database theory and practice. It is applicable to all sorts of problems since it allows you to reduce complexity and thus simplify coding.

[vray10]

Hey, thanks very much for that, I'll look into it. I'll let you know how it works out.