下拉列表提交JSON数据至数据库时存ID而非日期时间问题排查

Hey there, the issue here is super straightforward—your dropdown options are sending the ID values from your JSON to the backend, not the actual date/time text users see. Let's break this down:

The Root Cause #

Look at your JavaScript code where you build the dropdown options:

html_code += '<option value="' + value.id + '">' + value.avail + '</option>';

You're setting the <option>'s value attribute to value.id (the numeric ID from your JSON), but displaying value.avail (the human-readable date/time). When the form submits, browsers send the value of the selected option, not the visible text. That's why your database is getting IDs instead of dates/times.

Solution 1: Submit the Visible Date/Time Directly #

If your ArenaDate and ArenaTime database fields are meant to store the actual date/time strings users see, just change the value in your option to use value.avail instead of value.id:

Update this line in your JavaScript:

html_code += '<option value="' + value.id + '">' + value.avail + '</option>';

To this:

html_code += '<option value="' + value.avail + '">' + value.avail + '</option>';

Now when the form submits, $_POST['date'] and $_POST['time'] will hold the actual date/time text, which will get inserted into your database correctly.

Solution 2: Keep IDs (Better for Database Normalization) #

If your JSON IDs are linked to a separate table of dates/times (a more normalized database design), you should keep storing the IDs in tblbooking—but you'll need to join tables when querying to show the actual dates/times to users.

For example, if you have:

• A tbl_dates table with id and date_string columns
• A tbl_times table with id, time_string, and date_id columns

You can query bookings with the actual dates/times like this:

SELECT 
  b.userEmail,
  d.date_string AS ArenaDate,
  t.time_string AS ArenaTime
FROM tblbooking b
JOIN tbl_dates d ON b.ArenaDate = d.id
JOIN tbl_times t ON b.ArenaTime = t.id
WHERE b.userEmail = :useremail;

This approach avoids duplicate data in your database and makes it easier to update date/time formats later.

Quick Note About Data Types #

If your ArenaDate field is a MySQL DATE type and ArenaTime is TIME, make sure the value.avail strings match the required formats (e.g., YYYY-MM-DD for dates, HH:MM:SS for times). If your JSON uses a different format, you'll need to convert it either in the frontend or before inserting into the database.

内容的提问来源于stack exchange，提问作者Smiddy