[RESOLVED] LEFT OUTER JOIN help

[SFDonovan]

Structure dump

tl_students`
`Name_Last` text,
`Name_First` text,
`Email` varchar(30) default NULL,
`LDAP_USER` varchar(255) NOT NULL default '',
`Class_Year` varchar(6) default NULL,
`SOMS_KEY` int(11) default NULL,
`UID` varchar(9) default NULL,

tl_group_students`
`Group_ID` int(11) NOT NULL default '0',
`SOMS_KEY` int(11) NOT NULL default '0',
`LDAP_USER` varchar(255) NOT NULL default ''

tl_M2peereval`
`UID` varchar(9) NOT NULL,
`rater` varchar(10) NOT NULL,
`qa_1_rating` tinyint(1) unsigned NOT NULL,
`qa_2_rating` tinyint(1) unsigned NOT NULL,
`datesubmit` date NOT NULL,
`Academic_Year` varchar(5) NOT NULL,
`term` varchar(10) NOT NULL

It's a peer evaluation and I need to find all students in my group that I "$authuser" have not yet done an evaluation on.

$authuser is my LDAP_USER value from the student table.

I need to see everybody in my team but I am having an error.

I have three teammates in my group. My teammate Mark has already filled out his evaluation on Darren. Now I can no longer see Darren in the list even though I have not yet filled out the eval.

$UserGroup = User group for my team from the tl_group_students table.
rater in the tl_M2peereval is $authuser or who filled out the evaluation.
Same value for LDAP_USER in student table. Like I said I captured the LDAP_USER and assigned it to $authuser during authentication.

My query is close but I'm overlooking something. I only get one of my team mates (Mark) to show up and he is one who has already completed the evaluations on both Darren and myself.

This tells me that Darren's UID exist in the table, but I am not the rater for it... Mark is.

I hope I'm making sense.

Here is my query.

$getgroupmembers = $db->sql_query("SELECT s.* FROM ".$prefix."_tl_students s
LEFT JOIN ".$prefix."_tl_M2peereval pe ON (s.UID = pe.UID)
JOIN ".$prefix."_tl_group_students gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND s.LDAP_USER != '$authuser'
AND pe.UID IS NULL");

[Phill Pafford]

$query = "SELECT s.* FROM ".$prefix."_tl_students AS s /* What data do you need returned? you are returning all of the students fields is this wanted? */
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (s.UID = pe.UID) /* Looks to limit */
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = s.SOMS_KEY) /* Looks to limit to the student groups */
WHERE gs.Group_ID = '$UserGroup' /* limts to one group */
AND s.LDAP_USER != '$authuser' /* Return everything but the authuser? */
AND pe.UID IS NULL"; /* Limits to peers not reviewed */

echo $query . "<br />\n"; //<-- Print query to screen

$getgroupmembers = $db->sql_query($query);



Could you print you query and post it?

I guess I am just thinking out loud above.

[SFDonovan]

Here is the result.

SELECT s.* FROM atlas_tl_students AS s /* What data do you need returned? you are returning all of the students fields is this wanted? */ LEFT JOIN atlas_tl_M2peereval AS pe ON (s.UID = pe.UID) /* Looks to limit */ JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = s.SOMS_KEY) /* Looks to limit to the student groups */ WHERE gs.Group_ID = '19' /* limts to one group */ AND s.LDAP_USER != 'w002sfd' /* Return everything but the authuser? */ AND pe.UID IS NULL

[Phill Pafford]

Ok when a user does the peer review does his pe.UID get entered into the atlas_tl_M2peereval table?

I think the LEFT JOIN is not needed


SELECT s.*
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '19' /* This is limiting to the group */
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL /* This condition is limiting the UID's that have no value */


Try it and let me know

[SFDonovan]

Ok when a user does the peer review does his pe.UID get entered into the atlas_tl_M2peereval table?

I think the LEFT JOIN is not needed


SELECT s.*
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '19' /* This is limiting to the group */
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL /* This condition is limiting the UID's that have no value */


Try it and let me know

Yes the UID is entered. It should end up as the rater does his eval's on all his team members. Each having their own UID and his rater written to the record. I want to find all team members who I have not yet done an evaluation for.

You want me to just run this in phpmyadmin?

[Phill Pafford]

sure what ever is easiest for you

[SFDonovan]

It didn't like this:

#1054 - Unknown column 's.SOMS_KEY' in 'on clause'

[Phill Pafford]

Is the atlas_tl_students table the same structure are this table tl_students



tl_students`
`Name_Last` text,
`Name_First` text,
`Email` varchar(30) default NULL,
`LDAP_USER` varchar(255) NOT NULL default '',
`Class_Year` varchar(6) default NULL,
`SOMS_KEY` int(11) default NULL, /* <-- Is there */
`UID` varchar(9) default NULL,

