Script that will insert the date of every friday into a mysql database

i have a table called lt_weekend.  I want to be able to insert the date of all the fridays in the year 2006. I am using php 4 and mysql 5.0.18. Please help
LVL 1
revo1059Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

campbelcCommented:
I'm a major newbie with php but pretty good with MySQL. Do you have console access to MySQL or can you give me a sample php insert statement that I can replicate for you.

Example:
insert into it_weekend values ('01-06-2006');
insert into it_weekend values ('01-13-2006');

etc...

Also what other columns are there in your it_weekend table?
Artysystem administratorCommented:
The algorithm is:
1) Find 1st friday of 2006, it's a $start_day
2) While year of $curr_day is 2006:
  insert $start_day into database
  add 7 days to $start_day


// Begin from 2006-01-01
$start_day=mktime(0, 0, 0, 1, 1, 2006);
// Find 1st friday
for($i=1; $i<=7; $i++) {
  if (gmdate("N", $start_day)=="5") {
    break;
  }
  $start_day+=24*60*60; // next day
}

// Now start_day is a first friday in unix timestamp format (integer)
while (gmdate("Y", $start_day)!="2007") {
   // for appropriate time convertion look: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html
   // I suppose you have a TIMESTAMP type for your_date_field
   $query = "INSERT INTO lt_weekend (your_date_field) VALUES ($start_day)";
   $link.mysql_query($query);
  $start_day+=7*24*60*60;
}
todd_farmerCommented:
INSERT INTO it_weekend VALUES ('2006-01-06');

then run the following 53 times:

INSERT INTO it_weekend SELECT DATE_ADD(MAX(date_column), INTERVAL 1 WEEK) FROM it_weekend;

then run the following once:

DELETE FROM it_weekend WHERE YEAR(date_column) > '2006';

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

star_trekCommented:
To add to todd_farmer:
Since you are using mysql > 5.0. You can take advantage of the stored procedures as
CREATE PROCEDURE p01 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 54 DO
INSERT INTO  it_weekend VALUES(DATE_ADD('2006-01-06', INTERVAL v WEEK));
SET v = v + 1;
END WHILE;
DELETE FROM dt WHERE YEAR(dt1) > '2006';
END; //

You can call the stored procedure as
call p01 (); //

Note : // is the delimiter, you can change the delimiter by passing the mysql command
DELIMITER ; --This changes the delmiter to semi-colon


star_trekCommented:

Note : The delete statement in your case should be
DELETE FROM it_weekend where YEAR(your_date_column) > '2006';
revo1059Author Commented:
I like the idea of a stored procedure, because i will need to do this for all the years to come.  When I pasted the SP into my mysql command line, it gave me an error


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT
SET v = 0
WHILE v < 54 DO
INSERT INTO  lt_weekend VALUES(DATE_ADD('2006-01-0' at line 3


any ideas.  

also, the name of the table is lt_weekend... lt stands for list table.

thanks
Artysystem administratorCommented:
Date string is incorrect: '2006-01-0', there is no 0 day in a month
star_trekCommented:
You have to set the delemiter first in the mysql Command line

DELIMITER //

-- This above would set '//' as the delimiter and not ;, since we are going to use ; in our stored procedure

then copy paste the SQL code and call the stored procedure as above

you can change the delimiter if you want to ; from //, type the following on the mysql command line.

DELIMITER ;


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
revo1059Author Commented:
CREATE PROCEDURE p01 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 54 DO
INSERT INTO  lt_weeks VALUES(DATE_ADD('2006-01-06', INTERVAL v WEEK));
SET v = v + 1;
END WHILE;
DELETE FROM lt_weeks WHERE YEAR(weekend) > '2006';
END; //

this is what i typed into the mysql command line.  it cut off the rest of the line.  I put in the full date 2006-01-06.


revo1059Author Commented:
The table is called lt_weeks and the dave field is weekend
                          < L NOT i  >
revo1059Author Commented:
DELIMITER //
CREATE PROCEDURE p01 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 54 DO
INSERT INTO  lt_weeks VALUES(DATE_ADD('2006-01-06', INTERVAL v WEEK));
SET v = v + 1;
END WHILE;
DELETE FROM lt_weeks WHERE YEAR(weekend) > '2006';
END; //

THIS WAS THE FINAL CODE AND IT WORKED GREAT.  THANK YOU
star_trekCommented:
what error are you getting now?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.