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']
