MySQL query to form fields based on selection of dropdown

[SF23103]

I am stumped.

 

I have a database similar to this:

 

|    Class Number    |    Class Name    |    Class Date    |

           0001                    MATH                 1/1/2016

           0002                    SCIENCE            2/1/2016

           0003                    HISTORY           3/1/2016

 

I am calling all classes where the date is > current date.  All matching classes are then put into a html dropdown box.

 

I would now like to populate two additional fields with the class number and class date based on the selection of the dropdown.

 

I know I'll need to use AJAX, but I am a little stumped on how to get started.  My first problem is that I have the beginning of the html select tag before:

while($row = $result->fetch_assoc()){

and the end select tag after the }, so I can't put the two additional form fields in the loop.  Any tips on where to go from here?

 

 

Here's what I have so far:

<?php 
// Get required login info
include "/secret/path/to/login.php";

$db = new mysqli('localhost', $username, $password, $database); // Connect to DB using required login info
if($db->connect_errno > 0){ die('Unable to connect to database [' . $db->connect_error . ']'); }

unset($username);// put these variables back to null
unset($password);// put these variables back to null
unset($database);// put these variables back to null

date_default_timezone_set('America/Los_Angeles'); // set default time zone PST

$sql = "SELECT * FROM ft_form_7 WHERE class_full != 'Class Full' AND class_start_date > CURRENT_DATE()";

$result = $db->query($sql);

