Select list items from related database table.
Moderator: General Moderators
Select list items from related database table.
I am trying to create a program for a school, which will allow teachers to assign thier students to groups, and then ask a question of that group. To do this, I am using a series of tables in a MySQL database (The database design may not be the best way to do it, but this is how I need to based on some other features of the overall program).
Tables:
Students (student_ID, student_name)
Group (group_id, group_name)
Question (question_id, question_text, question_group)
Group_student_rel (rel_ID, group_id, student_id)
I am now trying to create a page which will allow a teacher to assign students to a group. What I want to do, is create a list item in a form, and populate it with all of the students, and then allow the teacher to select the students from the list to add to the group (by creating an entry in the Group_studend_rel table for each student). I have been able to do this, but my problem is that if the teacher wants to go back and change the students, I need to be able to select the students in the list that have entries in the Group_student_rel table for them in that group.
Example:
If I have Student 1, Student 2, Student 3 in the students table (ID's 1,2,3), and Group 1 added to the Group table (ID 1), and I currently have student 2 added to group 1 (an entry in Group_student_rel of rel_ID=1, group_id=1, student_ID=2), how can I select student 2 in the list in the form based on the presence of the Group_student_rel entry?
I am sorry if I have thouroghly confused anyone- I am still fairly new to this and really don't know exactly how to ask the question- if you need further clarification, please let me know.
Tables:
Students (student_ID, student_name)
Group (group_id, group_name)
Question (question_id, question_text, question_group)
Group_student_rel (rel_ID, group_id, student_id)
I am now trying to create a page which will allow a teacher to assign students to a group. What I want to do, is create a list item in a form, and populate it with all of the students, and then allow the teacher to select the students from the list to add to the group (by creating an entry in the Group_studend_rel table for each student). I have been able to do this, but my problem is that if the teacher wants to go back and change the students, I need to be able to select the students in the list that have entries in the Group_student_rel table for them in that group.
Example:
If I have Student 1, Student 2, Student 3 in the students table (ID's 1,2,3), and Group 1 added to the Group table (ID 1), and I currently have student 2 added to group 1 (an entry in Group_student_rel of rel_ID=1, group_id=1, student_ID=2), how can I select student 2 in the list in the form based on the presence of the Group_student_rel entry?
I am sorry if I have thouroghly confused anyone- I am still fairly new to this and really don't know exactly how to ask the question- if you need further clarification, please let me know.
Re: Select list items from related database table.
OK, so I decided that I should probably post my actual pages:
----group_rel.php----
----group_rel_script.php----
----group_rel.php----
Code: Select all
<?php require_once('/Connections/Rbook.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_Rbook, $Rbook);
$query_students = "SELECT student_id, student_name FROM students ORDER BY student_name ASC";
$students = mysql_query($query_students, $Rbook) or die(mysql_error());
$row_students = mysql_fetch_assoc($students);
$totalRows_students = mysql_num_rows($students);
$colname_rel = "-1";
if (isset($_GET['group_id'])) {
$colname_rel = $_GET['group_id'];
}
mysql_select_db($database_Rbook, $Rbook);
$query_rel = sprintf("SELECT rel_id, student_id, group_id FROM Group_student_rel WHERE group_id = %s", GetSQLValueString($colname_rel, "int"));
$rel = mysql_query($query_rel, $Rbook) or die(mysql_error());
$row_rel = mysql_fetch_assoc($rel);
$totalRows_rel = mysql_num_rows($rel);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<form id="form1" name="form1" method="post" action="group_rel_script.php">
<label>Students in Group:
<select name="students[]" size="10" multiple="multiple" id="students">
<?php
do {
?>
<option value="<?php echo $row_students['student_id']?>"><?php echo $row_students['student_name']?></option>
<?php
} while ($row_students = mysql_fetch_assoc($students));
$rows = mysql_num_rows($students);
if($rows > 0) {
mysql_data_seek($students, 0);
$row_students = mysql_fetch_assoc($students);
}
?>
</select>
</label>
<input name="group_id" type="hidden" id="group_id" value="<?php echo $_GET['group_id'];?>" />
</form>
</body>
</html>
<?php
mysql_free_result($students);
mysql_free_result($rel);
?>
Code: Select all
<?php require_once('/Connections/Rbook.php'); ?>
<?php /* Add students */
/* Delete current Relationships */
$Query = "DELETE FROM Group_student_rel
WHERE group_id = " . $_POST["product_ID"];
mysql_select_db($database_Rbook, $Rbook);
mysql_query($query, $Rbook) or die(mysql_error());
/* INSERT the new ones */
if(!isset($_POST["students"])){
$_POST["students"] = array('1');
}
for($i=0; $i < count($_POST["students"]); $i++) {
$query2 = sprintf("INSERT INTO Group_student_rel
(group_id, student_id)
VALUES (%d, %d)",$_POST["group_id"],$_POST["students"][$i]);
mysql_select_db($database_Rbook, $Rbook);
mysql_query($query2, $Rbook) or die(mysql_error());
}
?>Re: Select list items from related database table.
Use JOINs : http://www.w3schools.com/Sql/sql_join.asp
There are 10 types of people in this world, those who understand binary and those who don't
Re: Select list items from related database table.
Sorry for the delayed response, but it took me a bit to process that and figure out how to implement that (although now that I figured it out I feel a bit dumb that it took so long). Thanks for the input, that was exactly what i was looking for.
Re: Select list items from related database table.
I'm really happy you've figured it out by yourself. Really 
If you need any help I'll be glad to help you again
If you need any help I'll be glad to help you again
There are 10 types of people in this world, those who understand binary and those who don't
Re: Select list items from related database table.
Well... I guess that I do need some more help. My SQL query is going to actually involve three tables (the example that I gave was somewhat simplified from what I actually have). I have a table of recordbooks that is the table that everything is based off of. Because each student may have multiple recordbooks, I have the recordbooks assigned to the students using a student ID, and I want to retrieve the student's first and last names. I am currently using the script below to get all of the records that I want (and how I want) for the base query:
Now, I need to add a LEFT JOIN onto that, to include the table group_pair (with three columns pair_id- a unique id, group_id- the id binding to the selected group, and rbook_id- binding the pair to the recordbook.
Basically, the end result will be a teacher can go in and see a list containing of all of the recordbooks that are owned by students in thier class, with each recordbook that has been assigned to a given group pre-selected. As I understand JOINS, it means that I need a LEFT JOIN with the rbook table as the left table, and the group_pair table as the right. Where I am getting confused, is how to also bind the user_users table to the mix so that I can utilize the chapter (class) and name fields, and also filter the records from group_pair so that only the records with the group_id matching the selected group id (passed from a GET variable).
Code: Select all
SELECT rbook.`year`, user_users.first_name, user_users.last_name, rbook.book_id
FROM rbook, user_users
WHERE rbook.chapter_id = colname AND rbook.user_id = user_users.user_id
ORDER BY rbook.`year` DESC, user_users.last_name, user_users.first_nameBasically, the end result will be a teacher can go in and see a list containing of all of the recordbooks that are owned by students in thier class, with each recordbook that has been assigned to a given group pre-selected. As I understand JOINS, it means that I need a LEFT JOIN with the rbook table as the left table, and the group_pair table as the right. Where I am getting confused, is how to also bind the user_users table to the mix so that I can utilize the chapter (class) and name fields, and also filter the records from group_pair so that only the records with the group_id matching the selected group id (passed from a GET variable).
Re: Select list items from related database table.
Let me start with some notes:
1. I'd advice you : don't use comma operator (it's in fact a CROSS JOIN) for joins - use * JOIN keywords instead. Also, don't use the WHERE clause to "implement" the ON condition.
Having this in mind your query is transformed to:
[sql]SELECT rbook.`year`, user_users.first_name, user_users.last_name, rbook.book_idFROM rbookINNER JOIN user_users ON rbook.user_id = user_users.user_idWHERE rbook.chapter_id = colname ORDER BY rbook.`year` DESC, user_users.last_name, user_users.first_name[/sql]
2.
3.
PS: Please, put SQL code inside sql BB code tags instead of code tags.
1. I'd advice you : don't use comma operator (it's in fact a CROSS JOIN) for joins - use * JOIN keywords instead. Also, don't use the WHERE clause to "implement" the ON condition.
Having this in mind your query is transformed to:
[sql]SELECT rbook.`year`, user_users.first_name, user_users.last_name, rbook.book_idFROM rbookINNER JOIN user_users ON rbook.user_id = user_users.user_idWHERE rbook.chapter_id = colname ORDER BY rbook.`year` DESC, user_users.last_name, user_users.first_name[/sql]
2.
So, in your table group_pair you have a unique field id. I suppose it's also the primary key of this table. Well ... it's considered a bad practice, because your "natural" key is the combination of group_id and rbook_id together. And you have put a "surrogate" key for primary one.table group_pair (with three columns pair_id- a unique id, group_id- the id binding to the selected group, and rbook_id- binding the pair to the recordbook
3.
LEFT JOIN is used when there are records which don't relate to other records, but still they have to be included in the result set. So, I believe you need INNER JOIN (every student must be a member of at least one group).Now, I need to add a LEFT JOIN onto that, to include the table group_pair (with three columns pair_id- a unique id, group_id- the id binding to the selected group, and rbook_id- binding the pair to the recordbook.
Basically, the end result will be a teacher can go in and see a list containing of all of the recordbooks that are owned by students in thier class, with each recordbook that has been assigned to a given group pre-selected.
PS: Please, put SQL code inside sql BB code tags instead of code tags.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Select list items from related database table.
Not quite. while eventually every student would in theory be a part of a group, I am trying to highlight the students which are a part of a given group in a list of all of the students. The student may be a member of one group, five groups, or no groups. I basically want to filter the records from group_pair that are not related to the given group because there could in theory be more than one pair entry for each student, or there could be none.LEFT JOIN is used when there are records which don't relate to other records, but still they have to be included in the result set. So, I believe you need INNER JOIN (every student must be a member of at least one group).
I am trying to use the presence of a pair between the given group and a student to determine whether the entry for that student in the form option list should be selected (i.e. that student has been assigned to that group).
Re: Select list items from related database table.
?to a given group pre-selected.
Then, what about student who don't belong to a group?
There are 10 types of people in this world, those who understand binary and those who don't
Re: Select list items from related database table.
Just try it : join this table by using LEFT JOIN and see whther you like the result or not 
Then come back here and post the issues you have with it.
Then come back here and post the issues you have with it.
There are 10 types of people in this world, those who understand binary and those who don't