Mysql/PDO-设置变量 [英] Mysql / PDO - Setting Variable

问题描述

我在尝试设置变量然后在select语句中使用它时遇到了麻烦.我不断收到一般错误"，无法弄清楚我在做什么错.任何输入将不胜感激.我正在尝试使用带有命名参数的子查询来设置变量.

I'm having trouble trying to set a variable then use it in a select statement. I keep getting a "general error" and can't figure out what I'm doing wrong. Any input would be appreciate. I'm trying to set a variable using subqueries with named parameters.

$query = $dbh->prepare("Set @available = (SELECT SUM(payments) FROM payments WHERE customer = :customer) - (SELECT SUM(charges) FROM charges WHERE customer = :customer); SELECT @available");
$query->bindParam(":customer", $customer);
$query->execute();

推荐答案

如果出于某些原因要使用MySQL用户变量，则不需要多查询支持.只要您打开会话(连接)，它们(用户变量)就会存在.因此，您可以执行以下操作

If you want to use MySQL user variables, for some reason, you don't need multi-queries support. They (user variables) live as long as you session (connection) is open. Therefore you can do something like this

$customer = 1;

try {
    $db = new PDO('mysql:host=localhost;dbname=dbname;charset=UTF8', 'user', 'password');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    $sql = "SET @available = (SELECT SUM(payments) FROM payments WHERE customer = ?) - 
                             (SELECT SUM(charges)  FROM charges  WHERE customer = ?)";
    $query = $db->prepare($sql);
    $query->execute(array($customer, $customer));

    $query = $db->prepare("SELECT @available");
    $query->execute();
    $result = $query->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo "Exeption: " .$e->getMessage();
    $result = false;
}
$query = null;
$db = null;

var_dump($result);

示例输出:

array(1) {
  [0]=>
  array(1) {
    ["@available"]=>
    string(6) "100.00"
  }
}

这篇关于Mysql/PDO-设置变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！