如何在Node.js+Express环境下将表单数据存入MySQL数据库

Hey there! Since you're totally new to Node.js/Express/Handlebars, let's break this down step by step—no confusing jargon, just straightforward steps you can follow to get your form data into a database.

Step 1: Set Up Your Node.js Project First #

First, create a new folder for your project, open a terminal in it, and run these commands to initialize a Node project and install all the tools you'll need:

npm init -y
npm install express express-handlebars body-parser mysql2

• express: The backend framework to handle requests
• express-handlebars: To render your .hbs templates
• body-parser: To read form data sent from the frontend
• mysql2: To connect and interact with a MySQL database (we'll use MySQL for this example since it's common for beginners)

Step 2: Create Your Basic Express Server #

Create a file named server.js in your project folder with this code:

const express = require('express');
const exphbs = require('express-handlebars');
const bodyParser = require('body-parser');
const mysql = require('mysql2');

const app = express();

// Configure Handlebars as the template engine
app.engine('hbs', exphbs.engine({ extname: '.hbs' }));
app.set('view engine', 'hbs');

// Use body-parser to parse form data
app.use(bodyParser.urlencoded({ extended: true }));

// Route to render your form page
app.get('/', (req, res) => {
  res.render('index'); // Assumes your form is in views/index.hbs
});

// Start the server
const PORT = 3000;
app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

Step 3: Fix Your Form (This is Critical!) #

Your current form has two big issues that prevent backend from receiving data:

1. Empty action attribute (needs to point to your backend route)
2. Most fields don't have a name attribute (the backend uses this to identify data)

Here's the fixed form code (replace your existing .hbs form with this):

<div class="row">
  <form action="/submit-sensor" method="post" class="col s12">
    <div class="row">
      <div class="col s3 offset-s1">
        <label>Sensor Type</label><br>
        <label>
          <input id="combo" class="with-gap" name="sensorType" type="radio" value="temp_humid"/>
          <span>Temperature & Humidity</span>
        </label><br>
        <label>
          <input id="temp" class="with-gap" name="sensorType" type="radio" value="temp"/>
          <span>Temperature</span>
        </label><br>
        <label>
          <input id="humid" class="with-gap" name="sensorType" type="radio" value="humid"/>
          <span>Humidity</span>
        </label>
      </div>
    </div>
    <div class="row">
      <div class="input-field col s3 offset-s1">
        <select id="sensorForest" name="sensorForest">
          <option value="" disabled selected>Choose your forest</option>
          <optgroup label="The Netherlands">
            <option value="streekbos">Streekbos Bovenkarspel</option>
          </optgroup>
          <optgroup label="United States of America">
            <option value="losAngeles">Los Angeles National Forest</option>
          </optgroup>
        </select>
        <label>Forest</label>
      </div>
      <div class="input-field col s3">
        <textarea id="textarea2" class="materialize-textarea" data-length="50" name="sensorLocation"></textarea>
        <label for="textarea2">Enter sensor Location</label>
      </div>
    </div>
    <div class="row">
      <div class="input-field col s6 offset-s1">
        <div class="input-field col s6">
          <input id="latitude" type="text" class="validate" name="latitude">
          <label for="latitude">Latitude</label>
        </div>
        <div class="input-field col s6">
          <input id="longitude" type="text" class="validate" name="longitude">
          <label for="longitude">Longitude</label>
        </div>
      </div>
    </div>
    <div>
      <div class="row">
        <div class="col s12 offset-s1">
          <button class="btn waves-effect waves-light" type="submit">
            Submit <i class="material-icons right">send</i>
          </button>
        </div>
      </div>
    </div>
  </form>
</div>

Key changes:

• Added action="/submit-sensor" to the form (points to our upcoming backend route)
• Added name attributes to every input/select/textarea
• Added value attributes to radio buttons (so backend knows which option was selected)

Step 4: Connect to Database & Save Form Data #

First, create a MySQL database (let's call it sensor_db) and a table to store your data. Run this SQL query in your MySQL client:

CREATE TABLE sensors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sensor_type VARCHAR(50) NOT NULL,
  forest VARCHAR(100) NOT NULL,
  location TEXT,
  latitude VARCHAR(50),
  longitude VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now, update your server.js to add the database connection and the POST route to handle form submissions:

// ... (keep all existing code above)

// Create database connection
const db = mysql.createConnection({
  host: 'localhost', // Usually localhost
  user: 'your_mysql_username', // e.g., root
  password: 'your_mysql_password',
  database: 'sensor_db'
});

// Connect to database
db.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL database!');
});

// POST route to receive form data and save to database
app.post('/submit-sensor', (req, res) => {
  const { sensorType, sensorForest, sensorLocation, latitude, longitude } = req.body;

  // Insert data into database
  const query = 'INSERT INTO sensors (sensor_type, forest, location, latitude, longitude) VALUES (?, ?, ?, ?, ?)';
  db.query(query, [sensorType, sensorForest, sensorLocation, latitude, longitude], (err, result) => {
    if (err) throw err;
    // Redirect back to form or show success message
    res.send('Sensor data saved successfully! <a href="/">Go back</a>');
  });
});

// ... (keep the server listen code at the end)

Make sure to replace your_mysql_username and your_mysql_password with your actual MySQL credentials.

Step 5: Test It All Out #

1. Start your MySQL server (if it's not running already)
2. Run your Node server: node server.js
3. Open http://localhost:3000 in your browser
4. Fill out the form and submit it
5. Check your MySQL sensors table—you should see the new entry!

Bonus: No Page Refresh (AJAX) #

If you don't want the page to reload when submitting, replace your frontend script with this (uses the Fetch API):

const form = document.querySelector('form');
form.addEventListener('submit', async (e) => {
  e.preventDefault(); // Stop default form reload

  const formData = new FormData(form);
  const data = Object.fromEntries(formData);

  try {
    const response = await fetch('/submit-sensor', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/x-www-form-urlencoded',
      },
      body: new URLSearchParams(data),
    });

    if (response.ok) {
      alert('Data saved successfully!');
      form.reset(); // Clear the form
    }
  } catch (err) {
    console.error('Error:', err);
  }
});

Quick Beginner Tips #

• Always check the terminal for errors—Node will tell you if something's wrong (like wrong database credentials)
• Use console.log(req.body) in the POST route to make sure you're receiving all form data
• Make sure your .hbs files are in a views folder (Express expects this by default)

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