使用MySQL存储过程与PDO实现登录无返回值问题求助

Hey there, let's dig into why your login verification code isn't returning any data from MySQL. I've spotted several issues in both the stored procedure and the PHP/PDO code that could be causing this problem—let's go through them one by one:

The current stored procedure uses unnecessary parameter types and has potential naming ambiguity that might be throwing off the query. Here's how to adjust it:

Problem 1: Unnecessary INOUT Parameters #

You don't need to pass the email/password back as output—login verification only requires input values. Using INOUT can confuse PDO's parameter binding and lead to unexpected behavior.

Problem 2: Ambiguous Parameter Naming #

While your column names (emailLogon, passwordLogon) don't directly clash with the parameter names, it's best practice to prefix parameters to avoid any accidental scope issues (e.g., MySQL might misinterpret email as a column name if context is unclear).

Corrected Stored Procedure #

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `verifLogin`(
    IN `p_email` VARCHAR(75),
    IN `p_password` VARCHAR(30)
)
READS SQL DATA
BEGIN
    -- Return a single count value for efficient validation (instead of selecting all columns)
    SELECT COUNT(*) AS login_match FROM tblLogon 
    WHERE emailLogon = p_email AND passwordLogon = p_password;
END$$
DELIMITER ;

This version uses IN parameters, adds a clear prefix, and returns a focused result that's easier to handle in PHP.

Your PHP code has a few critical mistakes that prevent it from correctly retrieving and processing the stored procedure's result:

Problem 1: Incorrect Parameter Binding #

You're using PDO::PARAM_INPUT_OUTPUT for the email parameter, which is only needed for INOUT parameters. Since we changed the stored procedure to use IN parameters, this is unnecessary and can cause binding failures.

Problem 2: Invalid Result Processing #

$row = (int) $stmt -> fetchAll(PDO::FETCH_ASSOC); is unreliable—fetchAll() returns an array of rows, and casting it to an integer will only give you 1 (non-empty array) or 0 (empty array) without proper context. Instead, fetch the count value directly.

Problem 3: Missing PDO Error Mode #

Without enabling PDO's exception mode, you might not see underlying database errors that are causing the lack of results. Add this right after initializing your PDO connection:

$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Corrected PHP Code #

try {
    $email = $_POST['emailLog'];
    $password = $_POST['passwordLog'];
    
    $sql = "CALL verifLogin (?, ?)";
    $stmt = $PDO->prepare($sql);
    // Bind both parameters as input strings
    $stmt->bindParam(1, $email, PDO::PARAM_STR);
    $stmt->bindParam(2, $password, PDO::PARAM_STR);
    $stmt->execute();
    
    // Fetch the single row with the match count
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $matchCount = (int)$result['login_match'];
    
    if ($matchCount > 0) {
        $res = array("erro" => "false", "message" => "Ok!");
    } else {
        $res = array("erro" => "true", "message" => "Fail!");
    }
    
    echo $res['message'];
} catch (Exception $exc) {
    echo $exc->getTraceAsString();
}

If you still don't get results after these fixes, try these checks:

• Verify that the tblLogon table actually has rows with the email/password you're testing (double-check case sensitivity if your collation is case-sensitive).
• Test the stored procedure directly in MySQL CLI or phpMyAdmin to confirm it returns a count:

  CALL verifLogin('test@example.com', 'yourpassword');
  

• Ensure that the root@localhost user has permission to execute the stored procedure.

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