Table data into a form

[whoopuk]

I've hit a wall with my script any help / ideas would be really appreciated.

I've written some reporting which fetches data from three mysql tables, applies some php logic then populates a table in a page with (amongst other fields) a medal (Gold, Silver, Bronze). There needs to be some manual moderation by visitor at this point. A visitor should be able to override the automatically calculated medal if they don't agree with it. Hopefully the screen shot attached makes sense.

I then want the overriden medal be updated in the original database table.

I'm stuck on how I process all the medals (including moderated ones) in one go.

Hope this makes sense

Screenshot:
http://citybit.southampton-city.ac.uk/gsb_table.png

[tirna]

I think you would have a better chance of getting some help if you posted your code.

[whoopuk]

That's a good shout, thanks Here's my code:

<?php 

 //connect to server
 $con = mysql_connect("localhost","username","password");
    if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  
    //connect to db
    mysql_select_db("citybit0809", $con);
  
    $categoryid = $_POST['dept'];
    
    //echo $categoryid;
  
    //get course category name
    $get_dept_name = mysql_query("SELECT name FROM mdl_course_categories where id = $categoryid");
    $dept_name = mysql_fetch_assoc($get_dept_name);
    echo $dept_name['name'];
    
    $dept_name_text = $dept_name['name'];
    
    echo "<br /><br />";
    
    echo "<form method='post' action='http://musca/process.php' name='gsb_process_form'><br><br><input type='submit' name='submit' value='Process GSB Medals'></font></p><br>";
    
    echo"
        <table border='1' cellspacing='0' cellpadding='2' width='100%'>
        <tr>
            <td bgcolor='#C0C0C0'><b>ID</b></td>
            <td bgcolor='#C0C0C0'><b>Shortname</b></td>
            <td bgcolor='#C0C0C0'><b>Course name (click name to visit course)</b></td>
            <td bgcolor='#C0C0C0'><b>More course info</b></td>
            <td bgcolor='#C0C0C0'><b>NEW GSB SCORE</b></td>
            <td bgcolor='#C0C0C0'><b>Previous GSB Score</b></td>
            <td bgcolor='#C0C0C0'><b>Override GSB</b></td>
        </tr>";
    
    
    //get course codes from department
    $get_dept_codes = mysql_query("SELECT id, shortname, gsb, fullname FROM mdl_course where category = $categoryid order by id asc");

    //loop through and process gsb stats for courses 
    while($row = mysql_fetch_array($get_dept_codes))
        {
             $courseid = $row['id'];
            $courseshortname = $row['shortname'];
            $coursefullname = $row['fullname'];
            $old_gsb_score = $row['gsb'];
            
            if ($old_gsb_score == "") $old_gsb_score = "&nbsp;";
            else $old_gsb_score = $old_gsb_score;
            
            //bronze stats
            $standardslinknum = mysql_query("update coursestandards set coursestandards.linksnum = (SELECT count(*) FROM mdl_resource where type<>'ims' and course=$courseid) where coursestandards.courseid=$courseid");

            //silver stats
            $standardsassnum = mysql_query("update coursestandards set coursestandards.assignmentnum = (SELECT count(*) FROM mdl_assignment where course=$courseid) where coursestandards.courseid=$courseid");
            $standardsfeedbacknum = mysql_query("update coursestandards set coursestandards.feedbacknum = (SELECT count(*) FROM mdl_feedback where course=$courseid) where coursestandards.courseid=$courseid");
            $standardsimailnum = mysql_query("update coursestandards set coursestandards.imailnum = (SELECT count(*) FROM mdl_mail where course=$courseid) where coursestandards.courseid=$courseid");
            $standardsfeedbacknum = mysql_query("update coursestandards set coursestandards.feedbacknum = (SELECT count(*) FROM mdl_feedback where course=$courseid) where coursestandards.courseid=$courseid");
            $standardsinteractnum = mysql_query("update coursestandards set coursestandards.interactnum = (SELECT count(*) FROM mdl_resource where type='ims' and course=$courseid) where coursestandards.courseid=$courseid");
            $standardsquestnum = mysql_query("update coursestandards set coursestandards.questnum = (SELECT count(*) FROM mdl_questionnaire where course=$courseid) where coursestandards.courseid=$courseid");
            $standardsquiznum = mysql_query("update coursestandards set coursestandards.quiznum = (SELECT count(*) FROM mdl_quiz where course=$courseid) where coursestandards.courseid=$courseid");    
    
            //gold stats
            $standardschatnum = mysql_query("update coursestandards set coursestandards.chatnum = (SELECT count(*) FROM mdl_chat where course=$courseid) where coursestandards.courseid=$courseid");
            $standardsforumbacknum = mysql_query("update coursestandards set coursestandards.forumnum = (SELECT count(*) FROM mdl_forum where course=$courseid) where coursestandards.courseid=$courseid");
            $standardswikinum = mysql_query("update coursestandards set coursestandards.wikinum = (SELECT count(*) FROM mdl_wiki where course=$courseid) where coursestandards.courseid=$courseid");    
        
            //Gold, Silver, Bronze logic
            $bronze_score = mysql_query("select coursestandards.linksnum from coursestandards where coursestandards.courseid = $courseid");

            $bs = mysql_fetch_assoc( $bronze_score );

            $bss = $bs['linksnum'];
            
            if ($bss < 30) $gsb_score = "In Dev";

            else 
            
                { 

                $gsb_bronze = 1;

                $silver_fetch = mysql_query("select coursestandards.assignmentnum, coursestandards.interactnum, coursestandards.feedbacknum, coursestandards.questnum, coursestandards.quiznum from coursestandards where courseid = $courseid");

                $silver_array = mysql_fetch_assoc( $silver_fetch );

                $silver_counter = 0;

                if ($silver_array['assignmentnum'] > 0) $silver_counter ++;
                if ($silver_array['interactnum'] > 0) $silver_counter ++;
                if ($silver_array['feedbacknum'] > 0) $silver_counter ++;
                if ($silver_array['questnum'] > 0) $silver_counter ++;
                if ($silver_array['quiznum'] > 0) $silver_counter ++;
                if ($silver_counter > 1) $gsb_silver = 10;
                else $gsb_silver = 0;

                $gold_fetch = mysql_query("select coursestandards.forumnum, coursestandards.chatnum, coursestandards.wikinum, coursestandards.imailnum from coursestandards where courseid = $courseid");

                $gold_array = mysql_fetch_assoc( $gold_fetch );

                $gold_counter = 0;

                if ($gold_array['forumnum'] > 0) $gold_counter ++;
                if ($gold_array['chatnum'] > 0) $gold_counter ++;
                if ($gold_array['wikinum'] > 0) $gold_counter ++;
                if ($gold_array['imailnum'] > 0) $gold_counter ++;

                if ($gold_counter > 1) $gsb_gold = 100;
                else $gsb_gold = 0;

                $gsb = $gsb_bronze + $gsb_silver + $gsb_gold;

                if ($gsb == 111) $gsb_score = "Gold";
                else if ($gsb == 11) $gsb_score = "Silver";
                else if ($gsb == 1) $gsb_score = "Bronze";
                else if ($gsb == 101) $gsb_score = "Bronze";
                else $gsb_score = "In Dev";
                
                                                
                }
                
            
                echo   "<tr>
                        <td>$courseid</td>
                        <td>$courseshortname</td>
                        <td><a target='_blank' title='Click to enter this course' href='http://musca/citybit2/course/view.php?id=$courseid'>$coursefullname</a></td>
                        <td><a target='_blank' title='More course info' href='http://musca/stats/courseinfo/course_info.php'>More course info</a></td>
                        <td>$gsb_score</td>
                        <td>$old_gsb_score</td>
                        <td>
                        <p align='right'><select size='1' name='override_gsb'>
                        <option></option>
                        <option value='Gold'>Gold</option>
                        <option value='Silver'>Silver</option>
                        <option value='Bronze'>Bronze</option>
                        <option value='In Dev'>In Dev</option>
                        <option value='exclude'>Exclude</option>
                        </select></td>
                        </tr>
                        
                <input type='hidden' name='prevgsb' value=$old_gsb_score>
                <input type='hidden' name='currentgsb' value=$gsb_score>
                <input type='hidden' name='courseid' value=$courseid>";
                        
                
                                        
        }
        
        
        echo "<input type='hidden' name='course' value=$dept_name_text>
            </form>";

?>