if(!$result = $db->query($sql)){ die('There was an error running the query [' . $db->error . ']';} // show error if necessary


if(mysqli_num_rows($result) < 1) { (include "/path/to/get-classes-for-registration-no-classes.php"); }

echo '<select name="class_drop_down">'; // opens the dropdown box

while($row = $result->fetch_assoc()){

   echo '<option value="'.$row['class_name'].'">'.$row['class_name'].'</option>';

}

echo '</select>';

$db->close();
$result->free();

?>

[secweb]

You'd start the call like this, then use the element passed to function to get the current "value".

 

echo '<select name="class_drop_down" onchange="myfunc(this)">'; 

[SF23103]

The dropdown is going to be static based on the return from the MySQL query.  So you're proposing I will use that to dynamically change the rest of the form?

 

Alex

[Ch0cu3r]

If you do not want to form to reload when selecting the class name then you need to use AJAX.

 

Example Code

 

HTML

Class Name: <select name="class_drop_down">
<?php
... your code for populating class names here ...
</select>
Class Number: <input type="text" name="class_number" value="" disabled="disabled" />
Class Date: <input type="text" name="class_date" value="" disabled="disabled" />

<!-- place this code at the bottom of your page before cloing </body></html> tags -->
<script src="https://code.jquery.com/jquery-2.1.4.min.js"></script>
<script>
// using jquery to apply an onchange event on the select element named class_drop_down
$('select[name="class_drop_down"]').on('change', function() {
    // do an ajax request to get_class_details.php, passing selected the class name
    $.ajax({
        url: 'get_class_details.php',
        method: 'POST',
        data: { class_name: $(this).val()},
        dataType: 'json',
        // this function is called with the response from the request
        success: function(response) {
            // make sure a error was not returned
            if(response.error === undefined) {
                // set values for class number and date fields as follows
                $('input[name="class_number"]').attr('value', response.class_number);
                $('input[name="class_date"]').attr('value', response.class_date);
            }
            else {
                // demo purposes only, output error message as an alert
                alert(response.error);
            }
        },
    });
});
</script>

get_class_details.php

<?php
// set the content type to be JSON
// returning the result of the AJAX request to be in JSON format
header('Content-type', 'text/json');

include "/secret/path/to/login.php";

$db = new mysqli('localhost', $username, $password, $database); // Connect to DB using required login info
if($db->connect_errno > 0){ die('Unable to connect to database [' . $db->connect_error . ']'); }

// check class_name exists in POST
if(isset($_POST['class_name']))
{
    // make sure it is not empty
    if(!empty(trim($_POST['class_name'])))
    {
        // use prepared to statement for get class number and data where the class the name matches
        $stmt = $db->prepare('SELECT class_number, class_date FROM ft_form_7 WHERE class_name = ?');
        if($stmt)
        {
            // bind class name to the query
            $stmt->bind_param('s', $_POST['class_name']);

            // the class_number and class_date values returned by the query will be bound to these variables
            $stmt->bind_result($class_number, $class_date);

            // execute prepared query
            $stmt->execute();

            // attempt to get first result
            if($stmt->fetch())
            {
                // define our data array with the query result
                $data = array(
                    'class_number' => $class_number,
                    'class_date' => $class_date,
                );
            }
            else
            {
                // no row returned, set error message stating class could not be found
                $data = array('error' => 'Class name "' . htmlentities($_POST['class_name'], ENT_QUOTES) . '" cannot be found');
            }
        }
        // problem with the query
        else
        {
            // trigger error message for server admin containing mysql error message
            trigger_error('Unable to query ft_form_7 table: ' . $db->error);

            // output generic error message for user
            $data = array('error' => 'Unable to perform this action at this time due to a server error');
        }
    }
    else
    {
        // class name is empty, set error message
        $data = array('error' => 'Class name cannot be empty');
    }
}
else
{
    // class name not set in POST
    $data = array('error' => 'Invalid Request');
}

// output result in JSON
echo json_encode($data);

Edited September 15, 2015 by Ch0cu3r

[SF23103]

Wow Ch0cu3r that is way more than I expected, thank you so much.  That looks great, and I am working on implementing it.  I got my class names to populate, but right now for some reason the get_class_details.php isn't running, as I am getting a server 500 error.  I'm trying to turn on error reporting at it doesn't seem to give me any information.  Thinking it is not running the script at all due to a syntax error I tried taking out the first line 

header('Content-type', 'text/json');

 and it actually ran the script, of course giving an invalid request error because there is nothing set in POST, but it actually ran.  I went back to the html page, and tried it out without the header info and expectedly it gave me a "Unable to perform this action at this time due to a server error" pop up message.  I know we need to return the request in JSON, but it doesn't seem to like that way of doing it.  Still working on finding a solution but I thought I would post this in case you had any ideas!

 

Thanks again for your help... more than appreciated.  

Edited September 15, 2015 by SF23103

[SF23103]

EDIT:  Got it working!  Just changed all the class_number to course_number throughout the whole thing instead of trying to move it to class_number after all the MySQL stuff was done. That way I did't miss any.  Still leaving out the below header line, as it throws an error.  Its working great, and you are my hero!

 

Thanks!

 

Wow Ch0cu3r that is way more than I expected, thank you so much.  That looks great, and I am working on implementing it.  I got my class names to populate, but right now for some reason the get_class_details.php isn't running, as I am getting a server 500 error.  I'm trying to turn on error reporting at it doesn't seem to give me any information.  Thinking it is not running the script at all due to a syntax error I tried taking out the first line 

header('Content-type', 'text/json');

 and it actually ran the script, of course giving an invalid request error because there is nothing set in POST, but it actually ran.  I went back to the html page, and tried it out without the header info and expectedly it gave me a "Unable to perform this action at this time due to a server error" pop up message.  I know we need to return the request in JSON, but it doesn't seem to like that way of doing it.  Still working on finding a solution but I thought I would post this in case you had any ideas!

 

Thanks again for your help... more than appreciated.  

Edited September 15, 2015 by SF23103

[Ch0cu3r]

Sorry  header('Content-type', 'text/json');  should of been  header('Content-Type: text/json');. Looks like I got nodejs setHeader function mixed up with PHP's header function there.

 

You got the "Unable to perform this action at this time due to a server error" message because of a MySQL error. Looks like the error was because your actual column names differ to the ones you mentioned. For reference whenever you get a 500 Internal Server error check your servers error logs for the cause.

[SF23103]

I got this working great, and when I set it up to send the data, it only sends the form field in the select dropdown.  It is not sending any of the values that are filled by AJAX.  Is it not truly changing the value of the text box?  Is there any way to truly fill that text box with the value for the form submission?

[SF23103]

fixed the last issue:

 

Changed 

 

$('input[name="class_start_time"]').attr('value' , response.class_start_time);

to

$('input[name="class_start_time"]').val(response.class_start_time);