如何将带jAutoCalc插件的动态表多行数据插入MySQL数据库

Got it, let's break this down step by step—we'll cover both collecting your dynamic table data on the frontend and inserting it into MySQL on the backend. I'll use vanilla JavaScript for the frontend and PHP for the backend (since it's widely used with MySQL), but you can adapt the backend to your preferred language if needed.

1. Frontend: Collect Dynamic Row Data #

First, let's assume your table has a consistent structure (each row has inputs for theory score, practical score, and the auto-calculated total from jAutoCalc). Here's a sample table setup that plays nicely with jAutoCalc:

<table id="scoreTable">
  <thead>
    <tr>
      <th>Student Name</th>
      <th>Theory Score</th>
      <th>Practical Score</th>
      <th>Total Score</th>
    </tr>
  </thead>
  <tbody>
    <!-- Dynamic rows will live here (add more with your jAutoCalc setup) -->
    <tr class="score-row">
      <td><input type="text" class="student-name" required></td>
      <td><input type="number" class="theory-score" data-jautocalculator="total = theory + practical" min="0" max="100"></td>
      <td><input type="number" class="practical-score" data-jautocalculator="total = theory + practical" min="0" max="100"></td>
      <td><input type="number" class="total-score" readonly></td>
    </tr>
  </tbody>
  <tfoot>
    <tr>
      <td colspan="3">Subtotal:</td>
      <td><input type="number" id="subtotal" readonly data-jautocalculator="sum(total)"></td>
    </tr>
    <tr>
      <td colspan="3">Grand Total:</td>
      <td><input type="number" id="grand-total" readonly data-jautocalculator="sum(total)"></td>
    </tr>
  </tfoot>
</table>
<button id="save-to-db">Save All Rows to Database</button>

Now add JavaScript to gather all row data and send it to the backend:

document.getElementById('save-to-db').addEventListener('click', function() {
  const allRows = document.querySelectorAll('.score-row');
  const scoreData = [];

  // Loop through each row to collect valid data
  allRows.forEach(row => {
    const name = row.querySelector('.student-name').value.trim();
    const theory = parseFloat(row.querySelector('.theory-score').value);
    const practical = parseFloat(row.querySelector('.practical-score').value);
    const total = parseFloat(row.querySelector('.total-score').value);

    // Skip empty/invalid rows
    if (name && !isNaN(theory) && !isNaN(practical) && !isNaN(total)) {
      scoreData.push({
        student_name: name,
        theory_score: theory,
        practical_score: practical,
        total_score: total
      });
    }
  });

  // Send data to backend if there's valid content
  if (scoreData.length > 0) {
    fetch('save-scores.php', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
      },
      body: JSON.stringify(scoreData),
    })
    .then(res => res.json())
    .then(response => {
      if (response.success) {
        alert(`Success! Saved ${response.rows_inserted} rows to the database.`);
        // Optional: Clear the table or reset inputs here
      } else {
        alert(`Oops, something went wrong: ${response.error}`);
      }
    })
    .catch(err => {
      console.error('Server connection error:', err);
      alert('Failed to connect to the server.');
    });
  } else {
    alert('No valid rows to save! Please check your inputs.');
  }
});

2. Backend: Insert Data into MySQL #

Create a file named save-scores.php to handle the data insertion. We'll use prepared statements to avoid SQL injection and ensure security:

<?php
// Database credentials (update these with your own)
$host = 'localhost';
$db_name = 'your_database_name';
$db_user = 'your_username';
$db_pass = 'your_password';

try {
  // Connect to MySQL using PDO (more secure than mysqli)
  $pdo = new PDO("mysql:host=$host;dbname=$db_name;charset=utf8mb4", $db_user, $db_pass);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // Get JSON data from frontend
  $input_data = json_decode(file_get_contents('php://input'), true);

  if (empty($input_data)) {
    throw new Exception('No data received from the frontend.');
  }

  // Prepare INSERT statement (reusable for all rows)
  $stmt = $pdo->prepare("
    INSERT INTO student_scores (student_name, theory_score, practical_score, total_score)
    VALUES (:name, :theory, :practical, :total)
  ");

  $rows_inserted = 0;

  // Insert each row
  foreach ($input_data as $row) {
    $stmt->execute([
      ':name' => $row['student_name'],
      ':theory' => $row['theory_score'],
      ':practical' => $row['practical_score'],
      ':total' => $row['total_score']
    ]);
    $rows_inserted += $stmt->rowCount();
  }

  // Send success response back to frontend
  echo json_encode([
    'success' => true,
    'rows_inserted' => $rows_inserted
  ]);

} catch (Exception $e) {
  // Send error response
  echo json_encode([
    'success' => false,
    'error' => $e->getMessage()
  ]);
}
?>

3. Critical Setup & Notes #

• Create the MySQL Table: First, make sure you have a table to store the data. Run this SQL query in your MySQL database:

  CREATE TABLE student_scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(255) NOT NULL,
    theory_score DECIMAL(5,2) NOT NULL,
    practical_score DECIMAL(5,2) NOT NULL,
    total_score DECIMAL(5,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  

• Optimize for Large Datasets: If you're inserting hundreds of rows, use a batch INSERT instead of looping through each row to boost performance. Here's how to adjust the PHP code:

  // Alternative batch insert method
  $placeholders = [];
  $values = [];
  foreach ($input_data as $row) {
    $placeholders[] = "(?, ?, ?, ?)";
    $values[] = $row['student_name'];
    $values[] = $row['theory_score'];
    $values[] = $row['practical_score'];
    $values[] = $row['total_score'];
  }
  
  $stmt = $pdo->prepare("
    INSERT INTO student_scores (student_name, theory_score, practical_score, total_score)
    VALUES " . implode(', ', $placeholders)
  );
  $stmt->execute($values);
  $rows_inserted = $stmt->rowCount();
  

• Validate Data: The frontend has basic checks, but add stricter validation (like score ranges, required fields) in both frontend and backend to ensure data integrity.
• jAutoCalc Sync: If you add rows dynamically, make sure jAutoCalc refreshes the calculations before saving. You can call jAutoCalc.refresh() right before collecting data to ensure totals are up-to-date.

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