Page 1 of 1

Populating/Updating Database Data

Posted: Fri Jan 08, 2010 2:58 pm
by javiqq
We have a page with a form used to add/update the schools to their respective County's. Previously the client could only add school but not specificy the County. The code below adds a select menu so they can choose which county school belongs in. Currently when the page is loaded, you can view all the counties from the database successfully, however, when you select a school all the other fields populate with that school's information except this select field. Can someone help me get this working?

It needs to populate according to the school's selected or be able to update the database if the user decides to change a school's County.

This is the code I'm having issues with:

Code: Select all

 
    <select>
            <option value="">Assign school to a county</option>
            <?php 
            //  if((isset($_POST['schools']))&&($_POST['schools']!='Choose One')){
                $query_county = "SELECT co_county_name 
                                 FROM county
                                 ORDER BY co_county_name ASC";
                $result_county = @mysql_query($query_county);
            //  }
            
                while($row = mysql_fetch_assoc($result_county)){
                        echo "<option value=\"$row[co_county_id_pk]\"";
                        if($_POST['schools']==$row[sc_school_id_pk]) {
                            echo " selected=\"selected\">$row[sc_school_id_fk]</option>\n";}
                        else {
                            echo ">$row[co_county_name]</option>\n";}
                }
            ?>
        </select>
 
This is all the code working together to populate the fields:

Code: Select all

 
<form name="school_options" action="<?php echo $_SERVER['PHP_SELF'].'?admin_util=school_options'; ?>" method="post"  onSubmit="return checkuserFields_submit()">
    <fieldset><legend> Add/Edit a School:</legend>
    <table width="100" align="right" bordercolor="#FF0000" bgcolor="#FF0000">
    <tr><td>
    <?php echo '<span class="style3"><b>Select schools: </b></span>';
    $query = "SELECT * FROM schools ORDER BY sc_school_name ASC";
    $result = @mysql_query($query);
    echo '<select name="schools" align="right" class="sm_textblue" onchange="refreshPage(this.form)"><option>Choose One</option>';
    while($row = mysql_fetch_assoc($result)){
        echo "<option value=\"$row[sc_school_id_pk]\"";
        if($_POST['schools']==$row[sc_school_id_pk]) {echo " selected=\"selected\">$row[sc_school_name], $row[sc_school_city]</option>\n";}
        else {echo ">$row[sc_school_name], $row[sc_school_city]</option>\n";}
    }
    echo '</select></td></tr>';
    
//  if((isset($_POST['schools']))&&($_POST['schools']!='Choose One')){
        $query = "SELECT * FROM schools WHERE sc_school_id_pk='{$_POST['schools']}'";
        $result = @mysql_query($query);
        $row_edit = mysql_fetch_assoc($result);
//  }
    
    ?>
    </table>
    <p><br><br>
    <table width="350" align="left" bordercolor="#FFFFFF" bgcolor="#B4B3B1">
    <tr><td align="left" width="15%"><span class="style2"><span class="style3">School Name:</span></span></td>
    <td align="left" width="30%"><input type="text" name="school_name" size="30" maxlength="40" class="sm_textblack" value="<?php echo $row_edit['sc_school_name']; ?>" /></td></tr> 
    
    <tr><td align="left" width="150%"><span class="style3"><b>School City:</b></span></td>
    <td align="left" width="30%"><input type="text" name="school_city" size="30" class="sm_textblack" maxlength="40" value="<?php echo $row_edit['sc_school_city']; ?>" /></td></tr>
    
    
    <tr><td align="left" width="150%"><span class="style3"><b>School County:</b></span></td>
    <td align="left" width="30%">
 
        <select>
            <option value="">Assign school to a county</option>
            <?php 
            //  if((isset($_POST['schools']))&&($_POST['schools']!='Choose One')){
                $query_county = "SELECT co_county_name 
                                 FROM county
                                 ORDER BY co_county_name ASC";
                $result_county = @mysql_query($query_county);
            //  }
            
                while($row = mysql_fetch_assoc($result_county)){
                        echo "<option value=\"$row[co_county_id_pk]\"";
                        if($_POST['schools']==$row[sc_school_id_pk]) {
                            echo " selected=\"selected\">$row[sc_school_id_fk]</option>\n";}
                        else {
                            echo ">$row[co_county_name]</option>\n";}
                }
            ?>
        </select>
  
        
        <!--<input type="text" name="school_city" size="30" class="sm_textblack" maxlength="40" value="<?php echo $row_edit['sc_school_city']; ?>" />--></td></tr>
 
    </td>
    </tr>
    </table>
    </fieldset>
    <div align="center"><input type="submit" name="enter_update_school" value="Update/Enter" /></div>
    </form> 
    </p>