PHP表单实现SQL创建与插入及mysqli_connect报错排查求助

First, let’s break down exactly why those errors are popping up:

• The Undefined variable: mysqli_connect error is a simple variable name mix-up. Looking at your code snippet, you assigned your database connection to $db, but later tried to use $mysqli_connect—a variable that was never defined.
• The Call to a member function query() on a non-object error is a direct side effect: since $mysqli_connect doesn’t exist, you’re trying to run a database method on nothing, which fails instantly.

Here’s how to fix this and get your form fully functional:

Step 1: Fix the Connection Variable Reference #

Everywhere you’re using $mysqli_connect (like line 60), replace it with $db—that’s the variable you actually assigned your connection to. This will resolve both errors immediately.

Step 2: Correct Your Database Connection Parameters #

Your current connection line uses null,null,null,'connection', which won’t work for most local or production setups. The mysqli_connect function requires valid credentials:

$db = mysqli_connect('localhost', 'your_username', 'your_password', 'your_database_name') or die("Can't connect to DB:" . mysqli_connect_error());

• localhost: Default for local servers (XAMPP/WAMP/MAMP)
• your_username: Usually root for local environments
• your_password: Leave empty if you don’t have a password set locally, otherwise use your database password
• your_database_name: The name of the database you want to use (if it doesn’t exist, create it first—see Step 3)

Step 3: Ensure the Database & Table Exist #

If your target database doesn’t exist, you need to create it before trying to interact with it. Add this code right after connecting (note: connect without specifying the database first to create it):

// Connect to MySQL server without selecting a database
$db = mysqli_connect('localhost', 'root', '') or die("Can't connect to DB:" . mysqli_connect_error());

// Create database if it doesn't exist
$createDbSql = "CREATE DATABASE IF NOT EXISTS connection";
if ($db->query($createDbSql)) {
    echo "Database is ready (created or already exists)";
} else {
    echo "Error creating database: " . $db->error;
}

// Now select the database
mysqli_select_db($db, 'connection');

// Create your table with IF NOT EXISTS to avoid duplicate errors
$sql = "CREATE TABLE IF NOT EXISTS s (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    submission_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($db->query($sql)) {
    echo "Table is ready";
} else {
    echo "Error creating table: " . $db->error;
}

Step 4: Add Safe Data Insertion Logic #

When handling form submissions, always sanitize user input to prevent SQL injection. Here’s how to integrate form data insertion safely:

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Sanitize input
    $name = mysqli_real_escape_string($db, $_POST['name']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    
    // Insert query
    $insertSql = "INSERT INTO s (name, email) VALUES ('$name', '$email')";
    if ($db->query($insertSql)) {
        echo "Data inserted successfully!";
    } else {
        echo "Insert error: " . $db->error;
    }
}

Example Full Working Snippet #

Putting it all together, your code might look like this:

<?php
// Database connection setup
$db = mysqli_connect('localhost', 'root', '', 'connection') or die("Can't connect to DB:" . mysqli_connect_error());

// Create table if not exists
$sql = "CREATE TABLE IF NOT EXISTS s (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    submission_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

$db->query($sql);

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $name = mysqli_real_escape_string($db, $_POST['name']);
    $email = mysqli_real_escape_string($db, $_POST['email']);
    
    $insertSql = "INSERT INTO s (name, email) VALUES ('$name', '$email')";
    if ($db->query($insertSql)) {
        echo "<p>Success! Your data was added.</p>";
    } else {
        echo "<p>Error: " . $db->error . "</p>";
    }
}
?>

<!-- HTML Form -->
<form method="POST">
    <label for="name">Name:</label>
    <input type="text" id="name" name="name" required>
    
    <label for="email">Email:</label>
    <input type="email" id="email" name="email" required>
    
    <button type="submit">Submit</button>
</form>

Key Best Practices #

• Use IF NOT EXISTS in CREATE statements to avoid duplicate database/table errors
• Always sanitize user input (or use prepared statements for even better security)
• Add error handling for every database query to catch issues early

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