Mysql simple join question

I have a unrecognizable problem with “join.”

can someone tell me why my query not working? I can’t see anything wrong with it myself.

table [fsattendances]

table [firesafety]


SELECT fsattendances.attendee, firesafety.SFDate FROM firesafety RIGHT JOIN fsattendances ON = fsattendances.fireSafetyId
WHERE fsattendances.attendee=$userid

Assume that $userid in the WHERE clause works

this is the function that calls the query

public function query($sql, $params = array()) {
			$this->_error = false;
			if($this->_query = $this->_pdo->prepare($sql)){
				$x = 1;
				if(count($params)) {
					foreach($params as $param) {
						$this->_query->bindValue($x, $param);
				if($this->_query->execute()) {
					$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);
					$this->_count = $this->_query->rowCount();
				} else {
					$this->_error = true;
			return $this;

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 = 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

The only other thing I can think of is use a LEFT OUTER JOIN instead of a RIGHT JOIN. See if that works?



by “does nothing” is the query not returning any records in the results set or is the query failing with an error?

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 = fsattendances.fireSafetyId LEFT JOIN fstraining ON = fstraining.fireSafetyId LEFT JOIN fscomponents ON fstraining.FSComponentId =', 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

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.