问题是，您想获取主题，而不是学生。因此，我反转了FROM和LEFT JOIN。因此，当您想要主题列表时，您将从SELECt …
FROM主题开始。然后，如果您需要每个主题的其他详细信息（例如用户名等），请应用LEFT
JOIN，这意味着：将所有必需的详细信息（用户名等）加入LEFT表的每个记录（例如主表）中您的情况是表格“主题”）。
祝好运！
<?phptry { $dbAdapter = new DbAdapter(); $connection = $dbAdapter->connect(); $userid1 = 1; $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'; $bindings = array( ':userid1' => $userid1, ); $statement = $connection->prepare($sql); if (!$statement) { throw new UnexpectedValueException('The sql statement could not be prepared!'); } 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!'); } } $executed = $statement->execute(); if (!$executed) { throw new UnexpectedValueException('The prepared statement could not be executed!'); } $users = $statement->fetchAll(PDO::FETCH_OBJ); if ($users === FALSE) { throw new UnexpectedValueException('Fetching users list failed!'); } $connection = NULL; if (empty($users)) { $response->getBody()->write( '{ "error": { "message":"Invalid" } }' ); } else { $response->getBody()->write(json_enpre($users)); }} catch (PDOException $exc) { echo $exc->getMessage(); // $logger->log($exc); exit();} catch (Exception $exc) { echo $exc->getMessage(); // $logger->log($exc); exit();}function getInputParameterName($key) { return is_int($key) ? ($key + 1) : (':' . ltrim($key, ':'));}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);/if (empty($users)) { //...} else { //...}调用的适配器方法是
public：
- connect ：连接到数据库，例如创建PDO实例，例如创建db连接。
- 断开连接 ：与数据库断开连接。
- fetchAll ：一次获取更多记录。返回数组的数组。因此，每个元素都是对应于db记录的数组。
- fetchOne ：仅获取一条记录。
- fetchColumn ：获取列值。
- update ：执行UPDATE查询。返回受影响的行数。
- delete ：执行DELETE查询。返回受影响的行数。
- insert ：执行INSERT查询。返回最后的插入ID。
- getLastInsertId ：返回执行INSERT操作后的最后一个插入ID。
就这样 ：-）
<?phpclass DbAdapter { private $connectionConfigs; private $connection; private $statement; 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, )); } 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; } public function disconnect() { $this->connection = NULL; return $this; } 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; } 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(); } } 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 { $data = $this->getStatement()->fetch($fetchMode, $fetchCursorOrientation, $fetchCursorOffset); return $data; } catch (Exception $exception) { echo $exception->getMessage(); exit(); } } 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(); } } public function insert($sql, array $bindings = array()) { $this ->prepareStatement($sql) ->bindInputParameters($bindings) ->executePreparedStatement() ; return $this->getLastInsertId(); } public function update($sql, array $bindings = array()) { $this ->prepareStatement($sql) ->bindInputParameters($bindings) ->executePreparedStatement() ; return $this->getStatement()->rowCount(); } public function delete($sql, array $bindings = array()) { $this ->prepareStatement($sql) ->bindInputParameters($bindings) ->executePreparedStatement() ; return $this->getStatement()->rowCount(); } 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; } 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; } private function getInputParameterName($key) { return is_int($key) ? ($key + 1) : (':' . ltrim($key, ':')); } 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; } 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; } public function getLastInsertId($sequenceObjectName = NULL) { $this->connect(); try { return $this->getConnection()->lastInsertId($sequenceObjectName); } catch (PDOException $pdoException) { echo $pdoException->getMessage(); exit(); } } public function getConnectionConfigs() { return $this->connectionConfigs; } public function setConnectionConfigs($connectionConfigs) { $this->connectionConfigs = $connectionConfigs; return $this; } public function getConnection() { return $this->connection; } public function setConnection(PDO $connection) { $this->connection = $connection; return $this; } public function getStatement() { return $this->statement; } public function setStatement(PDOStatement $statement) { $this->statement = $statement; return $this; }}
