数据库错误SQLSTATE[HY093]排查及PHP代码解析求助

Hey there, let's work through your issue step by step—first fixing that annoying SQLSTATE[HY093] error, then breaking down what your PHP code does.

1. Fixing SQLSTATE[HY093] Error #

This error in PDO always boils down to mismatched parameter binding between your SQL query and the parameters you're passing. Here are the most common causes to check:

• Mismatched placeholder names: Ensure the named placeholder in your SQL (like :id) exactly matches the key in your parameters array, including the colon and casing. For example, :ID vs :id will break things.
• Missing/extra parameters: If your SQL has 2 placeholders but your array only has 1 parameter (or vice versa), you'll get this error.
• Wrong placeholder type: Mixing named placeholders (:id) with positional ones (?) will also trigger this—stick to one style.

Looking at your getJoke function, the placeholder and parameter match up correctly (:id in both the SQL and parameters array). Since your code is incomplete, double-check any other calls to the query function elsewhere in your codebase—those are likely where the mismatch is happening.

2. PHP Code Breakdown #

First, here's your code formatted for clarity:

<?php 
include_once __DIR__ . '/../includes/DatabaseConnection.php'; 
include_once __DIR__ . '/../includes/totalJokes.php'; 

function query($pdo,$sql,$parameters=[]){ 
    $query = $pdo->prepare($sql); 
    foreach($parameters as $name=>$value){ 
        $query->bindValue($name,$value); 
    } 
    $query->execute(); 
    return $query; 
} 

function getJoke($pdo,$id){ 
    $query = 'SELECT * FROM `joke` WHERE `id`= :id'; 
    $parameters = [':id'=>$id]; 
    $row = query($pdo,$query,$parameters); 
    return $row->fetch(); 
}

Let's break this down piece by piece:

• Included Files:
  • DatabaseConnection.php: This is almost certainly a file that sets up and returns a PDO database connection instance—without this, none of the database functions would work.
  • totalJokes.php: Based on the name, this likely contains logic to count total jokes in your database, though it's not used in the snippet you provided.
• query() Function:
  This is a reusable helper function to simplify PDO prepared statements:
  • It takes a PDO instance, a SQL string, and an optional array of parameters.
  • It creates a prepared statement with $pdo->prepare() (this prevents SQL injection by separating SQL logic from data).
  • It manually binds each parameter to its named placeholder using bindValue()—though note that $query->execute($parameters) would do the same thing in one line, this explicit binding can be helpful for debugging.
  • Finally, it executes the query and returns the prepared statement object, which you can use to fetch results.
• getJoke() Function:
  This is a specific function to fetch a single joke by its ID:
  • It takes the PDO instance and a joke ID as inputs.
  • It defines a SQL query that selects all columns from the joke table where the id matches the named placeholder :id.
  • It creates a parameters array to map the input $id to the :id placeholder.
  • It calls the reusable query() function to run the query.
  • It uses fetch() to get the first (and only) row from the result set, which will be the joke data for the given ID.

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