SF23103 Posted September 15, 2015 Share Posted September 15, 2015 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(); ?> Quote Link to comment https://forums.phpfreaks.com/topic/298174-mysql-query-to-form-fields-based-on-selection-of-dropdown/ Share on other sites More sharing options...
secweb Posted September 15, 2015 Share Posted September 15, 2015 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)">'; Quote Link to comment https://forums.phpfreaks.com/topic/298174-mysql-query-to-form-fields-based-on-selection-of-dropdown/#findComment-1520870 Share on other sites More sharing options...
SF23103 Posted September 15, 2015 Author Share Posted September 15, 2015 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 Quote Link to comment https://forums.phpfreaks.com/topic/298174-mysql-query-to-form-fields-based-on-selection-of-dropdown/#findComment-1520871 Share on other sites More sharing options...
Solution Ch0cu3r Posted September 15, 2015 Solution Share Posted September 15, 2015 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/298174-mysql-query-to-form-fields-based-on-selection-of-dropdown/#findComment-1520880 Share on other sites More sharing options...
SF23103 Posted September 15, 2015 Author Share Posted September 15, 2015 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/298174-mysql-query-to-form-fields-based-on-selection-of-dropdown/#findComment-1520916 Share on other sites More sharing options...
SF23103 Posted September 15, 2015 Author Share Posted September 15, 2015 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/298174-mysql-query-to-form-fields-based-on-selection-of-dropdown/#findComment-1520918 Share on other sites More sharing options...
Ch0cu3r Posted September 15, 2015 Share Posted September 15, 2015 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. Quote Link to comment https://forums.phpfreaks.com/topic/298174-mysql-query-to-form-fields-based-on-selection-of-dropdown/#findComment-1520924 Share on other sites More sharing options...
SF23103 Posted September 22, 2015 Author Share Posted September 22, 2015 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? Quote Link to comment https://forums.phpfreaks.com/topic/298174-mysql-query-to-form-fields-based-on-selection-of-dropdown/#findComment-1521323 Share on other sites More sharing options...
SF23103 Posted September 22, 2015 Author Share Posted September 22, 2015 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); Quote Link to comment https://forums.phpfreaks.com/topic/298174-mysql-query-to-form-fields-based-on-selection-of-dropdown/#findComment-1521325 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.