[Resolved] inserting data into MySQL db

[claire]

Hi
I have the following script. It produces a matrix of names and weeknumbers on the screen. I would like to store that matrix in a MySQL db. Any ideas on how to do that?

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



// 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.



for ($i = 0; $i < sizeof($distribution); $i++) {
echo "<table>"; 
echo "<tr>"; 
echo "<td >"; 
echo "<h3>";



echo $persons[$i].", ";

echo "</td>";
 echo "</h3>";                                       

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)." /".($currentYear + intval(($distribution[$i][$j] - 1) / 52)); 
} else {
$toPrint = $distribution[$i][$j];
}
echo "<td nowrap width=\"30\">" ."<h3>".$toPrint;
echo "</td>";

}
echo "</tr>";

}

echo "</table>";
      

[bad76]

Hy

You can make a table like this:

ID as integer,
NUSER as integer,
REALNAME as char[255],
NWEEK as integer,
VALUE as integer

and insert data in this way:

for ($i = 0; $i < sizeof($distribution); $i++)
for ($j = 0; $j < sizeof($distribution[$i]); $j++) {
{
INSERT 0, '$i','$persons[$i]',$j,$distribution[$i][$j]
}

Last you can use the actual print routine to print out data, retrieving it from DB instead of array.

see you.

[claire]

hm tried that, but it will not insert the distribution of weeks into the db.

The printout looks like this:

Name | Weeks

Smith | 1 | 11 | 34
Jones | 2 | 12 | 35
Brown | 3 | 13 | 36
etc...

The Insert into db looks like this:

realname | nweek | value
Smith | 0 | 0
Jones | 0 | 0
Brown | 0 | 0

what to do???

[bad76]

But
all the value in db are 0 ?!?

[claire]

All the values but '$i' and '$persons[$i]' are 0. Those two are inserted correctly. So I get the names OK but not the week numbers.

[bad76]

Wait. Resuming all:

First start with your routines.

$startWithWeek = 1; 
$numberOfWeeks = $veckor;
$startWithPerson = $nummer;
$currentYear = $today;     

$distribution = array();                                        

for ($i = 0; $i < $startWithPerson; $i++) {
$distribution[$i][0] = 0;
}

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;
}

for ($i = ($startWithPerson + $numberOfWeeks - 1) % sizeof($persons) + 1; $i < sizeof($persons); $i++) {
$newWeekIndex = isset($distribution[$i]) ? sizeof($distribution[$i]) : 0;
$distribution[$i][$newWeekIndex] = 0;    

}

Then insert those values into a database (I'm forgotten: make ID autoincrement and primary key...):

//(... connection DB...)

for ($i = 0; $i < sizeof($distribution); $i++) 
for ($j = 0; $j < sizeof($distribution[$i]); $j++) 
{
   $myquery="INSERT INTO table VALUES(0, '".$i."','".$persons[$i]."','".$j."','".$distribution[$i][$j]."')";

   // to debug echo $myquery 
   mysql_query($myquery) or die ("Error: ".mysql_error());
} 

At last print out the table.

The same values you have in $distribution must be in the db. I have tried on my server.

See you.

[claire]

Yes, thanks... this works better, but not completely!

In the printout all the names have several occurences of weeks. (In my little example earlier, the name Smith should be associated with three different weeks (no 1, 11 and 34)).

In the database all names are written only once and with that name only the first occurrence of week associated with that name is inserted in the column.

I need to get a table with
-either the names repeated again as long as there are weeks left to distribute or
-a table similar to the printout where new columns are added after the name as long as there are weeks left to distribute

[bad76]

Sorry, don't understand.

This is a dump of my table after running example:

ID | NUSER | R.NAME | NWEEK | VALUE
1 | 0 | pil0 | 0 | 0
2 | 0 | pil0 | 1 | 8
3 | 0 | pil0 | 2 | 16
4 | 0 | pil0 | 3 | 24
5 | 0 | pil0 | 4 | 32
6 | 0 | pil0 | 5 | 40
7 | 0 | pil0 | 6 | 48
8 | 0 | pil0 | 7 | 56
9 | 1 | pil1 | 0 | 1
10 | 1 | pil1 | 1 | 9
11 | 1 | pil1 | 2 | 17
...

Every person have more than one occurence.

Check these lines:
for ($i = 0; $i < sizeof($distribution); $i++)
for ($j = 0; $j < sizeof($distribution[$i]); $j++) {

there isn't a ";" after first for...

If you want all occurrence in one row change DB:

ID as integer, (autoinc, primary key etc..)
NUSER as integer,
REALNAME as char[255],
VALUE as char[255].

And inner loop:
for ($i = 0; $i < sizeof($distribution); $i++)
{
$values="";
for ($j = 0; $j < sizeof($distribution[$i]); $j++)
{
$values.=$distribution[$i][$j].";";
}
$myquery="INSERT INTO distributor VALUES(0, '".$i."','".$persons[$i]."','".$values)";
// to debug echo $myquery
mysql_query($myquery) or die ("Error: ".mysql_error());
}

[claire]

This is a dump of my database. The reason I'm not getting a distribution of weeks in the VALUE column must be because the NWEEK isn't working.

ID |NUSER|RNAME|NWEEK|VALUE
10 |0 | Wilkingson | 0 | 1

11 |1 | Brunsteins | 0 | 2

12 |2 | Hasselman | 0 | 3

13 |3 | Enbergson | 0 | 4

14 |4 | Hagberger | 0 | 5

15 |5 | Renckgren | 0 | 6

16 |6 | Bergstams | 0 | 7

17 |7 | Seltstrom | 0 | 8

18 |8 | Haebering | 0 | 9

otherwise, your dump is exactly the way I want it to be. Just have to figure out why it isn't working at this end.

I haven't tried your second example (all in one row) since the above really is the way I want it.

Any ideas why the NWEEK isn't working? I don't have an ";" after the first for...

[bad76]

Hi,
this is my php file that have used for example.

<?php
$startWithWeek = 1;
$numberOfWeeks = 56;
$startWithPerson = 1;
$currentYear = 2003;
$distribution = array();

$persons[]="pil0";
$persons[]="pil1";
$persons[]="pil2";
$persons[]="pil3";
$persons[]="pil4";
$persons[]="pil5";
$persons[]="pil6";
$persons[]="pil7";

for ($i = 0; $i < $startWithPerson; $i++) {
$distribution[$i][0] = 0;
}

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;
}

for ($i = ($startWithPerson + $numberOfWeeks - 1) % sizeof($persons) + 1; $i < sizeof($persons); $i++) {
$newWeekIndex = isset($distribution[$i]) ? sizeof($distribution[$i]) : 0;
$distribution[$i][$newWeekIndex] = 0;
}

$dstlink = mysql_connect ("localhost", "root", "") or die ("Error on open DB");
mysql_select_db("test") or die ("Error on open table");


for ($i = 0; $i < sizeof($distribution); $i++)
for ($j = 0; $j < sizeof($distribution[$i]); $j++)
{
   $myquery="INSERT INTO distributor VALUES(0, '".$i."','".$persons[$i]."','".$j."','".$distribution[$i][$j]."')";

   // to debug echo $myquery
   mysql_query($myquery) or die ("Error: ".mysql_error());
}

?>

I hope this will help you.

[claire]

The code worked fine! Thanks!

Don't know what was wrong in my script, but works fine now!

Thanks again!!