Insert date +7 days

PHP
1

Hey,

I am trying to do an insert statement whereby i add multiple queries based on value from a dropdown. Basically a user will be adding fitness courses to a website. This is done simply by going into the admin and filling in the following form:

http://www.glofamily.com/glo/images/courses.jpg

It looks quite simple, the gist of it is that each course is set on a specific date. I have all of this working perfectly. However the client has asked for something more as usual. Sometimes courses will rung on the same day but for multiple weeks, and instead of having to go in every week putting in a course for the same day they want a way to enable “Recurring courses” by a given number of weeks.

So in the image above you can see i have a “Recurring weeks” field. What i intend to do is when the user selects lets say ‘3’ it adds the course with the date of (as per the image) “03 06 2010” and also inserts another 2.

So there would in essence be 3 courses added, each with different dates. In this case it should add like so:

“03 06 2010”
“10 06 2010”
“17 06 2010”

You see what i mean? So there would be 3 separate inserts.

First of all, is this a good way to do this?

Secondly how can i do this in terms of the SQL?

I currently have this, but needs to somehow check to see the value selected from the dropdown add the date and then add any recurring weeks:


    public function insertCourse($category){

        $date = $_POST['year'].'-'.$_POST['month'].'-'.$_POST['day'] ." ".$_POST['hours'].":".$_POST['minutes'].":00";
        $startdate = "0000-00-00 ".$_POST['starthours'].":".$_POST['startminutes'].":00";
        $enddate = "0000-00-00 ".$_POST['finishhours'].":".$_POST['finishminutes'].":00";

        $sql = "INSERT INTO tbl_courses
                    (catID, locID, date, startdate, enddate, title, body, email, phone, image, date_added) VALUES
                    (
                    '".mysql_real_escape_string($_POST['catID'])."',
                    '".mysql_real_escape_string($_POST['locID'])."',
                    '".mysql_real_escape_string($date)."',
                    '".mysql_real_escape_string($startdate)."',
                    '".mysql_real_escape_string($enddate)."',
                    '".mysql_real_escape_string($_POST['title'])."',
                    '".mysql_real_escape_string($_POST['body'])."',
                    '".mysql_real_escape_string($_POST['email'])."',
                    '".mysql_real_escape_string($_POST['phone'])."',
                    '".mysql_real_escape_string($_FILES['image']['name'])."',
                    now()
                    )";
        $result = mysql_query($sql) or die(mysql_error());
        return "Successfully added event";
    }

So somehow i need to check if the value is more than 1, and if it is then do the one insert and then another adding an extra 7 days to the first date and so on…?

Is this possible?

Apologies if i have confused you…

Thanks again…

2

Yes, you need a way to group the same courses togther. The recurring id sounds good.
Then when you update you first delete all rows with that id, and then resume as if it’s a normal insert.

3

If you have fixed the date issues I would do something like this instead of executing query each time in the loop.


for($i = 0; $i < $recWeeks; $i++){ 
    $data[] = "'" . mysql_real_escape_string($_POST['catID'])."', '" 
            . mysql_real_escape_string($_POST['locID'])."','" 
            . mysql_real_escape_string($date) . "', '" 
            . mysql_real_escape_string($startdate)."' " . (($i + 1) * 7) . ", '" 
            . mysql_real_escape_string($enddate)."','" 
            . mysql_real_escape_string($_POST['title'])."', '" 
            . mysql_real_escape_string($_POST['body'])."','" 
            . mysql_real_escape_string($_POST['email'])."','" 
            . mysql_real_escape_string($_POST['practitioner'])."', '" 
            . mysql_real_escape_string($_POST['phone'])."','" 
            . mysql_real_escape_string($_FILES['image']['name']) . "', now()"; 
}
$sql = "INSERT INTO tbl_courses (catID, locID, date, startdate, enddate, title, body, email, practitioner, phone, image, date_added) 
VALUES 
(" . implode("),\
(", $data) . ")";
$result = mysql_query($sql) or die(mysql_error());
4

Hey,

Thanks ScallioXTX.

So would i need a for loop in my insert statement to check to see how many dates need to be recurring?

I’m just slightly confused as to how my insert statement would change?

