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;
}
}