已成功连接MySQLi数据库，但无法执行最简单的SQL查询

Hey there, I totally get how frustrating this must be—you’ve got a working search engine built with MySQL, you’ve confirmed your MySQLi connection is solid, but no matter what query examples you try, nothing executes successfully. Let’s walk through practical steps to diagnose and fix this.

First, Turn On MySQLi Error Reporting #

The biggest mistake folks make starting with MySQLi is skipping error reporting. By default, MySQLi won’t spit out detailed errors, so you’re left guessing what’s broken. Add this line right after you establish your database connection:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

This forces MySQLi to throw clear exceptions for any issues—like a typo in a table name, missing permissions, or invalid syntax—so you don’t have to play detective blind.

Verify Your Query Syntax & Implementation #

Let’s use a simple, tailored example to test. Suppose your search engine uses a table named search_index with fields id, title, and content. Here are two common query types you can test:

For Basic Non-Prepared Queries #

// Assume $mysqli is your already-connected MySQLi object
$searchTerm = "example";
$query = "SELECT id, title, content FROM search_index WHERE title LIKE '%{$searchTerm}%'";
$result = $mysqli->query($query);

// Check if the query worked
if ($result) {
    // Fetch and display results
    while ($row = $result->fetch_assoc()) {
        echo "<p>{$row['title']}</p>";
    }
} else {
    // Show the exact error
    echo "Query failed: " . $mysqli->error;
}

• Double-check that table/field names match exactly (MySQL is case-sensitive on systems like Linux).
• If names have spaces or special characters, wrap them in backticks: `search index`.

For Prepared Statements (More Secure for User Input) #

If you’re using prepared statements (recommended for search queries to avoid SQL injection), make sure parameter binding is correct:

$searchTerm = "%example%";
$query = "SELECT id, title, content FROM search_index WHERE title LIKE ?";

// Prepare the statement
$stmt = $mysqli->prepare($query);
if (!$stmt) {
    echo "Prepare failed: " . $mysqli->error;
    exit;
}

// Bind the parameter ("s" = string type)
$stmt->bind_param("s", $searchTerm);

// Execute and fetch results
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo "<p>{$row['title']}</p>";
}

• Use ? as placeholders (no quotes around them—MySQLi handles that automatically).
• Check $stmt->error if prepare/execute steps fail.

Rule Out Common Pitfalls #

• Don’t mix MySQL and MySQLi functions: Ensure you’re not accidentally using old mysql_* functions anywhere—they won’t work with your MySQLi connection.
• Check database permissions: Even if you can connect, your user might lack SELECT permissions on your search table. Run this directly in MySQL (phpMyAdmin or command line) to verify:

  SHOW GRANTS FOR your_database_user@localhost;
  

• Test your query directly in MySQL: Copy your SQL statement (replace variables with actual values) and run it in a MySQL client. If it fails there, the problem is with your SQL, not your PHP code.

Start with enabling error reporting first—it’s the fastest way to get a clear picture of what’s going wrong. Once you have the specific error message, fixing it will be way easier.

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