I tried something but don’t know where to go from here:


		$recWeeks = mysql_real_escape_string($_POST['recWeeks']);
		
        for($i=0; $i<recWeeks; $i++){
	        $sql = "INSERT INTO tbl_courses
	                    (catID, locID, date, startdate, enddate, title, body, email, practitioner, phone, image, date_added) VALUES
	                    (
	                    '".mysql_real_escape_string($_POST['catID'])."',
	                    '".mysql_real_escape_string($_POST['locID'])."',
	                    '".mysql_real_escape_string($date)."',
	                    '".mysql_real_escape_string($startdate)."',
	                    '".mysql_real_escape_string($enddate)."',
	                    '".mysql_real_escape_string($_POST['title'])."',
	                    '".mysql_real_escape_string($_POST['body'])."',
	                    '".mysql_real_escape_string($_POST['email'])."',
	                    '".mysql_real_escape_string($_POST['practitioner'])."',
	                    '".mysql_real_escape_string($_POST['phone'])."',
	                    '".mysql_real_escape_string($_FILES['image']['name'])."',
	                    now()
	                    )";
	        $result = mysql_query($sql) or die(mysql_error());
        }

$recWeeks is a dropdown that contains the value of the weeks recurring as per the image…

:confused:

5

Good catch, I hadn’t seen that one :slight_smile:

You’re welcome :slight_smile:

6 

$recWeeks = mysql_real_escape_string($_POST['recWeeks']);
		
for($i=0; $i<recWeeks; $i++){
	$sql = "INSERT INTO tbl_courses
		(catID, locID, date, startdate, enddate, title, body, email, practitioner, phone, image, date_added) VALUES
		(
		'".mysql_real_escape_string($_POST['catID'])."',
		'".mysql_real_escape_string($_POST['locID'])."',
		'".mysql_real_escape_string($date)."', 
		'".mysql_real_escape_string($startdate)."' + INTERVAL \\'" .($i * 7). "\\' DAY,
		'".mysql_real_escape_string($enddate)."',
		'".mysql_real_escape_string($_POST['title'])."',
		'".mysql_real_escape_string($_POST['body'])."',
		'".mysql_real_escape_string($_POST['email'])."',
		'".mysql_real_escape_string($_POST['practitioner'])."',
		'".mysql_real_escape_string($_POST['phone'])."',
		'".mysql_real_escape_string($_FILES['image']['name'])."',
		now()
		)";
	$result = mysql_query($sql) or die(mysql_error());
}

For $i = 0 you add 0 * 7 = 0 days, for $i = 1 you add 1 * 7 days, etc etc :slight_smile:

Edit:

Don’t forget to take care of the implications this has on your update script.
If the user changes from 3 weeks to 4, you only have to add one record, if he changes from 4 to 3 you have to remove one, etc
I’ve always found it easier to avoid such tedious checking and remove all currently concerned rows first and then add them again using the new $recWeeks value

7

Oh School boy error!! :wink:

It was the return $result;

:lol:

Thanks Scallio, really appreciate your help.

Thanks again.

8

why not just do it with mysql …

date_add(now() interval 7 day)

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

similar functions can be found in PHP
http://www.php.net/manual/en/datetime.add.php

you can use the build in classes for this.

for example from the above page…

$date = date_create(‘2000-01-01’);
date_add($date, date_interval_create_from_date_string(‘10 days’));
echo date_format($date, ‘Y-m-d’);

9

You can use something along the lines of

… '“.mysql_real_escape_string($date).” + INTERVAL \‘7\’ DAY"
… '“.mysql_real_escape_string($date).” + INTERVAL \‘14\’ DAY"

etc

10

I mean how would i be able to go through each course and update. Would i need some sort of Recurring ID so i can point to a specific set of courses?

11

Hey,

I had to change the code slightly as i was getting a syntax error, i changed it to this and it now works…


'".mysql_real_escape_string($date)."' + INTERVAL ($i * 7) DAY,