tl_group_students`
`Group_ID` int(11) NOT NULL default '0',
`SOMS_KEY` int(11) NOT NULL default '0',
`LDAP_USER` varchar(255) NOT NULL default ''

tl_M2peereval`
`UID` varchar(9) NOT NULL,
`rater` varchar(10) NOT NULL,
`qa_1_rating` tinyint(1) unsigned NOT NULL,
`qa_2_rating` tinyint(1) unsigned NOT NULL,
`datesubmit` date NOT NULL,
`Academic_Year` varchar(5) NOT NULL,
`term` varchar(10) NOT NULL



I just removed the LEFT JOIN from your query and added the table in the FROM clause

[SFDonovan]

One thing I was having trouble with was my other team members would submit their evals on the 4 or 5 in our team and the UID would then exist in the table. I pulled up the page and had missing team members even though I had never submitted and eval and my rater was written to any records.

Example:

Steve logs in and submits evaluations on Mark and Darren, but not Judy and Bob.

Bob logs in and cannot see Mark or Darren but does see Judy's name to select and fill out an evaluation.

I've been hammering on this for two days.

[SFDonovan]

Is the atlas_tl_students table the same structure are this table tl_students


They are the same table.

It doesn't like it in phpmyadmin. I get no error in the php script. I still get no team members displayed.

$groupmember_total = $db->sql_numrows($getgroupmembers);
if($groupmember_total = 0){
All evaluation are complete.
}

[Phill Pafford]

Try this


SELECT s.*
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (s.SOMS_KEY = gs.SOMS_KEY)
WHERE gs.Group_ID = '19'
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL


or


SELECT
s.Name_Last,
s.Name_First,
s.Email,
s.LDAP_USER,
s.Class_Year,
s.SOMS_KEY,
s.UID
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (s.SOMS_KEY = gs.SOMS_KEY)
WHERE gs.Group_ID = '19'
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL


Try in PHPMyAdmin

Also you might want to use the EXPLAIN command like this to see whats throwing the error in MySQL.


EXPLAIN SELECT s.*
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe
JOIN atlas_tl_group_students AS gs ON (s.SOMS_KEY = gs.SOMS_KEY)
WHERE gs.Group_ID = '19'
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL

[SFDonovan]

Nothing.

I'm pretty stumped.

Here is a dump of my students table.

atlas_tl_students` (`Name_Last`, `Name_First`, `Email`, `LDAP_USER`, `Class_Year`, `SOMS_KEY`, `UID`) VALUES
('Donovan', 'Steven', 'steven.donovan@xxx.edu', 'w002sfd', '2', 999, 'U00559693'),
('Anderson', 'Mark', 'mark.anderson@xxx.edu', 'w003mda', '2', 548, 'U00583949'),
('Needles', 'John', 'john.needles@xxx.edu', 'w001jan', '2', 537, 'U00580537'),
('Harbert', 'Darren', 'darren.harbert@xxx.edu', 'w001dxh', '2', 496, 'U00583382'),
('Trimboli', 'Sandy', 'sandra.trimboli@xxx.edu', 'w003skt', '2', 539, 'U00583903'),
('Merrill', 'Valencia', 'valencia.merrill@xxx.edu', 'w103vlm', '2', 562, 'U00583822'),
('Shafeek', 'Ife', 'ife.shafeek@xxx.edu', 'w001ifs', '2', 448, 'U00399476');

my group students table:

atlas_tl_group_students` (`Group_ID`, `SOMS_KEY`, `LDAP_USER`) VALUES
(20, 539, 'w003skt'),
(20, 448, 'w001ifs'),
(19, 496, 'w001dxh'),
(19, 999, 'w002sfd'),
(19, 548, 'w003mda'),
(20, 537, 'w001jan'),
(20, 562, 'w103vlm');


