возвращать несколько данных ответа в одном ответе

Дело в том, что вы хотите получить предметы, а не ученики. Итак, я перевернул FROM и LEFT JOIN. Итак, когда вам нужен список тем, вы начинаете с SELECT … FROM subject. Затем, если вам нужны другие данные для каждого объекта (например, имя пользователя и т. Д.), Вы применяете 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, eg 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. Имена методов не требуют пояснений. Итак, на каждой веб-странице больше нет кода спагетти 🙂 Но просто:

• заявление sql,
• массив привязок,
• вызов соответствующего метода в адаптере db и
• линия disconnect-from-db

Решение вашего вопроса будет выглядеть так:

 <?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.
• disconnect : Отключается от базы данных.
• fetchAll : выборки записей за один раз. Возвращает массив массивов. Таким образом, каждый элемент представляет собой массив, соответствующий записи db.
• fetchOne : выборки только одной записи.
• fetchColumn : выбор значения столбца.
• update : Выполняет запрос UPDATE. Возвращает количество затронутых строк.
• delete : Выполняет запрос DELETE. Возвращает количество затронутых строк.
• insert : Выполняет запрос INSERT. Возвращает последний идентификатор вставки.
• getLastInsertId : возвращает последний идентификатор вставки после выполнения операции INSERT.

Это все 🙂

 <?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, eg 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, eg 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; } }