php - 从 PHP 调用 MySql 存储过程显示错误

Question

我是 PHP 新手。在 Mysql 数据库中创建一个存储过程，现在从我的 PHP 代码调用我得到这个错误:

SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 6 for routine test.InsertUser is not a variable or NEW pseudo-variable in BEFORE trigger

我的程序是:

DELIMITER $$
CREATE PROCEDURE InsertUser
(
    FirstName   VARCHAR(100),
    LastName        VARCHAR(100),
    Email           VARCHAR(50),
    Age         INT,
    Location        VARCHAR(100),
    OUT Res INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
     SET Res = 0;
    ROLLBACK;       
END;

START TRANSACTION;

INSERT INTO USERS
(firstname,lastname,email,age,location,date)
VALUES
(FirstName,LastName,Email,Age,Location,CURRENT_TIMESTAMP());

COMMIT;
SET Res = 1;
END;
$$

我的 PHP 代码是:

$connection = new PDO($dsn, $username, $password, $options);
$resStatus=false;
    //$res = 0;
    $statement = $connection->prepare("CALL InsertUser(?,?,?,?,?,?)");
    $statement->bindParam(1, $_POST['firstname'], PDO::PARAM_STR, 100);
    $statement->bindParam(2, $_POST['lastname'], PDO::PARAM_STR, 100);
    $statement->bindParam(3, $_POST['email'], PDO::PARAM_STR, 100);
    $statement->bindParam(4, $_POST['age'], PDO::PARAM_INT, 10);
    $statement->bindParam(5, $_POST['location'], PDO::PARAM_STR, 100); 
    $statement->bindParam(6, $res, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 0);         
    // call the stored procedure
    $statement->execute();
    if($res == 1)
    {
        $resStatus = true;
    }

我的 table :

CREATE DATABASE test;

use test;

CREATE TABLE users (
      id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
      firstname VARCHAR(30) NOT NULL,
      lastname VARCHAR(30) NOT NULL,
      email VARCHAR(50) NOT NULL,
      age INT(3),
      location VARCHAR(50),
      date TIMESTAMP
 );

Answer 1

问题是由您的程序中的拼写错误引起的。您在行中键入了“loation”而不是“location”:

(firstname,lastname,email,age,location,date)

现在可以了:

mysql> CREATE PROCEDURE InsertUser
    (
         FirstName       VARCHAR(100),
         LastName        VARCHAR(100),
         Email            VARCHAR(50),
         Age                      INT,
         Location        VARCHAR(100),
         OUT Res                  INT
    )
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
      BEGIN
          SET Res = 0;
          ROLLBACK;
      END;

      START TRANSACTION;

      INSERT INTO USERS
      (firstname,lastname,email,age,location,date)
      VALUES
      (FirstName,LastName,Email,Age,Location,CURRENT_TIMESTAMP());

      COMMIT;
      SET Res = 1;
   END;
   $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> select * FROM users;
Empty set (0.00 sec)

mysql> Call InsertUser("aaa", "bbb", "aaa@test.com", 12, "Earth", @res);
Query OK, 0 rows affected (0.08 sec)

mysql> select * FROM users\G
*************************** 1. row ***************************
       id: 1
firstname: aaa
 lastname: bbb
    email: aaa@test.com
      age: 12
 location: Earth
     date: 2018-07-21 12:09:47
1 row in set (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

说到PHP报错，我查了一下，PARAM_INPUT_OUTPUT好像不稳定。对我来说，它也不起作用。

有一个解决方法:使用 MySQL 变量并在运行 CALL 后选择它:

<?php
try {
    $username = 'user';
    $password = '';
    $conn = new PDO('mysql:host=localhost;dbname=test1', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

// call the stored procedure
$statement = $conn->prepare("CALL InsertUser(?,?,?,?,?,@result)");

$params =  array("aaa", "bbb", "test@test.com", 13, "Earth");
$statement->bindParam(1, $params[0], PDO::PARAM_STR, 100);
$statement->bindParam(2, $params[1], PDO::PARAM_STR, 100);
$statement->bindParam(3, $params[2], PDO::PARAM_STR, 100);
$statement->bindParam(4, $params[3], PDO::PARAM_INT, 10);
$statement->bindParam(5, $params[4], PDO::PARAM_STR, 100);

$statement->execute();

//select result
$sql = "SELECT @result";
$stmt = $conn->prepare($sql);
$stmt->execute();

list($result) = $stmt->fetch(PDO::FETCH_NUM);
var_dump($result);

输出:字符串(1) "1"