Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Dec 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    select dropdown box option based on mysql

    I am fairly new to this so I'll give it my best shot to describe the issue I am having:

    The website I am creating allows users to input their information (studentID, firstname, lastname, college, major, etc.) and I am trying to allow each user to update their information using a similar form they used to register in the first place.

    The updateInformation.php form has text boxes with values set from mysql when the page is entered, including a dropdown box to select their college from a list that I manually added to the php code (it does not populate the from an sql query, and I would like to keep it that way if possible). Below is the code I used for the table:

    Code:
    $mainMenu = <<<EOBODY
    	<br /><u>Please select a school or college of study:</u><br /><br />
    	<select name="school"> Selected="$recordArray[school]"
    		<option value="AGNR">School of Agriculture and Natural Resources</option>
    		<option value="ARPP">School of Architecture, Planning, and Preservation</option>
    		<option value="ARHU">College of Arts and Humanities</option>
    		<option value="BSOS">College of Behavioral and Social Sciences</option>
    		<option value="BMGT">Robert H. Smith School of Business</option>
    		<option value="CMNS">College of Computer, Mathematical and Natural Sciences</option>
    		<option value="CoED">College of Education</option>
    		<option value="ENES">A. James Clark School of Engineering</option>
    		<option value="JOUR">Philip Merrill College of Journalism</option>
    		<option value="SoPH">School of Public Health</option>
    		<option value="UNDE">Undecided</option>
    	</select>
    EOBODY;
    The issue I am having is setting the selected value to be the value that is in the mysql DB. How would I insert php code so that it would auto-select the option that the user previously chosen? i.e. if the user was in "College of Arts and Humanities", the value "ARHU" would be selected by default.

    If it helps, here is the code I used to access the database:

    Code:
    session_start();
    
    if (!(isset($_SESSION['login']) && $_SESSION['login'] != '')) {
    header ("Location: login.php");
    }
    
    $title = "Study Buddy: University of Maryland";
    
    $db = accessApplicationDB();
    	$query = "select * ". 
    		"from currentCourses, Student ".
    		"where Student.studentID = '$_SESSION[studentID]' AND ".
    			"Student.studentID = currentCourses.studentID";
    	$result = mysqli_query($db, $query);
    
    if ($result) {
        $numberOfRows = mysqli_num_rows($result);
        if ($numberOfRows == 0) {
            $body = "<h2>Invalid University ID " . $_SESSION['studentID'] . " provided.</h2>";
        } else {
    		$recordArray = mysqli_fetch_array($result, MYSQLI_ASSOC);
    Thank you for your help!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,340 Times in 4,306 Posts
    Two ways:

    (1) Simplest. Get the prior choice in the master query where you get all the other info about the given student. And then just test for it as you create the list of schools:
    Code:
    $priorSchool = $recordArray["school"];
    
    $result = mysql_query("SELECT schoolid, schoolname FROM schools ORDER BY schoolid");
    while ( $schools = mysql_fetch_assoc($result) )
    {
        $schoolid = $schools["schoolid"];
        $schoolname = $schools["schoolname"];
        $sel = ( $schoolid == $priorSchool ) ?  " selected " : "";
        echo "<option value=\"$schoolid\" $sel>$schoolname</option>\n";
    }
    **********
    (2) Mildly better. Use SQL.
    Code:
    $priorSchool = $recordArray["school"];
    
    $sql = "SELECT schoolid, shoolname, IF(shoolid = $priorSchool, ' selected ', '' ) AS sel "
           . " FROM schools ORDER BY schoolid";
    $result = mysql_query( $sql );
    while ( $schools = mysql_fetch_assoc($result) )
    {
        $schoolid = $schools["schoolid"];
        $schoolname = $schools["schoolname"];
        $sel = $schools["sel"];
        echo "<option value=\"$schoolid\" $sel>$schoolname</option>\n";
    }
    ****************

    I don't use PHP, so apologies for errors in the PHP code, but the above show the priniciples at least.

    There is a third way, using SQL, but it probably doesn't apply for this case, where you need to get all the details about the student's prior choices anyway.

    But if you care:
    Code:
    SELECT S.schoolid, S.schoolname, IF(ST.schoolid IS NULL,'',' selected ') AS sel
    FROM schools AS S
    LEFT JOIN studentInfo AS ST
    ON S.schoolid = ST.schoolid AND ST.studentid = 888
    ORDER BY S.schoolid
    Where the 888 above is replaced by the actual student id.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •