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

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);
						$x++;
					}
				}
				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 firesafety.id = fsattendances.fireSafetyId AND fsattendances.attendee=$userid

Not tested, but should work, assuming that there actually IS related data in both tables.

HTH,

:slight_smile:

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?

V/r,

:slight_smile:

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

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