I'm in group 19 with Darren and Mark yet I don't see them. All I get is a zero result when I do sql_numrows

The EXPLAIN only showed the same error as before.

[Phill Pafford]

Hmm, Try this in the phpMyAdmin


SELECT
s.Name_Last,
s.Name_First,
s.Email,
s.LDAP_USER,
s.Class_Year,
s.SOMS_KEY,
s.UID
FROM atlas_tl_students AS s, atlas_tl_M2peereval AS pe, atlas_tl_group_students AS gs
WHERE gs.Group_ID = '19'
AND s.SOMS_KEY = gs.SOMS_KEY
AND s.LDAP_USER != 'w002sfd'
AND pe.UID IS NULL

[SFDonovan]

MySQL returned an empty result set (i.e. zero rows)

[Phill Pafford]

Well looks like the query you started off with is throwing an error and you might not be catching it. I would break down the query into steps like. First I need to find this in table A, then I need to find this from table B and so on.

Maybe add this or die(mysql_error());


$getgroupmembers = $db->sql_query("SELECT s.* FROM ".$prefix."_tl_students s
LEFT JOIN ".$prefix."_tl_M2peereval pe ON (s.UID = pe.UID)
JOIN ".$prefix."_tl_group_students gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND s.LDAP_USER != '$authuser'
AND pe.UID IS NULL") or die(mysql_error());

echo $getgroupmembers;

[SFDonovan]

Unknown column 's.SOMS_KEY' in 'on clause' which is incorrect. I have a SOMS_KEY in the student table. Is it not aliased right?

If I do this

FROM atlas_tl_students s, atlas_tl_M2peereval pe

I get the error.

If I do this I do not

FROM atlas_tl_students s JOIN atlas_tl_M2peereval pe but I still get no results.

[Phill Pafford]

Hmm try this, maybe the s is to short of an alias?


$getgroupmembers = $db->sql_query("SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND st.LDAP_USER != '$authuser'
AND pe.UID IS NULL") or die(mysql_error());

echo $getgroupmembers;

[SFDonovan]

Resource id #45

so I did this:

$getgroupmembers = "SELECT st.* FROM ".$prefix."_tl_students AS st
JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND st.LDAP_USER != '$authuser'
AND pe.UID IS NULL" or die(mysql_error());

echo $getgroupmembers;

and got

SELECT st.* FROM atlas_tl_students AS st JOIN atlas_tl_M2peereval AS pe ON (st.UID = pe.UID) JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY) WHERE gs.Group_ID = '19' AND st.LDAP_USER != 'w002sfd' AND pe.UID IS NULL


Which looks correct.

Odd behavior.

[Phill Pafford]

Resource id #45 is an object / array return, probably since your not calling the query function it's returning the mysql error function.



// NO MYSQL QUERY CALL BEFORE THE SQL SELECT STATEMENT
$getgroupmembers = "SELECT st.* FROM ".$prefix."_tl_students AS st
JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND st.LDAP_USER != '$authuser'
AND pe.UID IS NULL" or die(mysql_error());

echo $getgroupmembers;



ok try you php code this way

// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND st.LDAP_USER != '$authuser'
AND pe.UID IS NULL";

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}


This should at least tell you if your query is returning any records or errors.

[SFDonovan]

Ah... I have a result now. I didn't have the LEFT JOIN.

$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND st.LDAP_USER != '$authuser'
AND pe.UID IS NULL" or die(mysql_error());

But here is the problem that I originally had. (I'm back at square one). :(

There are three guys in my group 19.

Darren, Mark, and myslef.

I logged in an did an eval on Darren and Mark.

Mark logs in to do evaluations and can only see me...he cannot see Darren.

So the two records I inserted have one UID that is Mark's, the other UID is Darren's, both have my rater value.

Mark logs in and cannot see Darren because Darren's UID exist in the table however it is under my rater value.

[Phill Pafford]

ok let's start off by breaking up your query and seeing what we need to do.

First let's just run the LEFT join and look at the results


// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)";

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}


Does this give you the correct return? if not let's correct it.

Going further in the query

// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)";

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}


what about this?


// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'";

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}



now this


// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND st.LDAP_USER != '$authuser'";

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}