Now last thing, you mentioned to do the update i would need to add or delete a value, so just to show you my update statement at the moment:


    public function updateCourse($category){

        if(is_numeric($_POST['ID'])){

            if(isset($_FILES['image']['name']) && $_FILES['image']['name'] != ""){
                $image = ", image = '".mysql_real_escape_string($_FILES['image']['name'])."'";
            }else{
                $image = '';
            }
            
        	$date = $_POST['year'].'-'.$_POST['month'].'-'.$_POST['day'] ." ".$_POST['hours'].":".$_POST['minutes'].":00";
	        $startdate = "0000-00-00 ".$_POST['starthours'].":".$_POST['startminutes'].":00";
	        $enddate = "0000-00-00 ".$_POST['endhours'].":".$_POST['endminutes'].":00";

            $sql = "UPDATE tbl_courses SET
                    date = '".mysql_real_escape_string($date)."',
                    startdate = '".mysql_real_escape_string($startdate)."',
                    enddate = '".mysql_real_escape_string($enddate)."',
                    catID = '".mysql_real_escape_string($_POST['catID'])."',
                    locID = '".mysql_real_escape_string($_POST['locID'])."',
                    title = '".mysql_real_escape_string($_POST['title'])."',
                    body = '".mysql_real_escape_string($_POST['body'])."',
                    email = '".mysql_real_escape_string($_POST['email'])."',
                    practitioner = '".mysql_real_escape_string($_POST['practitioner'])."',
                    phone = '".mysql_real_escape_string($_POST['phone'])."',
                    date_added = now() 
                    ".$image."
                    WHERE ID = ".$_POST['ID']."";

            $result = mysql_query($sql);
            return $result;
        }else{
            die('ID needs to be numeric');
        }
    }

Can you see how i can incorporate this into here?

Thanks

12

Strange, the code looks fine (you’re using is_numeric, mysql_real_escape_string and everything, nice! :tup:) from what I can see.
Do you get an error?

13

Haha, great minds think alike! :lol:

I just implemented this:


    public function updateCourse($category){

		//DELETE the course
        if(is_numeric($_GET['RID'])){
            $sql = "DELETE from tbl_courses WHERE RID = '".$_GET['RID']."'";
            $result = mysql_query($sql) or die(mysql_error());
            return $result;
        }else{
            die('RID needs to be numeric');
        }
		
		//SELECT the MAX ID
		$query = "SELECT MAX(RID) as Max_ID FROM tbl_courses 
        WHERE deleted = 0";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result);


		//INSERT the new course
        $date = $_POST['year'].'-'.$_POST['month'].'-'.$_POST['day'] ." ".$_POST['hours'].":".$_POST['minutes'].":00";
        $startdate = "0000-00-00 ".$_POST['starthours'].":".$_POST['startminutes'].":00";
        $enddate = "0000-00-00 ".$_POST['finishhours'].":".$_POST['finishminutes'].":00";
		$recWeeks = mysql_real_escape_string($_POST['recWeeks']);
		
		for($i=0; $i<$recWeeks; $i++){
		    $sql = "INSERT INTO tbl_courses
		        (catID, locID, RID, weeks, date, startdate, enddate, title, body, email, practitioner, phone, image, date_added) VALUES
		        (
		        '".mysql_real_escape_string($_POST['catID'])."',
		        '".mysql_real_escape_string($_POST['locID'])."',
		    	'".mysql_real_escape_string($row['Max_ID'])."' + 1,
		        '".mysql_real_escape_string($_POST['recWeeks'])."',
				'".mysql_real_escape_string($date)."' + INTERVAL ($i * 7) DAY,		        
				'".mysql_real_escape_string($startdate)."',	        
				'".mysql_real_escape_string($enddate)."',
		        '".mysql_real_escape_string($_POST['title'])."',
		        '".mysql_real_escape_string($_POST['body'])."',
		        '".mysql_real_escape_string($_POST['email'])."',
		        '".mysql_real_escape_string($_POST['practitioner'])."',
		        '".mysql_real_escape_string($_POST['phone'])."',
		        '".mysql_real_escape_string($_FILES['image']['name'])."',
		        now()
		        )";
        $result = mysql_query($sql) or die(mysql_error());
        }
        return "Successfully added course";
    }

What happens here is the DELETE works but the insert does not.

Can you see any problems?