Form - Update options with MySQL query based on first selection

Question

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'

name are repetitive. still possible. create a select box where options are `name` and values are `ID`. then on form submission you will get that id and then you can update easily. — Alive to die - Anant, Jul 02 '15 at 22:19
If i understood correctly so if a name is selected then update all the rows by second selected value ? — Alive to die - Anant, Jul 02 '15 at 22:27
John and John are same person same with Jane - ID just there for primary column, so basically if John was Selected today location would only show 'Place 1' but if he selected his name tomoro it would show 'Place 2' — RedSparr0w, Jul 02 '15 at 22:27
what you said is not possible. How you can trace that today tomorrow? — Alive to die - Anant, Jul 02 '15 at 22:29
Based on Current Date variable with php at the start of the page — RedSparr0w, Jul 02 '15 at 22:30
yeah im meaning if he used the form tomoro then the other location would show instead because they will use the form each day so wanting the second option to change based on "`date=today AND name=nameselected`" — RedSparr0w, Jul 02 '15 at 22:34

score 1 · Accepted Answer · edited May 23 '17 at 12:32

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.

Thanks, the SQL statement parts not too much of an issue mainly how to run it when the name changes, would there be a way to do it with JQuery as i know a little bit but i know nothing about Ajax — RedSparr0w, Jul 02 '15 at 23:03
i have a table with names and Username and email/password etc how do i reference the 2 together? — RedSparr0w, Jul 02 '15 at 23:04
@RedSparr0w You'll want to use an `inner join`, here's [a primer](http://www.w3schools.com/sql/sql_join_inner.asp) on how to use it and the [official documentation](https://dev.mysql.com/doc/refman/5.0/en/join.html). — Andrew, Jul 23 '15 at 15:12

score 0 · Answer 2 · answered Jul 02 '15 at 23:02

0

Try this

SELECT location 
FROM table 
WHERE name = "John" 
  AND date = "03-07-2015"

answered Jul 02 '15 at 23:02

ιηςπεδιδιε ζαυπαυ

97
7

Form - Update options with MySQL query based on first selection

2 Answers2