在一个响应中返回多个响应数据
诸葛阳成
问题内容：
问题答案：
在我的科目表中，我按学期和月份列出了所有学生的课程，每个月的分数
[
{
"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"
}
]
这就是我尝试过的
$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) {}
我从查询中获得的当前输出是每个查询的多个响应，因为fetchAll我可以将其更改为，fetch但不会获取其他数据
[
{
"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"
}
]
我的问题是我如何将它们合并为一个响应，并在预期的输出中返回以下数据（我在每个字段中添加了一些说明以对其进行解释）
预期产量
[
{
"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
}
]
问题答案：
问题是，您想获取主题，而不是学生。因此，我反转了FROM和LEFT JOIN。因此，当您想要主题列表时，您将从SELECT …
FROM主题开始。然后，如果您需要每个主题的其他详细信息（例如用户名等），请应用LEFT
JOIN，这意味着：将所有必需的详细信息（用户名等）加入LEFT表的每个记录（例如主表）中您的情况是表格“主题”）。
祝好运！
<?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;
}
编辑：
对于我的项目，我开发了一个DbAdapter类。方法名称是不言自明的。因此，每个网页内没有更多的spagetti代码：-)而是：
- sql语句，
- 绑定数组，
- 对数据库适配器中相应方法的调用，以及
- 从数据库断开连接线
您问题的解决方案如下所示：
<?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 {
//...
}
调用的适配器方法是public：
- connect ：连接到数据库，例如创建PDO实例，例如创建db连接。
- 断开连接 ：与数据库断开连接。
- fetchAll ：一次获取更多记录。返回数组的数组。因此，每个元素都是对应于db记录的数组。
- fetchOne ：仅获取一条记录。
- fetchColumn ：获取列值。
- update ：执行UPDATE查询。返回受影响的行数。
- delete ：执行DELETE查询。返回受影响的行数。
- insert ：执行INSERT查询。返回最后的插入ID。
- getLastInsertId ：返回执行INSERT操作后的最后一个插入ID。
就这样 ：-）
<?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;
}
}
类似资料：
-
我想在spring boot中，当响应对象对于每个响应自动为null时返回404。 我需要建议。 我不想检查控制器中的对象是否为null。
-
本文向大家介绍symfony2 返回404响应，包括了symfony2 返回404响应的使用技巧和注意事项，需要的朋友参考一下 示例 当在服务器上找不到资源时，将返回404响应。在Symfony中，可以通过引发NotFoundHttpException异常来创建此状态。为了避免use在控制器内部添加多余的语句，请使用类createNotFoundException()提供的Controller
-
我想使用GooglePlaceAPI和自动完成视图。我有开发者控制台上的android和浏览器密钥。但当我尝试这些键API时，它返回空响应。 Logcat： D/Volley:[2651]a.a:请求的HTTP响应= 我的代码： 在onpostexecute日志中： 我是新来的google地方api，请帮我找到什么是错误的。
-
有很多使用OkHTTP3发出请求的例子，但是，我仍然无法发出正确的请求。据我所知，我的设置是正确的。 我不断收到如下所示的回复。我不知道他们是什么意思。 {“ContentLength”：238,“ContentTypeString”：“Application/JSON”,“Source”：{“Buffer”：{“Size”：0},“Closed”：False,“Source”：{“BytesRe
-
问题内容： 就像一个人在这里问到但他的解决方案是调用其他函数 …我想知道是否有可能拥有一个不调用a的函数第二个功能基于异步请求的响应，但仅当异步请求响应时。 可能是这样的： 不调用另一个函数，这有可能吗？ 我要实现的目标是拥有一个可以用一些参数调用的函数，该函数将返回异步Web服务（如FB）的响应。 问题答案： 简而言之，没有。您不能让异步函数同步返回有意义的值，因为该值当时不存在（因为它是在后台
-
我们如何将消息（例如“File uploaded Successed”）传递到调用/上一个jsp页面（例如upload.jsp）。jsp页面正在获取会话的属性，并检查用户登录的部门。它以部门名称确认用户。当用户上传文件，servlet在表单操作中被调用时，servlet检查部门名称并在数据库中插入数据，并在同一个upload.jsp页面上向用户显示消息“文件上传成功”。当iam在请求分派器中传递页