Insert 52 weeks for each year in MySQL (PHP script)

Question

Hi,

I want in a table called week_year with following schema:

Week_year = {id, week, year}

To insert the weeks for each year, such that, for 2001 there is week 1, week 2, week 3, … , week 52, and then start over for year 2002 up until the year 2009.

I’ve tried different PHP scripts but can’t seem to be getting it right. I’ve tried different tables, one for year and one for week and then give each week a year_id, but that didn’t seem to be effective.

I’m hoping that someone can help me create, what I believe is a simple PHP loop(s) to generate these numbers and insert them into my MySQL database.

Added additional Information posted as an answer

I've tried this code, which is a endless loop of the year:

<?php

$year = 2001;
$week_start = 1;
$week_end = 52;

for ( $week_start = 1; $week_start <= 52; $week_start++ ) {

echo $week_start;
echo "<br />";
    for ($week_start = 1; $week_start <= 52; $year++) {
        echo $year;
        echo "<br />";
    }
}
?>

Answer 1

I've tried this code, which is a endless loop of the year:

<?php

$year = 2001;
$week_start = 1;
$week_end = 52;

for ( $week_start = 1; $week_start <= 52; $week_start++ ) {

echo $week_start;
echo "<br />";
    for ($week_start = 1; $week_start <= 52; $year++) {
        echo $year;
        echo "<br />";
    }
}
?>

Answer 2

$id_start = 1;
for ($year=2002; $year<=2009; $year++) {
   $values = array();
   for ($week=1; $week<=52; $week++) {
      $values[] = implode(',', array($id_start++, $week, $year));
   }
   $query = 'INSERT INTO `week_year` (id, week, year) ' 
          . 'VALUES (' . implode('),(', $values) . ')';

   $db->query($query);
}

** NOTE ** check out the localtime() PHP function. You don't need to have a table with week/year, simply a timestamp and use that timestamp with $info = localtime($row->timestamp, true) and do $weekNumber = floor($info['tm_yday'] / 7); to get your week number. Just a thought.

Answer 3

Do this in SQL, not PHP:

create table artificial_range( int id not null auto_increment, ci int);

insert into artificial_range(c1) values (1);

-- NOW DOUBLE THE SIZE OF artificial_range

insert into artificial_range(c1) select c1 from artificial_range;

-- repeat the above insert five more times; -- you now have 64 rows in artificial_range

-- now insert into week_year:
insert into week_year(week, year)
select a.id, b.id + 2000
from 
  artificial_range a,
  artificial_range b 
where a.id < 53 and b.id < 10;


-- or even better, just make week_year a view:
create view week_year as
select a.id as week, b.id + 2000 as year
from 
  artificial_range a,
  artificial_range b 
where a.id < 53 and b.id < 10;

One caveat: depending on what you mean by 'week', some years have 53 weeks.

Answer 4

just call the populate_year_week(2001,2009) stored proc from your php script !

drop table if exists year_week;

create table year_week
(
year_id smallint unsigned not null,
week_id tinyint unsigned not null,
primary key (year_id, week_id)
);

delimiter ;

drop procedure if exists populate_year_week;

delimiter #

create procedure populate_year_week
(
in from_yr smallint unsigned,
in to_yr smallint unsigned
)
proc_main:begin

declare yr smallint unsigned default 0;
declare wk tinyint unsigned default 0;

  truncate table year_week;

  -- put some validation here !
  if to_yr < from_yr then
        leave proc_main;
    end if;

  -- create years and weeks...
  set yr = from_yr;
    while yr <= to_yr do

    set wk = 1;
    while wk < 53 do
      insert into year_week (year_id, week_id) values (yr,wk);    
      set wk=wk+1;
    end while;

    set yr=yr+1;
  end while;

end proc_main #

delimiter ;

call populate_year_week(2001,2009);

select * from year_week order by year_id, week_id;