在一个响应中返回多个响应数据 [英] return multiple response data in one response
本文介绍了在一个响应中返回多个响应数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在我的科目表中，我按学期和月份列出了所有学生的课程以及每个月的分数
<预><代码>[{"id": "4", - 这是主题 ID用户名":1"，"name": "生物","semester": "3", - 学期"month": "5", - 月份"points": "652" - 该类的点数时间":2017-06-18 22:45:04"},{"id": "3", - 这是主题 ID用户名":1"，"name": "数学","semester": "3", - 学期"month": "4", - 月份"points": "33" - 本课程的分数时间":2017-05-15 22:45:04"},{"id": "2", - 这是主题 ID用户名":1"，"name": "化学","semester": "1", - 学期"month": "3", - 月份"points": "22" - 本课程的分数时间":2017-04-11 22:45:04"},{"id": "1", - 这是主题 ID用户名":1"，"name": "phy","semester": "1", - 学期"month": "2", - 月份"points": "10" - 本课程的分数时间":2017-02-10 22:45:04"}]这是我试过的
$sql = "SELECT users.id userid,users.name username,subjects.id subjectid, subject.name subjectname, subject.points activepts FROM tbusers AS users INNER JOIN tbsubjects AS subject ON users.id = subject.userid WHERE users.id = '$userid' ORDER BY subject.time DESC";尝试 {$db = 新的 db();$db = $db->connect();$stmt = $db->prepare($sql);$stmt->execute();$user = $stmt->fetchAll(PDO::FETCH_OBJ);$db = 空；如果(空($用户)){$response->getBody()->write('{错误":{"message":"无效"}}');} 别的 {$response->getBody()->write(json_encode($user));}} catch(PDOException $e) {}由于 fetchAll，我从查询中获得的当前输出是每个响应的多个响应，我可以将其更改为 fetch 但它不会获取其他数据
我的问题是如何将它们合并为一个响应并在预期输出中返回以下数据(我添加了每个字段的一些描述来解释它)
预期输出
<预><代码>[{"userid": "1", - 来自用户表"username": "joe", - 来自用户表"subjectsid": "1", - 在这种情况下，学生的第一个主题 ID 是 phy"subjectname": "bio", - 当前主题名称"activepts": "652", - 当月的点数"totalpts": "717", - 该学生所有科目的总分"sem1": "32", - 该学生第一学期所有科目的总分"sem2": "0", - 该学生第二学期所有科目的总分"sem3": "685", - 该学生第 3 学期所有科目的总分}] 解决方案
问题是，您想要获取主题，而不是学生.所以，我颠倒了 FROM 和 LEFT JOIN.因此，当您想要一个主题列表时，您可以从 SELECT ... FROM 主题开始.然后，如果您需要每个主题的其他详细信息(如用户名等)，则应用 LEFT JOIN，这意味着:将所有需要的详细信息(用户名等)加入 LEFT 表的每条记录，例如主表(在你的情况下是表主题").
祝你好运！
connect();/** 我重命名了用户 ID 变量(从 $userId 到 $userid1)以向您展示您可以* 如果您愿意，可以提供更多用户.然后你只需要扩展* sql 语句中的 WHERE 子句和绑定数组.*/$userid1 = 1;/** sql 语句 - 它将被准备.** ========================================================* 我不确定以下字段 - 因为你* 没有为他们提供适当的选择标准:** 1) "subjectsid": "1", - 在这种情况下学生的第一个主题 ID 是 phy* 2) "subjectname": "bio", - 当前主题名* ========================================================*/$sql = '选择主题.用户ID，users.name AS 用户名，(选择 ID从 tbsubjectsWHERE userid = subject.userid按 id ASC 排序限制 1) 作为主题 ID，(选择名称从 tbsubjects在哪里用户 ID = 主题.用户 ID按时间排序 DESC限制 1) 作为主题名，(SELECT IFNULL(SUM(points), 0)从 tbsubjects在哪里用户 ID = 主题.用户 IDAND 月份 = DATE_FORMAT(NOW(), "%c")) 作为活动点，IFNULL(SUM(subjects.points), 0) AS totalpts,(SELECT IFNULL(SUM(points), 0)从 tbsubjects在哪里用户 ID = 主题.用户 IDAND 学期 = 1) 作为 sem1,(SELECT IFNULL(SUM(points), 0)从 tbsubjects在哪里用户 ID = 主题.用户 IDAND 学期 = 2) 作为 sem2,(SELECT IFNULL(SUM(points), 0)从 tbsubjects在哪里用户 ID = 主题.用户 IDAND 学期 = 3) 作为 sem3从tbsubjects AS 科目左加入 tbusers 作为用户 on users.id = subject.useridWHERE subject.userid = :userid1GROUP BY subject.useridORDER BY subject.time DESC';/** 准备好的sql语句的输入参数列表.*/$绑定=数组(':userid1' =>$userid1,);/** 准备并验证sql 语句.** --------------------------------------------------------------------------------* 如果数据库服务器无法成功准备语句，PDO::prepare()* 返回 FALSE 或发出 PDOException(取决于错误处理设置).* --------------------------------------------------------------------------------*/$statement = $connection->prepare($sql);如果(！$语句){throw new UnexpectedValueException('无法准备sql语句！');}/** 将输入参数绑定到准备好的语句.** -----------------------------------------------------------------------------------* 与 PDOStatement::bindValue() 不同，使用 PDOStatement::bindParam() 时变量* 被绑定作为参考，并且只会在* PDOStatement::execute() 被调用.* -----------------------------------------------------------------------------------*/foreach ($bindings as $key => $value) {$bound = $statement->bindValue(getInputParameterName($key), $值, getInputParameterDataType($value));如果(！$绑定){throw new UnexpectedValueException('不能绑定输入参数！');}}/** 执行准备好的语句.** ------------------------------------------------------------------* PDOStatement::execute 成功时返回 TRUE，失败时返回 FALSE.* ------------------------------------------------------------------*/$execute = $statement->execute();如果(！$执行){throw new UnexpectedValueException('准备好的语句无法执行！');}/** 获取用户列表 - 对象数组.*/$users = $statement->fetchAll(PDO::FETCH_OBJ);如果($users === FALSE){throw new UnexpectedValueException('获取用户列表失败！');}/** 关闭连接.*/$connection = NULL;/** 处理结果.*/如果(空($用户)){$response->getBody()->write('{错误": {"message":"无效"}}');} 别的 {$response->getBody()->write(json_encode($users));}} catch (PDOException $exc) {echo $exc->getMessage();//$logger->log($exc);出口();} 捕获(异常 $exc){echo $exc->getMessage();//$logger->log($exc);出口();}/*** 通过绑定数组中的键获取输入参数的名称.** @param int|string $key bindings 数组中输入参数的键.* @return int|string 输入参数的名称.*/函数 getInputParameterName($key) {返回 is_int($key) ?($key + 1) : (':' .ltrim($key, ':'));}/*** 通过其值获取 PDO::PARAM_* 常量，例如输入参数的数据类型.** @param混合 $value 输入参数的值.* @return int PDO::PARAM_* 常量.*/函数 getInputParameterDataType($value) {$dataType = PDO::PARAM_STR;如果 (is_int($value)) {$dataType = PDO::PARAM_INT;} elseif (is_bool($value)) {$dataType = PDO::PARAM_BOOL;}返回 $dataType;}对于我的项目，我开发了一个 DbAdapter 类.方法名称是不言自明的.因此，每个网页中不再有意大利面条代码 :-) 而只是:
- sql 语句，
- 绑定数组，
- 调用数据库适配器中的相应方法和
- 断开与数据库的连接
您的问题的解决方案如下所示:
$userid1,);//获取用户.$users = $dbAdapter->fetchAll($sql, $bindings);//断开与数据库的连接.$dbAdapter->disconnect();/** 处理结果.*/如果(空($用户)){//...} 别的 {//...}要调用的适配器方法是 public 方法:
- connect:连接到数据库，例如创建一个 PDO 实例，例如创建一个数据库连接.
- 断开连接:断开与数据库的连接.
- fetchAll:一次获取更多记录.返回一个数组数组.因此，每个元素都是一个数组，对应一个 db 记录.
- fetchOne:仅获取一条记录.
- fetchColumn:获取列值.
- update:执行 UPDATE 查询.返回受影响的行数.
- delete:执行 DELETE 查询.返回受影响的行数.
- insert:执行 INSERT 查询.返回最后一个插入 ID.
- getLastInsertId:在执行 INSERT 操作后返回最后一个插入 ID.
就是这样:-)
PDO::ERRMODE_EXCEPTION,PDO::ATTR_EMULATE_PREPARES =>错误的，PDO::ATTR_PERSISTENT =>真的，)){$this->setConnectionConfigs(array('主机' =>$主机，'数据库名称' =>$数据库名，'用户名' =>$用户名，'密码' =>$密码，'端口' =>$端口，'字符集' =>$字符集，'司机姓名' =>$司机姓名，'driverOptions' =>$driverOptions，));}/*** 连接数据库，例如创建一个 PDO 实例.** @return $this* @throws PDOException*/公共函数连接(){if (!isset($this->connection) || !$this->connection) {尝试 {$this->connection = new PDO($this->createDsn($this->connectionConfigs['host'], $this->connectionConfigs['dbname'], $this->connectionConfigs['port'], $this->connectionConfigs['charset'], $this->connectionConfigs['driverName']), $this->connectionConfigs['username'], $this->connectionConfigs['password'], $this->connectionConfigs['driverOptions']);} catch (PDOException $pdoException) {echo $pdoException->getMessage();出口();}}返回 $this;}/*** 断开与数据库的连接.** @return $this*/公共函数断开(){$this->connection = NULL;返回 $this;}/*** 创建 DSN 字符串.** @param string $host 主机.* @param string $dbname 数据库名称.* @param string $port 端口.* @param string $charset 字符集.* @param string $driverName 驱动程序名称.* @return 字符串 DSN 字符串.*/私有函数 createDsn($host, $dbname, $port, $charset, $driverName) {开关($driverName){默认值://mysql$dsn = sprintf('%s:host=%s;port=%s;dbname=%s;charset=%s', $司机姓名, $host, $端口, $dbname, $字符集);休息;}返回 $dsn;}/*** 通过执行 SELECT sql 语句获取数据.** @param string $sql Sql 语句.* @param array $bindings [可选] 输入参数.* @param integer $fetchMode [optional] PDO 语句的获取模式.* 必须是 PDO::FETCH_* 常量之一.* @param混合 $fetchArgument [可选] 获取 PDO 语句的参数.* @param array $fetchConstructorArguments [可选] PDO 语句的构造函数参数* 当获取模式为 PDO::FETCH_CLASS 时.* @return array 包含结果集中行的数组，失败时为 FALSE.* @throws UnexpectedValueException*/公共函数 fetchAll($sql, array $bindings = array(), $fetchMode = PDO::FETCH_ASSOC, $fetchArgument = NULL, array $fetchConstructorArguments = array()) {$this->prepareStatement($sql)->bindInputParameters($bindings)->executePreparedStatement();尝试 {如果(isset($fetchArgument)){$data = $this->getStatement()->fetchAll($fetchMode, $fetchArgument, $fetchConstructorArguments);} 别的 {$data = $this->getStatement()->fetchAll($fetchMode);}如果 ($data === FALSE) {throw new UnexpectedValueException('获取数据失败！');}返回 $data;} 捕获(异常 $exception){echo $exception->getMessage();出口();}}/*** 通过执行 SELECT sql 语句从结果集中获取下一行.* fetch mode 属性决定了 PDO 如何返回行.** @param string $sql Sql 语句.* @param array $bindings [可选] 输入参数.* @param integer $fetchMode [optional] PDO 语句的获取模式.* 必须是 PDO::FETCH_* 常量之一.* @param integer $fetchCursorOrientation [可选] 对于表示的 PDOStatement 对象* 一个可滚动的游标，这个值决定了哪一行将返回给调用者.* @param integer $fetchCursorOffset [可选] 结果中的绝对行数* 设置，或在调用 PDOStatement::fetch() 之前相对于游标位置的行.* @return array 包含结果集中下一行的数组，失败时为 FALSE.* @抛出异常*/公共函数 fetchOne($sql, array $bindings = array(), $fetchMode = PDO::FETCH_ASSOC, $fetchCursorOrientation = PDO::FETCH_ORI_NEXT, $fetchCursorOffset = 0) {$this->prepareStatement($sql)->bindInputParameters($bindings)->executePreparedStatement();尝试 {/** ==========================================================*注意:* ==========================================================* PDOStatement::fetch 不仅在失败时返回 FALSE，* 但也没有找到记录时！这是一个BUG.那是* 为什么我做了 try-catch 块:也许失败会* 抛出异常.** 相反，PDOStatement::fetchAll 在失败时返回 FALSE，* 但如果没有找到记录，则为空数组.这是* 正确的行为.* ==========================================================*/$data = $this->getStatement()->fetch($fetchMode, $fetchCursorOrientation, $fetchCursorOffset);返回 $data;} 捕获(异常 $exception){echo $exception->getMessage();出口();}}/*** 从结果集的下一行返回单列* 或 FALSE 如果没有更多行.** ==================================================================* 笔记:* -----* PDOStatement::fetchColumn() 不应用于检索* 布尔列，因为无法区分值* of FALSE 因为没有更多的行要检索.* 使用 PDOStatement::fetch() 代替.** 警告:* --------* 没有办法从同一行返回另一列，如果你* 使用 PDOStatement::fetchColumn() 来检索数据.* ==================================================================** @param string $sql Sql 语句.* @param array $bindings [可选] 输入参数.* @param integer $columnNumber [可选] 0-indexed number* 您希望从行中检索的列.如果没有提供值，* PDOStatement::fetchColumn() 获取第一列.* @return mixed 结果集下一行的单列* 或 FALSE 如果没有更多行.* @抛出异常*/公共函数 fetchColumn($sql, 数组 $bindings = array(), $columnNumber = 0) {$this->prepareStatement($sql)->bindInputParameters($bindings)->executePreparedStatement();尝试 {返回 $this->getStatement()->fetchColumn($columnNumber);} 捕获(异常 $exception){echo $exception->getMessage();出口();}}/*** 通过执行 INSERT sql 语句来存储数据.** @param string $sql Sql 语句.* @param array $bindings [可选] 输入参数.* @return int 最后插入的 id.*/公共函数插入($sql，数组 $bindings = 数组()){$this->prepareStatement($sql)->bindInputParameters($bindings)->executePreparedStatement();返回 $this->getLastInsertId();}/*** 通过执行 UPDATE sql 语句更新数据.** @param string $sql Sql 语句.* @param array $bindings [可选] 输入参数.* @return int 受影响的行数.*/公共函数更新($sql，数组$bindings = array()){$this->prepareStatement($sql)->bindInputParameters($bindings)->executePreparedStatement();返回 $this->getStatement()->rowCount();}/*** 通过执行 DELETE sql 语句删除数据.** @param string $sql Sql 语句.* @param array $bindings [可选] 输入参数.* @return int 受影响的行数.*/公共函数删除($sql，数组 $bindings = 数组()){$this->prepareStatement($sql)->bindInputParameters($bindings)->executePreparedStatement();返回 $this->getStatement()->rowCount();}/*** 准备和验证一个 sql 语句.** ----------------------------------------------------* 如果数据库服务器无法成功准备* 语句，PDO::prepare() 返回 FALSE 或发出* PDOException(取决于错误处理设置).* ----------------------------------------------------** @param string $sql Sql 语句.* @return $this* @throws PDOException* @throws UnexpectedValueException*/私有函数prepareStatement($sql) {$this->connect();尝试 {$statement = $this->getConnection()->prepare($sql);如果(！$语句){throw new UnexpectedValueException('无法准备sql语句！');}$this->setStatement($statement);} catch (PDOException $pdoException) {echo $pdoException->getMessage();出口();} 捕获(异常 $exception){echo $exception->getMessage();出口();}返回 $this;}/*** 将输入参数绑定到准备好的 PDO 语句.** @param array $bindings 输入参数.* @return $this* @throws UnexpectedValueException*/私有函数 bindInputParameters($bindings) {foreach ($bindings as $key => $value) {尝试 {$bound = $this->getStatement()->bindValue($this->getInputParameterName($key), $值, $this->getInputParameterDataType($value));如果(！$绑定){throw new UnexpectedValueException('一个值不能被绑定！');}} 捕获(异常 $exception){echo $exception->getMessage();出口();}}返回 $this;}/*** 通过绑定数组中的键获取输入参数的名称.** @param int|string $key bindings 数组中输入参数的键.* @return int|string 输入参数的名称.*/私有函数 getInputParameterName($key) {返回 is_int($key) ?($key + 1) : (':' .ltrim($key, ':'));}/*** 通过其值获取 PDO::PARAM_* 常量，例如输入参数的数据类型.** @param混合 $value 输入参数的值.* @return int PDO::PARAM_* 常量.*/私有函数 getInputParameterDataType($value) {$dataType = PDO::PARAM_STR;如果 (is_int($value)) {$dataType = PDO::PARAM_INT;} elseif (is_bool($value)) {$dataType = PDO::PARAM_BOOL;}返回 $dataType;}/*** 执行准备好的 PDO 语句.** @return $this* @throws UnexpectedValueException*/私有函数 executePreparedStatement() {尝试 {如果 (!$this->getStatement()->execute()) {throw new UnexpectedValueException('语句无法执行！');}} 捕获(异常 $exception){echo $exception->getMessage();出口();}返回 $this;}/*** 获取最后插入的行或序列值的ID.** @param string $sequenceObjectName [可选] 序列对象的名称* 应从中返回 ID.* @return string 最后一行的ID，或从指定的检索到的最后一个值* 序列对象，或者错误 IM001 SQLSTATE 如果 PDO 驱动程序不支持这个.* @throws PDOException*/公共函数 getLastInsertId($sequenceObjectName = NULL) {$this->connect();尝试 {返回 $this->getConnection()->lastInsertId($sequenceObjectName);} catch (PDOException $pdoException) {echo $pdoException->getMessage();出口();}}/*** 获取连接配置.** @return 数组*/公共函数 getConnectionConfigs() {返回 $this->connectionConfigs;}/*** 设置连接配置.** @param array $connectionConfigs 连接配置.* @return $this*/公共函数 setConnectionConfigs($connectionConfigs) {$this->connectionConfigs = $connectionConfigs;返回 $this;}/*** 获取数据库连接.** @return PDO 数据库连接.*/公共函数 getConnection() {返回 $this->connection;}/*** 设置数据库连接.** @param PDO $connection 数据库连接.* @return $this*/公共函数 setConnection(PDO $connection) {$this->connection = $connection;返回 $this;}/*** 获取 PDO 语句.** @return PDOStatement*/公共函数 getStatement() {返回 $this-> 语句；}/*** 设置 PDO 语句.** @param PDOStatement $statement PDO 语句.* @return $this*/公共函数 setStatement(PDOStatement $statement) {$this->statement = $statement;返回 $this;}}in my subjects table i have all student's classes by semester and month with each month's points
[
{
"id": "4", - this is the subject id
"userid": "1",
"name": "bio",
"semester": "3", - semester
"month": "5", - the month
"points": "652" - points of this class
"time": "2017-06-18 22:45:04"
},
{
"id": "3", - this is the subject id
"userid": "1",
"name": "math",
"semester": "3", - semester
"month": "4", - the month
"points": "33" - points of this class
"time": "2017-05-15 22:45:04"
},
{
"id": "2", - this is the subject id
"userid": "1",
"name": "chem",
"semester": "1", - semester
"month": "3", - the month
"points": "22" - points of this class
"time": "2017-04-11 22:45:04"
},
{
"id": "1", - this is the subject id
"userid": "1",
"name": "phy",
"semester": "1", - semester
"month": "2", - the month
"points": "10" - points of this class
"time": "2017-02-10 22:45:04"
}
]
this is what i tried
$sql = "SELECT users.id userid,users.name username,subjects.id subjectsid, subjects.name subjectname, subjects.points activepts FROM tbusers AS users INNER JOIN tbsubjects AS subjects ON users.id = subjects.userid WHERE users.id = '$userid' ORDER BY subjects.time DESC";
try {
$db = new db();
$db = $db->connect();
$stmt = $db->prepare($sql);
$stmt->execute();
$user = $stmt->fetchAll(PDO::FETCH_OBJ);
$db = null;
if(empty($user)) {
$response->getBody()->write
('
{
"error":
{
"message":"Invalid"
}
}');
} else {
$response->getBody()->write(json_encode($user));
}
} catch(PDOException $e) {}
the current output i am getting from my query is multiple responses for each because of fetchAll i could just change it to fetch but it won't get the other data
[
{
"userid": "1",
"username": "joe",
"subjectid": "4",
"subjectname": "bio",
"activepts": "652"
},
"userid": "1",
"username": "joe",
"subjectid": "3",
"subjectname": "math",
"activepts": "33"
},
"userid": "1",
"username": "joe",
"subjectid": "2",
"subjectname": "chem",
"activepts": "22"
},
"userid": "1",
"username": "joe",
"subjectid": "1",
"subjectname": "phy",
"activepts": "10"
}
]
my question is how can i merge them into one response and return the below data in the expected output (i have added a little description of each field to explain it)
expected output
[
{
"userid": "1", - from users table
"username": "joe", - from users table
"subjectsid": "1", - first subject id for the student in this case the one for phy
"subjectname": "bio", - current subject name
"activepts": "652", - points of current month
"totalpts": "717", - total points of all subjects for this student
"sem1": "32", - total points of all subjects for this student of semester 1
"sem2": "0", - total points of all subjects for this student of semester 2
"sem3": "685", - total points of all subjects for this student of semester 3
}
]
解决方案
The thing is, you want to fetch the subjects, not the students. So, I inverted the FROM and LEFT JOIN. So, when you want a list of subjects, you are starting with SELECT ... FROM subjects. Then, if you need other details to each subject (like username, etc), you apply LEFT JOIN, which means: JOIN all the needed details (username, etc) to each record of the LEFT table, e.g. of the main table (in you case is table "subjects").
Good luck!
<?php
try {
$dbAdapter = new DbAdapter();
$connection = $dbAdapter->connect();
/*
* I renamed user id variable (from $userId to $userid1) in order to show you that you can
* provide more users if you wish. Then you just have to extend
* the WHERE clause in the sql statement and the bindings array.
*/
$userid1 = 1;
/*
* The sql statement - it will be prepared.
*
* ======================================================
* I'm not sure about the following fields - because you
* didn't provide proper selection criteria for them:
*
* 1) "subjectsid": "1", - first subject id for the student in this case the one for phy
* 2) "subjectname": "bio", - current subject name
* ======================================================
*/
$sql = 'SELECT
subjects.userid,
users.name AS username,
(
SELECT id
FROM tbsubjects
WHERE userid = subjects.userid
ORDER BY id ASC
LIMIT 1
) AS subjectsid,
(
SELECT name
FROM tbsubjects
WHERE
userid = subjects.userid
ORDER BY time DESC
LIMIT 1
) AS subjectname,
(
SELECT IFNULL(SUM(points), 0)
FROM tbsubjects
WHERE
userid = subjects.userid
AND month = DATE_FORMAT(NOW(), "%c")
) AS activepts,
IFNULL(SUM(subjects.points), 0) AS totalpts,
(
SELECT IFNULL(SUM(points), 0)
FROM tbsubjects
WHERE
userid = subjects.userid
AND semester = 1
) AS sem1,
(
SELECT IFNULL(SUM(points), 0)
FROM tbsubjects
WHERE
userid = subjects.userid
AND semester = 2
) AS sem2,
(
SELECT IFNULL(SUM(points), 0)
FROM tbsubjects
WHERE
userid = subjects.userid
AND semester = 3
) AS sem3
FROM
tbsubjects AS subjects
LEFT JOIN tbusers AS users ON users.id = subjects.userid
WHERE subjects.userid = :userid1
GROUP BY subjects.userid
ORDER BY subjects.time DESC';
/*
* The input parameters list for the prepared sql statement.
*/
$bindings = array(
':userid1' => $userid1,
);
/*
* Prepare and validate the sql statement.
*
* --------------------------------------------------------------------------------
* If the database server cannot successfully prepare the statement, PDO::prepare()
* returns FALSE or emits PDOException (depending on error handling settings).
* --------------------------------------------------------------------------------
*/
$statement = $connection->prepare($sql);
if (!$statement) {
throw new UnexpectedValueException('The sql statement could not be prepared!');
}
/*
* Bind the input parameters to the prepared statement.
*
* -----------------------------------------------------------------------------------
* Unlike PDOStatement::bindValue(), when using PDOStatement::bindParam() the variable
* is bound as a reference and will only be evaluated at the time that
* PDOStatement::execute() is called.
* -----------------------------------------------------------------------------------
*/
foreach ($bindings as $key => $value) {
$bound = $statement->bindValue(
getInputParameterName($key)
, $value
, getInputParameterDataType($value)
);
if (!$bound) {
throw new UnexpectedValueException('An input parameter can not be bound!');
}
}
/*
* Execute the prepared statement.
*
* ------------------------------------------------------------------
* PDOStatement::execute returns TRUE on success or FALSE on failure.
* ------------------------------------------------------------------
*/
$executed = $statement->execute();
if (!$executed) {
throw new UnexpectedValueException('The prepared statement could not be executed!');
}
/*
* Fetch users list - array of objects.
*/
$users = $statement->fetchAll(PDO::FETCH_OBJ);
if ($users === FALSE) {
throw new UnexpectedValueException('Fetching users list failed!');
}
/*
* Close connection.
*/
$connection = NULL;
/*
* Handle results.
*/
if (empty($users)) {
$response->getBody()->write(
'{
"error": {
"message":"Invalid"
}
}'
);
} else {
$response->getBody()->write(json_encode($users));
}
} catch (PDOException $exc) {
echo $exc->getMessage();
// $logger->log($exc);
exit();
} catch (Exception $exc) {
echo $exc->getMessage();
// $logger->log($exc);
exit();
}
/**
* Get the name of an input parameter by its key in the bindings array.
*
* @param int|string $key The key of the input parameter in the bindings array.
* @return int|string The name of the input parameter.
*/
function getInputParameterName($key) {
return is_int($key) ? ($key + 1) : (':' . ltrim($key, ':'));
}
/**
* Get the PDO::PARAM_* constant, e.g the data type of an input parameter, by its value.
*
* @param mixed $value Value of the input parameter.
* @return int The PDO::PARAM_* constant.
*/
function getInputParameterDataType($value) {
$dataType = PDO::PARAM_STR;
if (is_int($value)) {
$dataType = PDO::PARAM_INT;
} elseif (is_bool($value)) {
$dataType = PDO::PARAM_BOOL;
}
return $dataType;
}
EDIT:
For my projects I developed a DbAdapter class. The method names are self-explanatory. So, no more spagetti code inside each web page :-) But just:
- the sql statement,
- the bindings array,
- the call to the corresponding method in the db adapter and
- the disconnect-from-db line
The solution to your question would look like this:
<?php
//***********************************************************************************
// Put this in a php file (like db.php) to include whereever you need db data access.
//***********************************************************************************
//
// Db configs.
define('DB_HOST', '...');
define('DB_PORT', 3306);
define('DB_DBNAME', '...');
define('DB_CHARSET', 'utf8');
define('DB_USERNAME', '...');
define('DB_PASSWORD', '...');
define('DB_DRIVER_NAME', 'mysql');
// Create db adapter.
$dbAdapter = new DbAdapter(DB_HOST, DB_DBNAME, DB_USERNAME, DB_PASSWORD, DB_PORT, DB_CHARSET);
//***********************************************************************************
$userid1 = 1;
// Sql statement.
$sql = 'SELECT ... FROM ... WHERE subjects.userid = :userid1 GROUP BY ... ORDER BY ...';
// Input parameters.
$bindings = array(
':userid1' => $userid1,
);
// Fetch users.
$users = $dbAdapter->fetchAll($sql, $bindings);
// Disconnect from db.
$dbAdapter->disconnect();
/*
* Handle results.
*/
if (empty($users)) {
//...
} else {
//...
}
The adapter methods to call are the public ones:
- connect: Connects to the database, e.g. creates a PDO instance, e.g creates a db connection.
- disconnect: Disconnects from the database.
- fetchAll: Fetches more records at once. Returns an array of arrays. So, each element is an array corresponding to a db record.
- fetchOne: Fetches only one record.
- fetchColumn: Fetches a column value.
- update: Performs an UPDATE query. Returns the number of affected rows.
- delete: Performs a DELETE query. Returns the number of affected rows.
- insert: Performs an INSERT query. Returns the last insert id.
- getLastInsertId: Returns the last insert id after an INSERT operation is performed.
That's all :-)
<?php
/*
* Database adapter.
*/
/**
* Database adapter.
*/
class DbAdapter {
/**
* Connection configs.
*
* @var array
*/
private $connectionConfigs;
/**
* Database connection.
*
* @var PDO
*/
private $connection;
/**
* PDO statement.
*
* @var PDOStatement
*/
private $statement;
/**
*
* @param string $host [optional] Host.
* @param string $dbname [optional] Database name.
* @param string $username [optional] User name.
* @param string $password [optional] Password.
* @param string $port [optional] Port.
* @param string $charset [optional] Character set.
* @param string $driverName [optional] Driver name.
* @param array $driverOptions [optional] Driver options.
* @return string DSN string.
*/
public function __construct($host = '', $dbname = ''
, $username = '', $password = '', $port = 3306, $charset = 'utf8', $driverName = 'mysql'
, $driverOptions = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => FALSE,
PDO::ATTR_PERSISTENT => TRUE,
)) {
$this->setConnectionConfigs(array(
'host' => $host,
'dbname' => $dbname,
'username' => $username,
'password' => $password,
'port' => $port,
'charset' => $charset,
'driverName' => $driverName,
'driverOptions' => $driverOptions,
));
}
/**
* Connect to db, e.g. create a PDO instance.
*
* @return $this
* @throws PDOException
*/
public function connect() {
if (!isset($this->connection) || !$this->connection) {
try {
$this->connection = new PDO(
$this->createDsn(
$this->connectionConfigs['host']
, $this->connectionConfigs['dbname']
, $this->connectionConfigs['port']
, $this->connectionConfigs['charset']
, $this->connectionConfigs['driverName']
)
, $this->connectionConfigs['username']
, $this->connectionConfigs['password']
, $this->connectionConfigs['driverOptions']
);
} catch (PDOException $pdoException) {
echo $pdoException->getMessage();
exit();
}
}
return $this;
}
/**
* Disconnect from db.
*
* @return $this
*/
public function disconnect() {
$this->connection = NULL;
return $this;
}
/**
* Create a DSN string.
*
* @param string $host Host.
* @param string $dbname Database name.
* @param string $port Port.
* @param string $charset Character set.
* @param string $driverName Driver name.
* @return string DSN string.
*/
private function createDsn($host, $dbname, $port, $charset, $driverName) {
switch ($driverName) {
default: // mysql
$dsn = sprintf('%s:host=%s;port=%s;dbname=%s;charset=%s'
, $driverName
, $host
, $port
, $dbname
, $charset
);
break;
}
return $dsn;
}
/**
* Fetch data by executing a SELECT sql statement.
*
* @param string $sql Sql statement.
* @param array $bindings [optional] Input parameters.
* @param integer $fetchMode [optional] Fetch mode for a PDO statement.
* Must be one of the PDO::FETCH_* constants.
* @param mixed $fetchArgument [optional] Fetch argument for a PDO statement.
* @param array $fetchConstructorArguments [optional] Constructor arguments for a PDO statement
* when fetch mode is PDO::FETCH_CLASS.
* @return array An array containing the rows in the result set, or FALSE on failure.
* @throws UnexpectedValueException
*/
public function fetchAll($sql, array $bindings = array(), $fetchMode = PDO::FETCH_ASSOC, $fetchArgument = NULL, array $fetchConstructorArguments = array()) {
$this
->prepareStatement($sql)
->bindInputParameters($bindings)
->executePreparedStatement()
;
try {
if (isset($fetchArgument)) {
$data = $this->getStatement()->fetchAll($fetchMode, $fetchArgument, $fetchConstructorArguments);
} else {
$data = $this->getStatement()->fetchAll($fetchMode);
}
if ($data === FALSE) {
throw new UnexpectedValueException('Fetching data failed!');
}
return $data;
} catch (Exception $exception) {
echo $exception->getMessage();
exit();
}
}
/**
* Fetch the next row from the result set by executing a SELECT sql statement.
* The fetch mode property determines how PDO returns the row.
*
* @param string $sql Sql statement.
* @param array $bindings [optional] Input parameters.
* @param integer $fetchMode [optional] Fetch mode for a PDO statement.
* Must be one of the PDO::FETCH_* constants.
* @param integer $fetchCursorOrientation [optional] For a PDOStatement object representing
* a scrollable cursor, this value determines which row will be returned to the caller.
* @param integer $fetchCursorOffset [optional] The absolute number of the row in the result
* set, or the row relative to the cursor position before PDOStatement::fetch() was called.
* @return array An array containing the next row in the result set, or FALSE on failure.
* @throws Exception
*/
public function fetchOne($sql, array $bindings = array(), $fetchMode = PDO::FETCH_ASSOC, $fetchCursorOrientation = PDO::FETCH_ORI_NEXT, $fetchCursorOffset = 0) {
$this
->prepareStatement($sql)
->bindInputParameters($bindings)
->executePreparedStatement()
;
try {
/*
* =========================================================
* NB:
* =========================================================
* PDOStatement::fetch returns FALSE not only on failure,
* but ALSO when no record is found! This is a BUG. That's
* why I made the try-catch block: maybe on failure will
* throw an exception.
*
* Instead, PDOStatement::fetchAll returns FALSE on failure,
* but an empty array if no record is found. This is the
* correct behaviour.
* =========================================================
*/
$data = $this->getStatement()->fetch($fetchMode, $fetchCursorOrientation, $fetchCursorOffset);
return $data;
} catch (Exception $exception) {
echo $exception->getMessage();
exit();
}
}
/**
* Returns a single column from the next row of a result set
* or FALSE if there are no more rows.
*
* =================================================================
* Note:
* -----
* PDOStatement::fetchColumn() should not be used to retrieve
* boolean columns, as it is impossible to distinguish a value
* of FALSE from there being no more rows to retrieve.
* Use PDOStatement::fetch() instead.
*
* Warning:
* --------
* There is no way to return another column from the same row if you
* use PDOStatement::fetchColumn() to retrieve data.
* =================================================================
*
* @param string $sql Sql statement.
* @param array $bindings [optional] Input parameters.
* @param integer $columnNumber [optional] 0-indexed number of the
* column you wish to retrieve from the row. If no value is supplied,
* PDOStatement::fetchColumn() fetches the first column.
* @return mixed A single column from the next row of a result set
* or FALSE if there are no more rows.
* @throws Exception
*/
public function fetchColumn($sql, array $bindings = array(), $columnNumber = 0) {
$this
->prepareStatement($sql)
->bindInputParameters($bindings)
->executePreparedStatement()
;
try {
return $this->getStatement()->fetchColumn($columnNumber);
} catch (Exception $exception) {
echo $exception->getMessage();
exit();
}
}
/**
* Store data by executing an INSERT sql statement.
*
* @param string $sql Sql statement.
* @param array $bindings [optional] Input parameters.
* @return int Last insert id.
*/
public function insert($sql, array $bindings = array()) {
$this
->prepareStatement($sql)
->bindInputParameters($bindings)
->executePreparedStatement()
;
return $this->getLastInsertId();
}
/**
* Update data by executing an UPDATE sql statement.
*
* @param string $sql Sql statement.
* @param array $bindings [optional] Input parameters.
* @return int Number of affected rows.
*/
public function update($sql, array $bindings = array()) {
$this
->prepareStatement($sql)
->bindInputParameters($bindings)
->executePreparedStatement()
;
return $this->getStatement()->rowCount();
}
/**
* Delete data by executing a DELETE sql statement.
*
* @param string $sql Sql statement.
* @param array $bindings [optional] Input parameters.
* @return int Number of affected rows.
*/
public function delete($sql, array $bindings = array()) {
$this
->prepareStatement($sql)
->bindInputParameters($bindings)
->executePreparedStatement()
;
return $this->getStatement()->rowCount();
}
/**
* Prepare and validate an sql statement.
*
* ----------------------------------------------------
* If the database server cannot successfully prepare
* the statement, PDO::prepare() returns FALSE or emits
* PDOException (depending on error handling settings).
* ----------------------------------------------------
*
* @param string $sql Sql statement.
* @return $this
* @throws PDOException
* @throws UnexpectedValueException
*/
private function prepareStatement($sql) {
$this->connect();
try {
$statement = $this->getConnection()->prepare($sql);
if (!$statement) {
throw new UnexpectedValueException('The sql statement can not be prepared!');
}
$this->setStatement($statement);
} catch (PDOException $pdoException) {
echo $pdoException->getMessage();
exit();
} catch (Exception $exception) {
echo $exception->getMessage();
exit();
}
return $this;
}
/**
* Bind the input parameters to a prepared PDO statement.
*
* @param array $bindings Input parameters.
* @return $this
* @throws UnexpectedValueException
*/
private function bindInputParameters($bindings) {
foreach ($bindings as $key => $value) {
try {
$bound = $this->getStatement()->bindValue(
$this->getInputParameterName($key)
, $value
, $this->getInputParameterDataType($value)
);
if (!$bound) {
throw new UnexpectedValueException('A value can not be bound!');
}
} catch (Exception $exception) {
echo $exception->getMessage();
exit();
}
}
return $this;
}
/**
* Get the name of an input parameter by its key in the bindings array.
*
* @param int|string $key The key of the input parameter in the bindings array.
* @return int|string The name of the input parameter.
*/
private function getInputParameterName($key) {
return is_int($key) ? ($key + 1) : (':' . ltrim($key, ':'));
}
/**
* Get the PDO::PARAM_* constant, e.g the data type of an input parameter, by its value.
*
* @param mixed $value Value of the input parameter.
* @return int The PDO::PARAM_* constant.
*/
private function getInputParameterDataType($value) {
$dataType = PDO::PARAM_STR;
if (is_int($value)) {
$dataType = PDO::PARAM_INT;
} elseif (is_bool($value)) {
$dataType = PDO::PARAM_BOOL;
}
return $dataType;
}
/**
* Execute a prepared PDO statement.
*
* @return $this
* @throws UnexpectedValueException
*/
private function executePreparedStatement() {
try {
if (!$this->getStatement()->execute()) {
throw new UnexpectedValueException('The statement can not be executed!');
}
} catch (Exception $exception) {
echo $exception->getMessage();
exit();
}
return $this;
}
/**
* Get the ID of the last inserted row or of the sequence value.
*
* @param string $sequenceObjectName [optional] Name of the sequence object
* from which the ID should be returned.
* @return string The ID of the last row, or the last value retrieved from the specified
* sequence object, or an error IM001 SQLSTATE If the PDO driver does not support this.
* @throws PDOException
*/
public function getLastInsertId($sequenceObjectName = NULL) {
$this->connect();
try {
return $this->getConnection()->lastInsertId($sequenceObjectName);
} catch (PDOException $pdoException) {
echo $pdoException->getMessage();
exit();
}
}
/**
* Get connection configs.
*
* @return array
*/
public function getConnectionConfigs() {
return $this->connectionConfigs;
}
/**
* Set connection configs.
*
* @param array $connectionConfigs Connection configs.
* @return $this
*/
public function setConnectionConfigs($connectionConfigs) {
$this->connectionConfigs = $connectionConfigs;
return $this;
}
/**
* Get database connection.
*
* @return PDO Database connection.
*/
public function getConnection() {
return $this->connection;
}
/**
* Set database connection.
*
* @param PDO $connection Database connection.
* @return $this
*/
public function setConnection(PDO $connection) {
$this->connection = $connection;
return $this;
}
/**
* Get PDO statement.
*
* @return PDOStatement
*/
public function getStatement() {
return $this->statement;
}
/**
* Set PDO statement.
*
* @param PDOStatement $statement PDO statement.
* @return $this
*/
public function setStatement(PDOStatement $statement) {
$this->statement = $statement;
return $this;
}
}
这篇关于在一个响应中返回多个响应数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
