www.webdeveloper.com
Results 1 to 2 of 2

Thread: [RESOLVED] Create Form Fields from MySQL table

Hybrid View

  1. #1
    Join Date
    May 2012
    Posts
    11

    resolved [RESOLVED] Create Form Fields from MySQL table

    Hi All,

    I need some guidance in creating a form that will generate from a mySQL table. The form needs to work as follows:

    I have a drop-down list where I can select a set of criteria as below:
    08:00 - 17:00
    09:00 - 18:00
    10:00 - 19:00
    11:00 - 20:00
    12:00 - 21:00

    When the user selects one of these options, the query needs to query mySQL table and create a form based on the result.
    The SQL query would be a plain select * from tablename (thus returning all row that have the set criteria)
    The first column returned, call it 'name' needs to be the field title.
    Next to this field there will then be a series of radio buttons ->> Present, Late, and Absent.
    Field 2 will be row 2 from the result set with the same series of radio buttons.


    The table below gives an indication of how the returned form should be created. This is assuming a drop-down option had already been selected. (The First row show in the table is not required, I am only showing it to illustrate how I want the fields to be viewed.
    AGENT RADIO_BUTTON_1 - PRESENT RADIO_BUTTON_2 - LATE RADIO_BUTTON_3 - ASENT
    AGENT 1 : RADIO BUTTON WITH PRESENT OPTION RADIO BUTTON WITH LATE OPTION RADIO BUTTON WITH ABSENT OPTION
    AGENT 2 : RADIO BUTTON WITH PRESENT OPTION RADIO BUTTON WITH LATE OPTION RADIO BUTTON WITH ABSENT OPTION


    You help and guidance is greatly appreciate. I think the sources I have been using is completely wrong and I have made absolutely no progress.

  2. #2
    Join Date
    May 2012
    Posts
    11
    Solved:

    PHP Code:
    <?php
    include_once 'includes/db_connect.php';
    include_once 
    'includes/functions.php';
    include_once 
    'includes/formatting.php';
    include_once 
    'includes/js_includes.php';
    include_once 
    'includes/css_includes.php';
    include_once 
    'nav/login_panel.php';

    // Page1.php
    // This page will simply go to the database and give us a list of agents along with their associated IDs
    // The user will tick checkboxes for each agent they wish to enter the status of

    //FUNCTION TO CREATE HTML FOR OPTIONS LIST
    function createOptions($optionList$selectedValue)
    {
        
    $options '';
        foreach (
    $optionList as $option)
        {
            
    $selected = ($option['value']==$selectedValue) ? ' selected="selected"' '';
            
    $options .= "<option value='{$option['value']}'{$selected}>{$option['label']}</option>\n";
        }
        return 
    $options;
    }

    function 
    db_result_to_array($result)
    {
        for (
    $count=0$row $result->fetch_assoc(); $count++) {
            
    $res_array[$count]=$row;
        }
        return 
    $res_array;
    }               


    ?>

    <script language="javascript">
    function getSelectValue(selectID)
    {
        var optionObj = document.getElementById(selectID);
        return optionObj.options[optionObj.selectedIndex].value;
    }

    function reload(form)
    {
        //Adding the unselected options should work fine
        var locationURL = 'page1.php';
        locationURL += '?shift='   + getSelectValue('shift');
        //            locationURL += '&event_date=' + getSelectValue('event_date');
        //            locationURL += '&tertiary_category='  + getSelectValue('tertiary_category');
        //Perform the reload
        self.location = locationURL;
    }

    </script>

    <?php

    if ( isset ( $_GET['shift'] ) && is_numeric $_GET['shift'] ) ){ 
      
    // There's a shift=xxx in the querystring so we use that as the select's default value 
      
    $default_value $_GET['shift']; 
    } else { 
      
    // There's no shift=xxx in the querystring so we can assign a default value here like so 
      
    $default_value 7


    $shift   = isset($default_value)   ? intval($default_value)   : false;

    //DETERMINE SELECTED OPTIONS PASSED ON QUERY STRING

    // CREATE THE DROP-DOWN MENU FROM THE SELECTED DATE
        
    $sql_1 "
                                                    SELECT 
                                                                                    DISTINCT(shift) as value
                                                    ,                               title as label
                                                    FROM 
                                                                                    schedule  
                                                    WHERE 
                                                                                    DATE(start) = '2014-05-07' 
                                                    AND                                title NOT LIKE '%1st%'
                                                    AND                                title NOT LIKE '%2nd%'
                                                    AND                                title NOT LIKE '%Lunch%'    
                                                    AND                                agent_id != 0                                                                                          
                                                    ORDER BY 
                                                                                    title ASC"
    ;
                    
    $optionList $db->query($sql_1);
                    
    $shift_options createOptions($optionList$shift);    

    // CREATE THE SQL TO GRAB ALL USERS FROM THE DB & EXECUTE QUERY - ADDED $SHIFT VARIABLE WHICH IS CREATED BY THE OPTIONS MENU
    // Create some SQL to grab ALL users from the DB (and also execute it)
        
    $sql "
                                                    SELECT 
                                                                                    DISTINCT(agent)    
                                                    ,                               agent_id 
                                                    FROM 
                                                                                    schedule  
                                                    WHERE 
                                                                                    DATE(start) = '2014-05-07' 
                                                    AND                                shift = 
    $shift
                                                    AND                                agent_id != 0                                                
                                                    ORDER BY 
                                                                                    agent ASC"
    ;
        
    $query $db -> query ($sql);
        
    $result $query -> fetchAll (PDO::FETCH_ASSOC);

    // $result should now hold an array of agents. Here, we do a check to make sure and die if not
        
    if ( count ($result) == ) die ("No agents found");
        
    // At this point, we know there's at least 1 record in the $result array and we can loop through without an error

    // STARTING THE HTML PAGE

    ?>

    <body>

    <?php
        
    echo "<body>";                
        echo   
    "<div id='container'>";
        echo                   
    "<div id='content' style='margin-top:-45px;'>";
        echo                   
    "<img src='' alt=''></img>";
        echo                   
    "<h1>Auxilium</h1><br>";    
        echo   
    "<div id='stylized' class='form'>";
                    

    // Start the HTML form       
        
    echo "<form name='page1_form' action='page2.php' method='post'>";
                    
        echo                   
    "<h1>Customer Operations Shift Roster</h1>";
                    echo 
    "</div>";
                    echo 
    "<br>";
                    echo 
    "<br>";        
                    echo 
    "<br>";
                    echo 
    "<br>";

        echo                   
    "<div id='shift_layout' class='form2'>";
        echo                   
    "<table class='shift_layout' border='1'><tbody>";
                    echo       
    "<tr>";
                    echo                           
    "<td>";
                    echo                                       
    "<label>Period :";
                    echo                                       
    "<span class='small2'>Date to update / edit</span>";
                    echo                                       
    "</label>";
                    echo                           
    "</td>";
                    echo                           
    "<td>";                
                    echo                           
    "<input id='event_date' type='text' name='event_date' onChange='reload(this.form)'>";
                    echo                           
    "</td>";
                    echo       
    "</tr>";                 
                    echo       
    "<tr>";
                    echo                           
    "<td>";
                    echo                                       
    "<label>Shift :";
                    echo                                       
    "<span class='small2'>Shift to update / edit</span>";
                    echo                                       
    "</label>";
                    echo                           
    "</td>";
                    echo                           
    "<td>";   
                    echo                                       
    "<select name='shift' id='shift' onChange='reload(this.form)'>";
                    echo                                       
    "<option value=''>--- Select a Shift ---</option>";
                    echo                                       
    $shift_options;
                    echo                                       
    "</select>";
                    echo                           
    "</td>";
                    echo       
    "</tr>";  
                    echo       
    "<tr>";
                    echo                                       
    "<td colspan='2'>";
                    echo                                       
    "<p class='shift_highlight'>Select the agents whom's attendance status you wish to update by checking the tick boxes below.";
                    echo                                       
    "</td>";
                    echo       
    "</tr>";  
    // Loop through all the Agents returned from the query above so that the user can select which one(s) they 
    // want to enter the status data for
        
    foreach ( $result as $agent_info ) {
        
            
    // We now have an array called $agent_info which holds two keys:
            //        agent_id
            //        agent_name
                    
    echo       "<tr>";
                    echo                       
    "<td>";          
                    echo                                       
    "<input type='checkbox' name='agent[]' value='" ucwords($agent_info['agent_id']) . "' id='" $agent_info['agent_id'] . "'>";
        echo                                   
    " ";
                    echo                       
    "</td>";
                    echo                       
    "<td>";                                                   
        echo                                   
    "<for='" $agent_info['agent_id'] . "'>";
                    echo                                       
    "<span class='buttonlabel'>"; echo ucwords($agent_info['agent']);
                    echo                                       
    "</span>";
        echo                                   
    "<br>";
                    echo                       
    "</td>";                 
                    echo       
    "</tr>";
       
        }
        
                   echo       
    "<tr>";    
                    echo                       
    "<td>";   
                    echo                       
    "<br>";  
    // Give the user a submit button
        
    echo                                   "<input type='submit' name='submit' value='Submit'>";
                    echo                       
    "</td>"
                    echo       
    "</tr>";

    // Close the HTML form
        
    echo "</form>";
        echo 
    "</div>";  
                    echo 
    "</table>";
                    echo 
    "</tbody>";
        echo                   
    "</div>";                                
        echo   
    "</div>";

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles