基于双输入框的jQuery Ajax MySQL搜索：新增输入1过滤输入2查询需求

Alright, let's tackle this problem step by step. You already have two independent input/button pairs working with Ajax, CGI, and MySQL—now you just need to tie input1's value as a filter for input2's query without breaking existing functionality. Here's how to do it:

First, modify the click handler for btn2 to grab the value from input1 and send it along with input2's value in the Ajax request. Keep btn1's logic completely untouched to maintain independence.

Example code for your JS file:

// Keep btn1's existing logic exactly as it is
document.getElementById('btn1').addEventListener('click', function() {
  const input1Val = document.getElementById('input1').value.trim();
  fetch('your_cgi_script.py', {
    method: 'POST',
    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
    body: `action=get_table1&input1=${encodeURIComponent(input1Val)}`
  })
  .then(res => res.json())
  .then(data => renderTable1(data))
  .catch(err => console.error('Table1 error:', err));
});

// Modify btn2's handler to include input1 as a filter
document.getElementById('btn2').addEventListener('click', function() {
  const input2Val = document.getElementById('input2').value.trim();
  const input1Filter = document.getElementById('input1').value.trim(); // Grab filter value
  
  fetch('your_cgi_script.py', {
    method: 'POST',
    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
    body: `action=get_table2&input2=${encodeURIComponent(input2Val)}&input1_filter=${encodeURIComponent(input1Filter)}`
  })
  .then(res => res.json())
  .then(data => renderTable2(data))
  .catch(err => console.error('Table2 error:', err));
});

// Keep your existing renderTable1 and renderTable2 functions unchanged
function renderTable1(data) { /* ... */ }
function renderTable2(data) { /* ... */ }

Next, update your CGI script to accept the input1_filter parameter and integrate it into the MySQL query for table2. Critical note: Always use parameterized queries to avoid SQL injection—never concatenate user input directly into your SQL string.

Example Python CGI code:

import cgi
import mysql.connector
import json

# Get POST parameters
form = cgi.FieldStorage()
action = form.getvalue('action')

# Database connection (store credentials in a config file instead of hardcoding!)
db = mysql.connector.connect(
  host="your_db_host",
  user="your_db_user",
  password="your_db_pass",
  database="your_db_name"
)
cursor = db.cursor(dictionary=True)
result = []

if action == 'get_table1':
  # Keep original table1 logic intact
  input1_val = form.getvalue('input1')
  query = "SELECT * FROM table1 WHERE your_table1_column = %s"
  cursor.execute(query, (input1_val,))
  result = cursor.fetchall()

elif action == 'get_table2':
  input2_val = form.getvalue('input2')
  input1_filter = form.getvalue('input1_filter', '')  # Default to empty string if not provided
  
  # Build dynamic query with optional filter
  query_components = ["SELECT * FROM table2 WHERE your_table2_column = %s"]
  query_params = [input2_val]
  
  # Add filter only if input1 has a value
  if input1_filter:
    # Replace `your_filter_column` with the actual column in table2 you want to filter
    query_components.append("AND your_filter_column = %s")
    query_params.append(input1_filter)
  
  # Combine parts and execute
  final_query = " ".join(query_components)
  cursor.execute(final_query, tuple(query_params))
  result = cursor.fetchall()

# Return JSON response
print("Content-Type: application/json")
print()
print(json.dumps(result))

# Cleanup
cursor.close()
db.close()

If your table2 has a large dataset, add an index on the column you're filtering with input1's value to speed up the query:

CREATE INDEX idx_table2_filter_column ON table2(your_filter_column);

If you need a fuzzy match (e.g., LIKE instead of exact equality), adjust the query part like this (note: fuzzy matches may reduce index effectiveness):

if input1_filter:
  query_components.append("AND your_filter_column LIKE %s")
  query_params.append(f"%{input1_filter}%")

• Verify that clicking btn1 still works exactly as before, with no interference from input2 or the new filter logic.
• Test btn2 with input1 empty: it should behave just like it did originally.
• Test btn2 with input1 filled: confirm that table2 results are filtered correctly based on input1's value.
• Test edge cases (e.g., special characters in input1) to ensure no SQL injection vulnerabilities exist.

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