[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-03-29
12
Medium Priority
?
570 Views
Last Modified: 2012-06-21
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
Comment
Question by:revo1059
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 6

Expert Comment

by:campbelc
ID: 16328471
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
 
LVL 27

Expert Comment

by:Nopius
ID: 16328701
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
 
LVL 30

Assisted Solution

by:todd_farmer
todd_farmer earned 400 total points
ID: 16329469
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 11

Assisted Solution

by:star_trek
star_trek earned 1600 total points
ID: 16333542
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
 
LVL 11

Expert Comment

by:star_trek
ID: 16333572

Note : The delete statement in your case should be
DELETE FROM it_weekend where YEAR(your_date_column) > '2006';
0
 
LVL 1

Author Comment

by:revo1059
ID: 16339389
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
 
LVL 27

Expert Comment

by:Nopius
ID: 16339911
Date string is incorrect: '2006-01-0', there is no 0 day in a month
0
 
LVL 11

Accepted Solution

by:
star_trek earned 1600 total points
ID: 16343153
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
 
LVL 1

Author Comment

by:revo1059
ID: 16343179
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
 
LVL 1

Author Comment

by:revo1059
ID: 16343192
The table is called lt_weeks and the dave field is weekend
                          < L NOT i  >
0
 
LVL 1

Author Comment

by:revo1059
ID: 16343256
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
 
LVL 11

Expert Comment

by:star_trek
ID: 16343264
what error are you getting now?
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question