jpergega Posted March 22, 2014 Share Posted March 22, 2014 Hi My system has 2 different accounts staff and student. Both users account details(login, names) are stored in the useraccount table. Student has another table called Task. I logged in as a staff and I want to view a student's tasks by clicking on the view link next to their name. Right now if I click on a student's view link it will just bring up everything in the task table, but that is not what I want it should only display whatever is belongs to this student. I am not sure how to do this could you please help? <?php while($row = mysqli_fetch_array($result)){ ?> <tr> <td><?php echo $row['username'] ; ?></td> <td><?php echo $row['firstname'] ; ?></td> <td><?php echo $row['surname'] ; ?></td> <td><a href="viewStudent.php<?php echo '?task_id='.$row['task.account_id']; ?>">View Account</a></td> <?php } } ?> Link to comment Share on other sites More sharing options...
Ingolme Posted March 22, 2014 Share Posted March 22, 2014 You need to make the correct SQL query if you want the correct data. What does your query look like? Link to comment Share on other sites More sharing options...
jpergega Posted March 22, 2014 Author Share Posted March 22, 2014 (edited) You need to make the correct SQL query if you want the correct data. What does your query look like? Thanks My query is this: <?php if(isset($_SESSION['account_id'])) { $account_id = $_SESSION['account_id']; $query = "SELECT staff.staff_id, staff.account_id, task.task_id, task.staff_id, useraccount.username, useraccount.firstname, useraccount.surname, useraccount.account_id FROM useraccount,staff, task WHERE task.account_id=useraccount.account_id AND task.staff_id=staff.staff_id AND staff.account_id=$account_id"; $result= mysqli_query($con,$query); Edited March 22, 2014 by jpergega Link to comment Share on other sites More sharing options...
Ingolme Posted March 22, 2014 Share Posted March 22, 2014 If you want to load the tasks for one particular user you probably have to check that useraccount.account_id has one particular value. I can't be sure, though, since I don't know the structure of your database. Link to comment Share on other sites More sharing options...
jpergega Posted March 23, 2014 Author Share Posted March 23, 2014 If you want to load the tasks for one particular user you probably have to check that useraccount.account_id has one particular value. I can't be sure, though, since I don't know the structure of your database. Hi Here is my tables will you be able to tell me how to display whatever is belongs to this student? Thanks CREATE TABLE userAccount ( account_id SMALLINT NOT NULL AUTO_INCREMENT, username varchar(7) NOT NULL, password varchar(8) NOT NULL, firstname varchar(50) NOT NULL, surname varchar (30) NOT NULL, email varchar(50) NOT NULL, permission ENUM('Student', 'Staff') NOT NULL, PRIMARY KEY (account_id) ) CREATE TABLE staff ( staff_id SMALLINT NOT NULL AUTO_INCREMENT, account_id SMALLINT NOT NULL, PRIMARY KEY (staff_id), FOREIGN KEY (account_id) REFERENCES userAccount(account_id) ) CREATE TABLE IF NOT EXISTS `task` ( `task_id` smallint(6) NOT NULL AUTO_INCREMENT, `taskName` varchar(100) NOT NULL, `description` varchar(300) NOT NULL, `starts` date NOT NULL, `finish` date NOT NULL, `progress` varchar(300) NOT NULL, `status` varchar(10) NOT NULL, `account_id` smallint(6) NOT NULL, `staff_id` varchar(100) DEFAULT NULL, PRIMARY KEY (`task_id`), KEY `account_id` (`account_id`), KEY `staff_id` (`staff_id`) ) Link to comment Share on other sites More sharing options...
Ingolme Posted March 23, 2014 Share Posted March 23, 2014 You don't have any foreign keys to the userAccount table in the task table so I don't know which field is referencing the student. Assuming that account_id references the userAccount table, then select the fields WHERE account_id=[students account ID] Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now