Listing 18 names consecutively over 52 weeks

[claire]

Hi
I have the following problem and not a clue on how to solve it!

I have 18 names in a MySQL db. For each week of the year everyone in the db takes turns being responsible for a task.This goes on year in and year out...

In order to know who during a week has the responsibility I prepare a schedule manually in the beginning of the year.
Eg this years schedule begins (the first week) with name no 12 performing the task. Name no 13 has the second week etc.Running through all names will eventually give the result that

name | weeks responible for task
(12) Sue | 1 | 19 | 37 | 3 year 2003
(13) Joe | 2 | 20 | 38 | 4 year 2003
etc

I sure would love to have MySQL and PHP give me an automated schedule each year.

Any ideas on how to do this?

[sarahk]

Haven't you already done it? You could just write the page in word, save as html and publish it.

or

You could create a mysql table to note all the names and then php could query who was responsible for "today" and display just that person's name.

[GBahle]

I'm not quite certain that this is what you have in mind; here's what I think you want ( i'm at an Internet Cafe on Gran Canaria atm, so no working code, sorry)

get yourself an array with the names (from your mysql database).

suppose you want to start with person nr 12 of your persons array in week1.
then person12 has to do whatever in week 1, week 19, week 37, week 55, week 73, etc... of course, the last two arent relevant, cause a year only has 52 weeks.
Now, simply write 2 for loops and assign each person in your first array a list or array that stores the weeks they are due. If you are not starting with the first person, make sure that the array indices in the for loops are correct by using modulo operations.

If you think that covers what you have in mind but have no idea how to do it, just post again, I'll be back home in a few days.

< Gernot ><

[toma42]

You need to make this flexible. Using pear classes, here's how:

1. get the # of people
   $people_count = $db->getOne('select count(*) from people');

2. assume 51 weeks / year
   $weeks = 51

3. use a second table to say who is assigned each week
   create table assignment(ref_people, week);
   where week and ref_people are int's

4. populate it

   $start = 1;
   $people = $db->getAll('select * from people');
   foreach ($people as $row) {
   for ($i = 1; $i <= $weeks; $i + $people_count) {
   $db->query("insert into assignment (ref_people, week) values ($row[people_key], " . ($i + $start) . ")");
   }
   $start ++;
   }
   

[toma42]

You need to make this flexible. Using pear classes, here's how:

1. get the # of people
   $people_count = $db->getOne('select count(*) from people');

2. assume 51 weeks / year
   $weeks = 51

3. use a second table to say who is assigned each week
   create table assignment(ref_people, week);
   where week and ref_people are int's

4. populate it

   $start = 1;
   $people = $db->getAll('select * from people');
   foreach ($people as $row) {
   for ($i = 1; $i <= $weeks; $i + $people_count) {
   $db->query("insert into assignment (ref_people, week) values ($row[people_key], " . ($i + $start) . ")");
   }
   $start ++;
   }
   

[GBahle]

Since that data is easily derived from the existing data in the database, why store it in there?
Seems a bit redundant when you can easily create it on the fly...
I mean, databases are meant to be as free of redundancy as possible and in this case, there's an algorithm that supplies the information. It's running in O(n^2), but with since n is reasonably small, thats not a problem.

[ranam]

I am not exactly sure but this is probably what you want (crude version). Just set the $serial variable to the id you want to stary with.

<?php
$SESS_DBHOST = "localhost";			/* database server hostname */
$SESS_DBNAME = "temp";				/* database name */
$SESS_DBUSER = "root";				/* database user */
$SESS_DBPASS = "";			/* database password */

$SESS_DBH = mysql_connect($SESS_DBHOST, $SESS_DBUSER,$SESS_DBPASS);
mysql_select_db($SESS_DBNAME,$SESS_DBH);

$serial=2;
$current=1;
$next=1;

$time  = mktime (0,0,0,12,31,date("Y"));
$week=date("W", $time);                 // March 10, 2001, 5:16 pm

$time  = mktime (0,0,0,1,1,date("Y"));
$week-=date("W", $time);                 // March 10, 2001, 5:16 pm

$result = mysql_query("SELECT * FROM users",$SESS_DBH);
$users=mysql_affected_rows();

$result = mysql_query("SELECT * FROM users WHERE id>=$serial ORDER BY id",$SESS_DBH);
while($myrow = mysql_fetch_array($result))
	{
	$current=$next;
	$next++;
	echo "($myrow[id]) $myrow[name] ";
	while($current<=$week)
		{
		echo $current." ";
		$current+=$users;
		}
	echo $current-$week." of next year";
	echo "<br>";
	}

$result = mysql_query("SELECT * FROM users WHERE id<$serial ORDER BY id",$SESS_DBH);
while($myrow = mysql_fetch_array($result))
	{
	$current=$next;
	$next++;
	echo "($myrow[id]) $myrow[name] ";
	while($current<=$week)
		{
		echo $current." ";
		$current+=$users;
		}
	echo $current-$week." of next year";
	echo "<br>";

}



?>

[mrmufin]

Great googly-moogly! Nuthin' like makin' a simple task unnecessarily complex, eh?

// Set the time to Monday:
$monday = mktime(0,0,0,9,9,2002);
// Here are the people:
$names = array('Jake','Mary','Sue','Alice','Mufin','Biff','Larry');
// Count the names:
$n = count($names);
// Start a weeks counter:
$w = 0;
while($w < 52) {
        for($i = 0; $i < $n; $i++) {
                $monday = $monday + 604800;
                echo "<br>{$names[$i]} (Starting): "  . date("F d, Y",$monday);
                $w++;
        }
}

[claire]

I've played around a little bit with your suggested codes...still not getting it to work the way I want to...
ranam code - need to test more - not quite sure what I'm getting. It counts OK for the first year but then adds on way beyond week 52...
The neat thing with ranam code is that I get the names listed once in a column followed by the weeks listed in the row. Just like my example above. Much easier to print out than a list of 52 rows (weeks) with names being repeated every 19th row.

mrmufin code doesn't allow me to start at a fixed name ($i=5 will simply ignore the first five names.) is it easy to fix?

i sure would love to try the idea GBahle suggested. Maybe you can give me some help when you get back home?

/thanks

[GBahle]

Try this

<?php

$startWithWeek = 10;      // The week of the current year to start with
$numberOfWeeks = 140;     // How many weeks to distribute
$startWithPerson = 2;    // The person to start with (counting from 0 onward)
$currentYear = 2002;     // The current year
$distribution = array(); // This will store the actual distribution

$persons = array("Mr. 1", "Ms. 2", "Ms. 3", "Mr. 4", "Mr. 5", "Ms. 6", "Mr. 7"); // Populate this from your DB

// Fill the first few arrayspots up to the starting person with 0s to make table creation easier later
for ($i = 0; $i < $startWithPerson; $i++) {
	$distribution[$i][0] = 0;
}

// Distribute the weeks among the persons
for ($i = $startWithWeek; $i < $numberOfWeeks + $startWithWeek; $i++) {
	$distPersonIndex = ($startWithPerson + $i - $startWithWeek) % sizeof($persons);
	$newWeekIndex = isset($distribution[$distPersonIndex]) ? sizeof($distribution[$distPersonIndex]) : 0;
	$distribution[$distPersonIndex][$newWeekIndex] = $i;
}

// Fill the last few arrayspots from the last person till the end of that "column" with 0s (for table creation)
for ($i = ($startWithPerson + $numberOfWeeks - 1) % sizeof($persons) + 1; $i < sizeof($persons); $i++) {
	$newWeekIndex = isset($distribution[$i]) ? sizeof($distribution[$i]) : 0;
	$distribution[$i][$newWeekIndex] = 0;	
}

// $distribution is now a matrix with each row corresponding to the weeks assigned a particular person

// print the results in a table
// The outer for loop: Print a row to display each person's information
// The inner for loop: Print a cell for each week assigned to the current person
// The if statement: Check whether the array contains a 0 (replace that with a blank space), or if the week nr is bigger 52
//   in which case it's in on of the next years.
echo "<table cellpadding=\"2\" border=\"1\">";

for ($i = 0; $i < sizeof($distribution); $i++) {
	echo "<tr><td width=\"100\">";
	echo $persons[$i]." ";
	echo "</td>";

	for ($j = 0; $j < sizeof($distribution[$i]); $j++) {
		if ($distribution[$i][$j] == 0) {
			$toPrint = "&nbsp;";
		} else if ($distribution[$i][$j] > 52) {
			$toPrint = ((($distribution[$i][$j]-1) % 52)+1)." year ".($currentYear + intval(($distribution[$i][$j] - 1) / 52)); 
		} else {
			$toPrint = $distribution[$i][$j];
		}
		echo "<td nowrap width=\"30\">".$toPrint."</td>";
	}
	echo "</tr>";
}
echo "</table>";

?>

[claire]

YEP! That's it! Beautiful!

I'll prepare a HTMLform where the administrator (that's me right now!) inputs the vars

$startWithWeek     
$numberOfWeeks     
$startWithPerson  

so that maximum flexibility is provided for the printout. I'll also pick up the var $currentYear from the MySQL server.

In this way, I can do a new printout in the middle of the year if e.g one of the names changes. And the output can run over any length of time (doesn't have to be a calender year)

GREAT!!

Once again, thanks a million, and I've learnt a lot!