• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

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
0
revo1059
Asked:
revo1059
  • 4
  • 4
  • 2
  • +2
3 Solutions
 
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?
0
 
NopiusCommented:
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;
}
0
 
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';

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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


0
 
star_trekCommented:

Note : The delete statement in your case should be
DELETE FROM it_weekend where YEAR(your_date_column) > '2006';
0
 
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
0
 
NopiusCommented:
Date string is incorrect: '2006-01-0', there is no 0 day in a month
0
 
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 ;


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


0
 
revo1059Author Commented:
The table is called lt_weeks and the dave field is weekend
                          < L NOT i  >
0
 
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
0
 
star_trekCommented:
what error are you getting now?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now