I am trying to build a MySQL query to show all of
the rows in the unit_control table.
Lets say there are 30 rows in the table, I need to display them all to
the user.
In the browser I want to display a table with 2 cols and as many rows
as are in the unit_control table. The left col will be the information
from the unit_control table and in the right col will either be the
session owners score from the student_unit_history table, or some
if_null() text. Either way I want to display all of the rows from my
unit_control table in the browser.
The code below only gets me the rows where the sessions owner matches
his records in the student_unit_history table. So if he has taken 2
units, I get 2 rows displayed in the browser, when I need all 30!
Can I do what I am trying to do?
Don
////////////////////
//My query
$qryUnits = "SELECT unit_control., student_unit_history. ";
$qryUnits .= "FROM unit_control ";
$qryUnits .= "LEFT JOIN student_unit_history ";
$qryUnits .= "ON unit_control.uc_unit_code = student_unit_history.unit_id ";
$qryUnits .= "WHERE student_unit_history.student_id = '$sess_user_name' ";
$qryUnits .= "ORDER BY unit_control.uc_unit_code";
//////////////////
// Rest of the code
////////////////
// First Query
if (!($resultUnits = mysql_query($qryUnits, $dblink1))) echo("$dbFailedConnect <br>" . mysql_error());
////////////////
///////////////
// first query results
while($listScore = mysql_fetch_assoc($resultUnits))
{
//////////////
//Unit control Data
$unit_code = $listScore["uc_unit_code"];
$unit_possible_score = $listScore["uc_possible_score"];
//////////////////////////////
// Student unit history data
$student_unit_id = $listScore["unit_id"];
$student_id = $listScore["student_id"];
$student_grade = $listScore["suh_unit_grade"];
$student_pending = $listScore["suh_pending"];
if (is_null($student_grade))
{
$score_col = "Not yet taken";
}
else
{
$score_col = "$student_grade out of $unit_possible_score";
}
echo("$unit_code, $student_id, $score_col<br>");
}
/////////////////////////////
//The tables in question:
TABLE unit_control (
uc_unit_code varchar(6) NOT NULL ,
uc_unit_name varchar(25) NOT NULL ,
uc_unit_date date NOT NULL DEFAULT '0000-00-00' ,
uc_unit_level char(1) NOT NULL ,
uc_content_area varchar(5) NOT NULL ,
uc_num_of_items int(3) unsigned NOT NULL DEFAULT '1' ,
uc_possible_score int(3) unsigned NOT NULL DEFAULT '0' ,
uc_unit_detail text ,
UNIQUE KEY uc_unit_code (uc_unit_code),
KEY uc_unit_code_2 (uc_unit_code)
) TYPE=MyISAM;
TABLE student_unit_history (
id int(8) NOT NULL auto_increment,
student_id varchar(24) NOT NULL ,
unit_id varchar(7) NOT NULL ,
suh_unit_grade varchar(5) ,
suh_pending int(1) unsigned ,
PRIMARY KEY (id)
) TYPE=MyISAM;