well we know the last one doesn't give the correct results.

Hope this helps some in debugging your SQL query.

[SFDonovan]

ok let's start off by breaking up your query and seeing what we need to do.

First let's just run the LEFT join and look at the results


// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)";

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}


Does this give you the correct return? if not let's correct it.

This gives me

Steven Donovan
Mark Anderson
Mark Anderson
Mark Anderson
John Needles
Darren Harbert
Sandy Trimboli
Valencia Merrill
Ife Shafeek

Mark has three evals written in the pe table and Darrren has one.

Also giving me Warning: Invalid argument supplied for foreach()

[SFDonovan]

This one

// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'";

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}


gives me


Darren Harbert
Steven Donovan
Mark Anderson
Mark Anderson
Mark Anderson

[Phill Pafford]

Odd, it doesn't give you all the Student info? (and don't worry about the foreach error, it's just a method to display data. nothing to do with your SQL query).

Like:

Name_Last
Name_First
Email
LDAP_USER
Class_Year
SOMS_KEY
UID

try this

// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND pe.UID IS NULL";

echo $sql . "<br />\n"; // Print the query

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}


what does this do?


AND st.LDAP_USER != '$authuser'

[Phill Pafford]

This one

// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'";

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}


gives me

Is this correct?

[SFDonovan]

Is this correct?

Yes it only gives me my group members.

Darren Harbert
Steven Donovan
Mark Anderson
Mark Anderson
Mark Anderson

[SFDonovan]

Odd, it doesn't give you all the Student info? (and don't worry about the foreach error, it's just a method to display data. nothing to do with your SQL query).

Like:

Name_Last
Name_First
Email
LDAP_USER
Class_Year
SOMS_KEY
UID

try this

// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND pe.UID IS NULL";

echo $sql . "<br />\n"; // Print the query

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}


what does this do?



SELECT st.* FROM atlas_tl_students AS st LEFT JOIN atlas_tl_M2peereval AS pe ON (st.UID = pe.UID) JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY) WHERE gs.Group_ID = '19' AND pe.UID IS NULL

Warning: Invalid argument supplied for foreach() in /Library/WebServer/Documents/atlas/modules/Your_Account/index.php on line 1140
Team Member
Steven Donovan

[Phill Pafford]

// Build the SQL
$sql = "SELECT st.* FROM ".$prefix."_tl_students AS st
LEFT JOIN ".$prefix."_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN ".$prefix."_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup'
AND pe.UID IS NULL";

echo $sql . "<br />\n"; // Print the query

// Execute the SQL or DIE and give MySQL error(s)
$getgroupmembers = $db->sql_query($sql) or die(mysql_error());

if(is_array($getgroupmembers))
{
//Loop through the return
foreach($getgroupmembers as $key => $val)
{
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}
} else {
echo $getgroupmembers . "<br />\n";
}

[SFDonovan]

Wouldn't this echo $getgroupmembers give me another Resource id error?

[SFDonovan]

SELECT st.* FROM atlas_tl_students AS st LEFT JOIN atlas_tl_M2peereval AS pe ON (st.UID = pe.UID) JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY) WHERE gs.Group_ID = '19' AND pe.UID IS NULL
Resource id #45
Team Member
Steven Donovan

[Phill Pafford]

Resource id #45 is not an error, it's a object/array returned.

Could I see the code for this function sql_query()

If you run this query in phpMyAdmin what do you get?


SELECT st.*
FROM atlas_tl_students AS st
LEFT JOIN atlas_tl_M2peereval AS pe
ON (st.UID = pe.UID)
JOIN atlas_tl_group_students AS gs
ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '19'
AND pe.UID IS NULL

[SFDonovan]

Resource id #45 is not an error, it's a object/array returned.

Could I see the code for this function sql_query()


function sql_query($query = "", $transaction = FALSE)
{
// Remove any pre-existing queries
unset($this->query_result);
if($query != "")
{

$this->query_result = @mysql_query($query, $this->db_connect_id);

}
if($this->query_result)
{
unset($this->row[$this->query_result]);
unset($this->rowset[$this->query_result]);
return $this->query_result;
}
else
{
return ( $transaction == END_TRANSACTION ) ? true : false;
}
}



If you run this query in phpMyAdmin what do you get?


