Storing personal prefrences

Anon

I would like to make it possible for some to store which jobs they are working one.

For example say we have random job numbers from 500 to 1000. One individual may have 15 jobs assigned to himself.

So far I have made a table in Mysql that has the fields employee_id and job_number. Another way I was thinking was to have employee_id and job_numbers (Notice the S on job_numbers). So instead of have multiple entries for each job number have one entry for each employee and in the job _numbers field list all the assigned job numbers via an array?

I also have a PHP file that queries the data base for all possible job numbers and lists them with check boxes.

I can figure out how to make it possible for one to go in and check of all his programs and add then into the DB. Where I have difficulties seeing is once he has assigned his programs I would like to make it possible to re-list all the jobs and have all his jobs already checked and then make it so he can uncheck or check new and old programs. Is it a good idea when they go in and re-edit there programs to delete all of his/her entries and re-insert the new? or is there a way to program in PHP to be smart enough to know if one got unchecked?

So basically here are the questions I have:

What is the best fields for the table in the DB?

How do I list all the job numbers with the a check to all assigned number?

How do I re-modify the DB when one goes back and checks and checks job numbers?

Thanks

djg_uk

Hi Bill,

I think the best approach to this is better database design. Try not to limit yourself to just using one table in the database. For example, why not have three tables - one for employee details, one for job details, and one to link the two.

The third table could have the following format:

job_id | employee_id

It is pretty simple to list all the JOB's associated with an EMPLOYEE by using the following query:

SELECT job_id FROM job_link WHERE employee_id = $employee_id

You could easily pull in details from the other two tables, including the state of the jobs from the second table...

Cheers,

David Grant

Anon

Thanks David,

I do actually have multible tables in my DB. I just mentioned the one since I did not feel I could explain the whole DB with out boring most and then getting little responce.

If I do as say how would you list all the job numbers (Via HTML) and only the ones assigned to the individual to be checked.

This is what I whipped up but have not had a chance to check it. (I used something similar for a select box insted of a checkbox.

<?php
$db = mysql_connect("localhost", "user", "pwd");

$result1 = mysql_db_query("cad_design_db", "select employee_id, job_nm FROM assn_jobs");

while($row1 = mysql_fetch_object($result1)) {

if ($row[employee_id] == $row1->employee_id)
{
$checked="checked";
}
echo "<input type="checkbox" name=\"$job_nb\" value=\"$job_nb\" $checked> $job_nb";
unset($checked);
}
mysql_free_result($result1);

djg_uk

Hi Bill,

Everything looks sound apart from the if statement. Shouldn't the employee_id part here be a variable ($employee_id)? I don't quite understand the $row part either.

Cheers,

David Grant