Page 1 of 1

creating and reading from an 'array'

Posted: Tue May 29, 2012 12:12 am
by inosent1
i have a table1 with a set of tasks

group task
2 research
2 analyze
3 review
3 write
3 report

what i want to do is write a query that says something like this:

"insert into [table2] each task from [table1] where the group number is 2"

so the result of the query will show table2 looking like this:

id task
1 research
2 analyze


the way i have been doing it is hard coding:

Code: Select all

$task1 = 'research';
$task2 = 'analyze';

$task_bundle = $task1  . '; ' . $task2;
$query1 = "INSERT INTO table2 (group, task) VALUES ('$group','$task1')";
mysql_query($query1);
$query2 = "INSERT INTO table2 (group, task) VALUES ('$group','$task2')";
mysql_query($query2);

$query3 = "INSERT INTO table3 (task_bundle) VALUES ('$task_bundle')";
mysql_query($query3);


so i need to pull each record from table 1, list it as a separate item to be placed into table2, and then group them into a text string and place that in table3.

Re: creating and reading from an 'array'

Posted: Tue May 29, 2012 1:44 am
by requinix
Any particular reason you have to insert them into another table? Can't you just do a SELECT on table2 when you need the information?

Re: creating and reading from an 'array'

Posted: Tue May 29, 2012 9:18 am
by inosent1
table1 is a list of tasks, that changes, some get added, deleted, revised.

a manager assigns tasks from table1 to a specific employee. now we have a table with tasks created for the employee, with start date, end date, and other vitals. now we can search based on who has been assigned what task, etc

thanks for the reply

Re: creating and reading from an 'array'

Posted: Tue May 29, 2012 9:37 am
by Celauran
Wouldn't it be more efficient to just add an assigned_to column to your tasks table that references user IDs or something along those lines?

Re: creating and reading from an 'array'

Posted: Wed May 30, 2012 8:41 am
by inosent1
good question

with hundreds of incoming cases on a monthly basis the same task can be assigned to several employees for the different files. the assignment table keeps track of assignment performance, etc, and makes it a little easier to see what is going on, etc

so, getting back to the question ... :)

thanks for the reply

Re: creating and reading from an 'array'

Posted: Wed May 30, 2012 8:44 am
by Celauran
You've just described a many-to-many relationship, in which case you'll actually want a third table connecting users to tasks.