Array ? complex

nagasea

Table Employee

ID EMPLOYEE

1 - Mark
2 - John

3 - Andy

Table Assignment

ID ASSIGNMENT

1 - 2,3

2 - 1,2,3

Okay, when i click on ASSIGNMENT 1, I script show All the employee but with "John and Andy" in RED color
can someone help me.

RESULT :
Mark
John X
Andy X

bubblenut

You haven't given us a lot to go on here nagasea. Are the two tables database tables and if so how are the tables structured (table names, column names)? If they're not stored in a database how are they stored and how are you retrieving the data?
Cheers
Bubble

vaaaska

It's possible, but first of all...you would need to get your Employees Assigned ID's into an array...for the paricular project

$theArray = implode(",", $row[EmployeesAssigned);

The do your query to get all your results...

And perhaps, because I'm sure there are other ways to do this, use while looping through the data...

// it's needle then haystack
in_array("$row_people[EmployeeID]", "$theArray");

To determine if they are in that project...so...
if (in_array("$row_people[EmployeeID]", "$theArray")) {
output...make it red
} else {
don't make it red
};

Am I off the mark here? I think this would do the trick, but I bet somebody comes along and blows me away. 😉

nagasea

yes it's 2 separate table
above i put the table structure

Table Employee with 2 COLOUMN (ID and EMPLOYEE)
which is :
1 | Mark
2 | John
3 | Andy

And

Table Assignment with 2 COLOUMN (ID and ASSIGNMENT)
which is
1 | 2,3
2 | 1,2,3

bubblenut

Do you have the power to change your ASSIGNMENTS table because it would be possible to do almost everything in SQL if you can? This would be quicker on large resultsets.

vaaaska

Hey Bubble,

Even if N isn't getting back to you, I would sure like to see how you could get most of this into the query. I wasn't sure if it was possible to get the "Assignment" values used directly in the query (although I suspected it was possible).

Perhaps you might post your solution to this?

Thanks...v

bubblenut

I asked if he had control over the structure of the database because it would require a different (relational :rolleyes: ) structure. There are two basic entities, Employee and Assignment however there is a many to many relationship between them (not allowed in the relational model) because each employee can be on more than one assignment and each assignment can have more than one employee working on it. Therefore we need a link table, I have decided to call it ass_line (he he). The structures going to be something like the following.

[b]employee[/b]
[u]emp_id[/u]
name

[b]assignment[/b]
[u]ass_id[/u]
name

[b]ass_line[/b]
[u]unique_id[/u]
[i]emp_id[/i]
[i]ass_id[/i]

// [b]table_name[/b]
// [u]primary_key[/u]
// [i]foreign_key[/i]

... ....... ........... Oh bllx ...
I was thinking down the lines of then doing a left joining select assignment left joining to employee through ass_line, but you're still not going to be able to pull out the names of those who are not on each assignement, which I'm guessing is pretty key :mad:
I'm sure there must be a clever way of doing this with one query. You could do it pretty quick with two queries and a small check array.
Basically, do a quick query of all emp_ids & names in the employee table and stick them in an array. Then do the left joining query as above with an order by ass_id, then when the ass_id changes do a run through of the employees array for all those which were not present on this assignment. I think it's going to be better with a little code (no error checking)

<?php
$sql1="SELECT emp_id, name FROM employee";
$sql2="SELECT assignment.ass_id as ass_id, assignment.name as ass_name, employee.emp_id as emp_id, employee.name as emp_name FROM assignemt LEFT JOIN ass_line ON (assignment.ass_id=ass_line.ass_id) LEFT JOIN employee ON (employee.emp_id=ass_line.emp_id) ORDER BY assignment.ass_id";

$res1=mysql_query($sql1);
while($row1=mysql_array($res1, MYSQL_ASSOC)) {
  $employees[$row1['emp_id']]=$row1['emp_name'];
}
mysql_free_result($res1);

$res2=mysql_query($sql2);
for($it=0,$last_ass;$row2=mysql_array($res2,MYSQL_ASSOC); ) {
  if($last_ass!=$row2['ass_id']) {
    if($it>0) {
      foreach($employees_tmp as $emp_id => $emp_name) {
        echo($emp_id.":".$emp_name."<br />\n");
      }
    }
    $employees_tmp=$employees;
    echo("Assignment: ".$row2['ass_id'].":".$row2['ass_name']."<br />\n");
    echo("Emp_id : Emp_name<br />\n");
  }
  unset($employees_tmp[$row2['emp_id']]);
   echo($row2['emp_id'].':'.$row2['emp_name']."*<br />\n");
}

Not really any different to what you were talking about really 🙁

vaaaska

Thanks for doing that...gives me some things to think about. I'm not so great with the JOIN so it's nice to see it used in something I can understand.

😉

nagasea

thanks for the solution.. i'll will take a look and learn it.