can someone tell me why my query not working? I can’t see anything wrong with it myself.
table [fsattendances]
id
fireSafetyId
attendee
table [firesafety]
id
SFDate
query:
SELECT fsattendances.attendee, firesafety.SFDate FROM firesafety RIGHT JOIN fsattendances ON firesafety.id = fsattendances.fireSafetyId
WHERE fsattendances.attendee=$userid
Try moving the text from the WHERE clause to your ON of the join.
SELECT fsattendances.attendee, firesafety.SFDate FROM firesafety RIGHT JOIN fsattendances ON firesafety.id = fsattendances.fireSafetyId AND fsattendances.attendee=$userid
Not tested, but should work, assuming that there actually IS related data in both tables.
Yes I am working with data that is present and related.
I tried your script and unfortunately it does nothing.
if I remove the firesafety table and only pull out “attendee” I get returned all the record for this $userid
I think the problen reside in the pdo function that I have. it seems to work fine with one table and filtering criteria but as soon as I introdce another table in the query I get nothing. I can’t take any credit for the query function since I picked it up part of a tutorial
no, it did not make any differences. I ended up finding the problem being the query method itself. so instead of using the query method I used another method and got it to work. here is the method I was able to use:
query script:
$userid = $user->data()->id;
$rs = DB::getInstance()->get('firesafety RIGHT JOIN fsattendances ON firesafety.id = fsattendances.fireSafetyId LEFT JOIN fstraining ON firesafety.id = fstraining.fireSafetyId LEFT JOIN fscomponents ON fstraining.FSComponentId = fscomponents.id', array( 'fsattendances.attendee', '=', $userid));
and the methods:
private function action($action, $table, $where = array()) {
if(count($where) === 3) {
$operators = array('=', '>', '<', '>=', '<=', '!=');
$field = $where[0];
$operator = $where[1];
$value = $where[2];
if(in_array($operator, $operators)) {
$sql = "{$action} FROM {$table} WHERE {$field} {$operator} ?";
if(!$this->query($sql, array($value))->error()) {
return $this;
}
}
}
return false;
}
//// this function is a shorth cut of the action function.
public function get($table=array(), $where) {
return $this->action('SELECT *', $table, $where);
}
errors on the ouput yes but the query itself did not returned any error, it was simply an invalid query as far as I was able to deduce. I have fixed the problem though. see my response with changing the method I was using