Mysql INSERT loop

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
calimero_php
Forum Newbie
Posts: 4
Joined: Wed Oct 13, 2010 9:42 am

Mysql INSERT loop

Post by calimero_php »

Hey, i need some help with the following:

I have a DATETIME range derived from columns "dv_start" and "dv_end".
I want to make a loop in the SQL that keeps adding 15 minutes to "dv_start" while dv_start < dv_end. aka ADDTIME(dv_start, 00:15:00)
The results from adding 15 minutes to dv_start should be used as rows (either inserted in new table or something similar method of achieving it) so i can use COUNT(dv_busy_free) and group it by those results that SQL generates with WHILE;

The code relating to SELECT COUNT(...) FROM ... WHERE ... GROUP BY ... (is working nicely as planed)
but, these are the problems:
- when ever i try adding time it returns NULL (even after casting and juggling around with types, i didnt get reliable method of getting it working - sometimes it does work sometimes it hits me with NULL again) or when i try to use DATEDIFF() - code works nicely with NOW() and 1 column, but when i use both columns... it simply returns NULL => what am i missing here...
- (as i never used PROCEDURE, BEGIN, WHILE, ENDWHILE, END inside mysql) - my understanding and trial/error attempts of that piece of code didnt get anywhere so i guess there is no use posting basic copy/paste syntax from dev.mysql.com.

Table structure:

Code: Select all

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dv_start` datetime DEFAULT NULL,
  `dv_end` datetime DEFAULT NULL,
  `dv_busy_free` int(11) DEFAULT "0",
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
Table data:

Code: Select all

id|	dv_start|	                     dv_end
_______________________________________________________
1|	"0000-00-00 01:01:01"|	"0000-00-00 03:03:03"
2|	"0000-00-00 01:01:01"|	"0000-00-00 01:02:01"
3|	"0000-00-00 01:01:01"|	"0000-00-00 01:03:01"
4|	"0000-00-00 01:01:01"|	"0000-00-00 01:04:01"
5|	"0000-00-01 01:01:01"|	"0000-00-01 01:02:01"
6|	"0000-00-01 01:01:01"|	"0000-00-01 01:03:01"
7|	"1000-00-03 01:01:01"|	"0000-00-01 01:04:01"


After hitting a wall for hours i could really use some help.
Tnx.
Post Reply