SELECT st.*
FROM atlas_tl_students AS st
LEFT JOIN atlas_tl_M2peereval AS pe
ON (st.UID = pe.UID)
JOIN atlas_tl_group_students AS gs
ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '19'
AND pe.UID IS NULL


I get one result. The record shows me. I have no evals (UID) written to the pe table.

[Phill Pafford]

Did you write this function?

try this for running your queires


/************************************************
* Function Name: mysqlQuery
* parameters: $query
* returns: $rs
* **********************************************
* Functionality: To query a MySQL database and
* return the results in array format
* **********************************************
* Updates
* **********************************************
* Updated By:
* Date:
* Changes:
* **********************************************
* Notes
************************************************/

function mysqlQuery($query)
{
// Gets the results from the query
$results = mysql_query($query, $this->connection);

// Loops through the queried results as an multi-dimensional array
while($rows = mysql_fetch_array($results, MYSQL_ASSOC))
{
// Push results to single array
$rs[] = $rows;
}

// Return results as array
return $rs;
}


After this your should see the results in the foreach loop

Ok another question I have is in the atlas_tl_M2peereval the UID's are NULL untill they get a review from you? is there more then one record for them in this table?

[SFDonovan]

I added the new mysqlQuery code to my mysql.php and changed the query and this is what it returned.

SELECT st.* FROM atlas_tl_students AS st LEFT JOIN atlas_tl_M2peereval AS pe ON (st.UID = pe.UID) JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY) WHERE gs.Group_ID = '19' AND pe.UID IS NULL
Key: 0 Val: Array
Team Member
Steven Donovan



Ok another question I have is in the atlas_tl_M2peereval the UID's are NULL until they get a review from you? is there more then one record for them in this table?

The records do not exist in the atlas_tl_M2peereval until I submit the evaluation.

You will have several records with the same UID but all from different raters. All should have the same academic year and all belonging to the same term.

[Phill Pafford]

ok so I would remove this from the query "AND pe.UID IS NULL"

Run this in phpMyAdmin
SELECT st.*
FROM atlas_tl_students AS st
LEFT JOIN atlas_tl_M2peereval AS pe ON (st.UID = pe.UID)
JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY)
WHERE gs.Group_ID = '19'


Also modify the foreach loop

if(is_array($getgroupmembers))
{
//Loop through the return
foreach($getgroupmembers as $key => $val)
{
if(is_array($val))
{
foreach($val as $k => $v)
{
// Print out the return
echo "SubKey: " . $k . " SubVal: " . $v . "<br />\n";
}
} else {
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}
}
} else {
echo $getgroupmembers . "<br />\n";
}

[SFDonovan]

Also modify the foreach loop

if(is_array($getgroupmembers))
{
//Loop through the return
foreach($getgroupmembers as $key => $val)
{
if(is_array($val))
{
foreach($val as $k => $v)
{
// Print out the return
echo "SubKey: " . $k . " SubVal: " . $v . "<br />\n";
}
} else {
// Print out the return
echo "Key: " . $key . " Val: " . $val . "<br />\n";
}
}
} else {
echo $getgroupmembers . "<br />\n";
}


Now I get

SELECT st.* FROM atlas_tl_students AS st LEFT JOIN atlas_tl_M2peereval AS pe ON (st.UID = pe.UID) JOIN atlas_tl_group_students AS gs ON (gs.SOMS_KEY = st.SOMS_KEY) WHERE gs.Group_ID = '19'
SubKey: Name_Last SubVal: Harbert
SubKey: Name_First SubVal: Darren
SubKey: Email SubVal: darren.harbert@xxxx.edu
SubKey: LDAP_USER SubVal: w001dxh
SubKey: Class_Year SubVal: 2
SubKey: SOMS_KEY SubVal: 496
SubKey: UID SubVal: U00583382
SubKey: Name_Last SubVal: Donovan
SubKey: Name_First SubVal: Steven
SubKey: Email SubVal: steven.donovan@xxxx.edu
SubKey: LDAP_USER SubVal: w002sfd
SubKey: Class_Year SubVal: 2
SubKey: SOMS_KEY SubVal: 999
SubKey: UID SubVal: U00559693
SubKey: Name_Last SubVal: Anderson
SubKey: Name_First SubVal: Mark
SubKey: Email SubVal: mark.anderson@xxxx.edu
SubKey: LDAP_USER SubVal: w003mda
SubKey: Class_Year SubVal: 2
SubKey: SOMS_KEY SubVal: 548
SubKey: UID SubVal: U00583949
SubKey: Name_Last SubVal: Anderson
SubKey: Name_First SubVal: Mark
SubKey: Email SubVal: mark.anderson@xxxx.edu
SubKey: LDAP_USER SubVal: w003mda
SubKey: Class_Year SubVal: 2
SubKey: SOMS_KEY SubVal: 548
SubKey: UID SubVal: U00583949
SubKey: Name_Last SubVal: Anderson
SubKey: Name_First SubVal: Mark
SubKey: Email SubVal: mark.anderson@xxxx.edu
SubKey: LDAP_USER SubVal: w003mda
SubKey: Class_Year SubVal: 2
SubKey: SOMS_KEY SubVal: 548
SubKey: UID SubVal: U00583949
Team Member
Darren Harbert
Steven Donovan
Mark Anderson
Mark Anderson
Mark Anderson

