echo data from different table

[jpergega]

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

}

}

?>

[Ingolme]

You need to make the correct SQL query if you want the correct data. What does your query look like?

[jpergega]

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

[Ingolme]

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.

[jpergega]

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`)

)

[Ingolme]

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]