RedSparr0w RedSparr0w - 4 months ago 7
SQL Question

Form - Update options with MySQL query based on first selection

What I want is for the 2nd set of options to change to whatever is available once a name is selected based on

date=today and name=selectedname
also

Here is the basics of the table:

ID | name | location | Date
1 |John | place 1 | 03-07-2015
2 |Jane | place 2 | 03-07-2015
3 |John | place 2 | 04-07-2015
4 |Jane | place 2 | 04-07-2015


I am just not sure how to run the SQL statement

SELECT location
FROM table
WHERE name = "selected name"
AND date = CURDATE()
based on when Select "name" changes

Here is an example of my current code :



<form name="test" id="test">
<div>
<label for="name">Name:</label>
<select name="name" id="name" required>
<option value="" disabled selected>Please Select</option>
<option value="John">John</option>
<option value="Jane">Jane</option>
</select>
</div>
<div>
<label for="location">location:</label>
<select name="location" id="location" required>
<option value="" disabled selected>Please Select</option>
<option value="place 1">place 1</option>
<option value="place 2">place 2</option>
</select>
</div>
<div>
<label for="startend">Start or Finish:</label>
<select name="startend" id="startend" required>
<option value="" disabled selected>Please Select</option>
<option value="Start">Start</option>
<option value="End">Finish</option>
</select>
</div>
<div class="submit">
<input type="submit" name="submit" value="Submit" class="submit" />
</div>
</form>





so once John selects 'John' from name selection the location select box will only contain 'Place 1' if todays date is 03-07-2015 but if todays date was 04-07-2015 then it would only contain 'Place 2'

Answer

If you would like to select the location of John today, you just need to select based on both name and date like this:

SELECT location 
FROM table 
WHERE name = "John" 
  AND date = CURDATE()

If you want to update the second select box on-the-fly after the name is selected, you'll probably want to use ajax. Here are some good examples of how to populate a select box with an ajax call, call that code the name or location fields change with something like:

$("#name, #location").change(function(){
    $.ajax({
        url: "fetchData.php",
        data: $('form').serialize(),
        dataType: "json",
        success: function(data) {
            alert("Next we populate the <select> with the data received...");
        }
    });
});

The fetchDate.php file is something you'll need to create. This PHP file will simply grab the correct location(s) (using the SQL examples above) and echo them out in JSON format for the JS to work with (convert a PHP array to JSON with the json_encode() function).

You'll also ideally want to use a dedicated names table and just reference John's name ID in here, otherwise you'll hit issues when the user needs to change their name or when multiple users have the same name.

Comments