[Nedals]

It looks like you are still having touble with this.
I think I understand the problem and you MAY want to approach this a completely different, and maybe simpler, way
This assumes you have control over the database structure.

tl_students:
+-----+-----------+------------+----------+
| UID | Name_Last | Name_First | Group_id | etc.
+-----+-----------+------------+----------+
| 999 | Donovan | Steven | 20 |
| 548 | Anderson | Mark | 20 |
| 537 | Needles | John | 19 |
| 496 | Harbert | Darren | 19 |
| 539 | Trimboli | Sandy | 19 |
| 562 | Merrill | Valencia | 20 |
| 448 | Shafeek | Ife | 20 |

Moved the Group_id into the t1_students table (saves a table)
If you need data about the group, you could use a LEFT JOIN to get that data

t1_review:
+-----+----------+
| uid | reviewby | etc
+-----+----------+-
| 999 | 562 |
| 999 | 548 |
| 448 | 562 |
|and so on

562 reviewed 999, 448 and will see 548
548 reviewed 999 and will see 562, 448

SELECT s.Name_Last, ... FROM t1_students s
WHERE s.group=20 AND s.UID<>562 AND s.UID NOT IN (SELECT r.uid FROM t1_review r WHERE r.uid=562)


Just a thought. :)

[SFDonovan]

In my reviewed by table I use a different value for the "reviewby". It looks like you are using UID for rater as well as UID for ratee. Instead of UID I use their LDAP_USER which I store in $authuser _SESSION.

This designates who is logged in and viewing the page.

So I tried to match on rater != $authuser to find those who I had not yet evaluated. I think it is the same as you are doing here:

AND s.UID<>562 with 562 being your own UID.

Your solution does have possibilities. I think moving Group_ID to the students table is redundant but that is just me.

This would actually be:

tl_M2peereval:
+-----+----------+
| uid | rater | etc
+-----+----------+-
| 999 | w103vlm |
| 999 | w003mda |
| 448 | w103vlm |

I have been messing with this more this morning:

SELECT s.Name_Last, s.Name_First, s.UID, s.SOMS_KEY, s.LDAP_USER, g.Group_ID FROM ".$prefix."_tl_students s
JOIN ".$prefix."_tl_group_students gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup' AND s.LDAP_USER <> '$authuser' AND s.LDAP_USER NOT IN (SELECT s.LDAP_USER FROM ".$prefix."_tl_M2peereval pe WHERE pe.rater = '$authuser')

Did I just make it way too complicated again?

[SFDonovan]

I think I have it fixed with this.

$getgroupmembers = $db->sql_query("SELECT s.Name_Last, s.Name_First, s.UID, s.SOMS_KEY, s.LDAP_USER, gs.Group_ID FROM ".$prefix."_tl_students s
JOIN ".$prefix."_tl_group_students gs ON (gs.SOMS_KEY = s.SOMS_KEY)
WHERE gs.Group_ID = '$UserGroup' AND s.LDAP_USER <> '$authuser' AND s.LDAP_USER NOT IN (SELECT s.LDAP_USER FROM ".$prefix."_tl_M2peereval pe WHERE pe.rater = '$authuser' AND s.UID = pe.UID)") or die(mysql_error());