I am having a problem trying to figure things out. What I
want to do is lets say I have a user list database that contains
information about a user. Some of those users are staff, teachers
and students.
I have gotten it up to a point where if a user logs in the
user will be able to make modifications to his account by placing a
link in the main menu.
I used this code…to start a session. It is embedded in
the main.php page.
if (!isset($_SESSION['start']) && session_start()) {
$_SESSION['start'] = time();
}
Once the user clicks on the link and brings the user to a
page where the user can edit their account I have the MySQL grab
the userID. Here is the following MySQL code.
$colname_getUser = "-1";
if (isset($_GET['userID'])) {
$colname_getUser = $_GET['userID'];
}
mysql_select_db($database_conYeshivah, $conYeshivah);
$query_getUser = sprintf("SELECT * FROM `user` WHERE userID =
%s", GetSQLValueString($colname_getUser, "int"));
$getUser = mysql_query($query_getUser, $conYeshivah) or
die(mysql_error());
$row_getUser = mysql_fetch_assoc($getUser);
$totalRows_getUser = mysql_num_rows($getUser);
Everything works up to this point.
The problem lies is let’s say a teacher logs in and
wishes to send off a message to a student. The teacher is able to
see the list of students that he/she has sent. However, the problem
lies that the teacher can see all other teacher’s message
lists. What is the best way of structuring this.
Here is the following MySQL database that I have structured
for teachers sending off to students.
CREATE TABLE IF NOT EXISTS `message` (
`messageID` int(10) unsigned NOT NULL auto_increment,
`userID` int(11) NOT NULL,
`stNum` int(9) NOT NULL COMMENT 'Get from User table',
`date` datetime NOT NULL,
`title` varchar(50) NOT NULL,
`subject` varchar(30) NOT NULL,
`body` longtext NOT NULL,
PRIMARY KEY (`messageID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
This is the database for users.
CREATE TABLE IF NOT EXISTS `user` (
`userID` int(10) unsigned NOT NULL auto_increment,
`dateOfReg` datetime NOT NULL,
`dateOfUpdate` datetime NOT NULL,
`stNum` int(9) default NULL COMMENT 'Student Number of 9 Pin
Code',
`first_name` varchar(20) NOT NULL,
`family_name` varchar(20) NOT NULL,
`username` varchar(20) NOT NULL,
`pwd` varchar(20) NOT NULL,
`email` varchar(50) NOT NULL,
`address` varchar(50) default NULL,
`city` varchar(30) default NULL,
`prov` varchar(20) default NULL,
`postal` varchar(6) default NULL,
`phone` int(10) default NULL,
`cell` int(10) default NULL,
`userTypeID` varchar(34) NOT NULL default '7',
PRIMARY KEY (`userID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
What is the best way of structuring where a teacher who is a
user can access only the students that he/she sent rather than
seeing every message that has been produced.
1
Reply
AdChoices