Insert by days of the week

[carrilo]

I have a mysql database to organise classes in a school

 

I use the following code to insert classes into the database

 

$current_date = new DateTime($dt); 

$end_date = new DateTime($dt2); 

 

if (empty($errors))

{

 

while ($current_date < $end_date) { 

    $date_string = $current_date->format('Y-m-d'); 

 

 

$query = "INSERT INTO clases (fecha, hora,  teacher, duracion, grupo ) VALUES ('$date_string', $ti, '$tch', '$drt' , '$grp' )";

$result = @mysql_query ($query);

 

$current_date->modify('+1 week'); 

 

This inserts one class every week between the start date and the end date. So If the start date is a Tuesday it will insert a class every tuesday between the 2 dates.

 

What I want to do now is to be able to insert classes on more than one day of the week,  for example every  

Tuesday and Thursday between the 2 dates.

 

How could I do this? 

Edited July 3, 2013 by carrilo

[AbraCadaver]

Well, it should be more elegant than this, but my brain is tired and this is what I came up with:

$days = array('tuesday', 'thursday');
 
while($current_date <= $end_date) {
	foreach($days as $day) { 
		if($current_date <= $end_date) {
			if(strtolower($current_date->format('l')) != strtolower($day)) {
				$date_string = $current_date->format('Y-m-d');
				$values[] =  "('$date_string', $ti, '$tch', '$drt', '$grp')";
				$current_date->modify("next $day");
			}
		}
	}
}
$values = implode(', ', $values);
$query = "INSERT INTO clases (fecha, hora,  teacher, duracion, grupo) VALUES $values";

Ideally though, you want to normalize your DB and not repeat all of the same info.

[DavidAM]

@AbraCadaver: That's an elegant solution, but there are a couple of problems with it:

1) The if($current_date <= $end_date) { is not needed since the condition is handled by the while loop

2) The $current_date->modify("next $day"); needs to be outside the foreach loop.

 

In fact, you can eliminate the foreach and use in_array:

 

 

$days = array('tuesday', 'thursday');

while($current_date <= $end_date) {
	if(in_array(strtolower($current_date->format('l')), $days)) {
		$date_string = $current_date->format('Y-m-d');
		$values[] = "('$date_string', $ti, '$tch', '$drt', '$grp')";
	}
	$current_date->modify("next $day");
}
$values = implode(', ', $values);
$query = "INSERT INTO clases (fecha, hora, teacher, duracion, grupo) VALUES $values";

@OP: You want to avoid running queries in a loop. This solution collects all of the values and executes a single INSERT statement after the loop.

 

Also, as AbraCadaver said, you really should consider normalizing your database.

[AbraCadaver]

Hi David, greetings from Katy.

 

1) I had to use the if because the foreach loops and the condition is only checked in the outer while loop.

2) The code posted doesn't work as $day doesn't get defined.  I modified it to use array_search and get the key to use $days[$key] which works but only does Tuesdays because the logic is flawed.

 

Your code got me to rethink it and I think this is the best I can do for now:

$days = array('tuesday', 'thursday');

while($current_date <= $end_date) {
	if(strtolower($current_date->format('l')) == strtolower(current($days)) && !$day = next($days)) {
		$day = reset($days);
	}
	$date_string = $current_date->format('Y-m-d');
	$values[] = "('$date_string', $ti, '$tch', '$drt', '$grp')";
	$current_date->modify("next $day");
}

[carrilo]

Thanks both of you for your help.

 

It is still not working. Currently I have used Abracdabra's 2nd code version as below.

 

 

 

 

 

$current_date = new DateTime($dt);

$end_date = new DateTime($dt2); 

 

$days = array('tuesday', 'thursday');

if (empty($errors))

{

 

 

while($current_date <= $end_date) {

if(strtolower($current_date->format('l')) == strtolower(current($days)) && !$day = next($days)) {

$day = reset($days);

}

$date_string = $current_date->format('Y-m-d');

$values[] = "('$date_string', $ti, '$tch', '$drt', '$grp')";

$current_date->modify("next $day");

}

$values = implode(', ', $values);

$query = "INSERT INTO clases (fecha, hora, teacher, duracion, grupo) VALUES $values";

 

if ($result)

{

 

echo "you have added classes ' . $dt. '<br />";

 

 

 

When I run the query it appears to work and gives the 'you have added classes' message but does not actually add anything to the database

[AbraCadaver]

You didn't run the query did you?

[carrilo]

For example 

$current_date = 2013-10-01

$end_date = 2013-11-1

 

It appears to run but does not enter anything into the database

[AbraCadaver]

Assuming you're connected to the database, you should have something like this:

$result = mysql_query($query) or die(mysql_error());

[carrilo]

OK OK 

 

thanks works perfectly

[carrilo]

This has a glitch

 

It works or doesn't work depending on the order you put the days in in the array.

 

for example if the first day is tuesday and the second thursday it works fine, however if I put in monday first then thursday it doesn't work (I get an error message). If I change it to tuesday first then monday it works. 

 

I can't see the logical reason for when it does or does not work.

 

Any ideas?

[DavidAM]

 

$days = array('tuesday', 'thursday');

while($current_date <= $end_date) {
	if(strtolower($current_date->format('l')) == strtolower(current($days)) && !$day = next($days)) {
		$day = reset($days);
	}
	$date_string = $current_date->format('Y-m-d');
	$values[] = "('$date_string', $ti, '$tch', '$drt', '$grp')";
	$current_date->modify("next $day");
}

 

I really don't see how this code could dependably work. Line 4 is saying if the date we are currently checking (in the while loop of dates) is the day of week at the current position of the days array, shift the position in the days array to the next position and if we are at the end, reset the position to the beginning. Lines 7,8 and 9 are executed regardless of whether the date is in the days array or not.

 

I hate to say "my code is better", but give it a try:

$days = array('tuesday', 'thursday');

while($current_date <= $end_date) {
	if(in_array(strtolower($current_date->format('l')), $days)) {
		$date_string = $current_date->format('Y-m-d');
		$values[] = "('$date_string', $ti, '$tch', '$drt', '$grp')";
	}
	$current_date->modify("next $day");
}
$values = implode(', ', $values);
$query = "INSERT INTO clases (fecha, hora, teacher, duracion, grupo) VALUES $values";

[carrilo]

Hi, thanks for the reply.

 

I have tried this but it just gives me a standard error message (fault in the code) when I execute the query

[DavidAM]

What error message? We can't really help if we don't know what the problem is.

 

Post the error message, and the code around the line number specified in the message.

[carrilo]

Sorry I meant the page won't load, there is an error in the code.

[mac_gyver]

use - $current_date->modify("+1 day"); and DavidAM's code should work.

 

straight forward code that loops over all the days in the range, testing if the current day of week is one of the desired days, is better in this case than trying to account for the starting day of the week, that can be anything, and needing to get that starting day up to one of the desired days of the week, then to get the current pointer to the $days array to that same value in order to get everything in sync before you can even start to actually loop to produce the result.

Edited July 11, 2013 by mac_gyver

[AbraCadaver]

use - $current_date->modify("+1 day"); and DavidAM's code should work.

 

straight forward code that loops over all the days in the range, testing if the current day of week is one of the desired days, is better in this case than trying to account for the starting day of the week, that can be anything, and needing to get that starting day up to one of the desired days of the week, then to get the current pointer to the $days array to that same value in order to get everything in sync before you can even start to actually loop to produce the result.

 

Yes, I was attempting to only loop as many times as there are valid dates (in $days array), hence my somewhat convoluted code and using the actual $day values from the code.  If I have time I'll fix it, but it seems that if you know you only want Mondays, then advance the date each time to Monday so that your loop only iterates 1/7 the number of times.  This could be important over several years.

[carrilo]

Ok many thanks seems to work fine

[carrilo]

My idea with this was to choose which two days in the array want in the array with post values from two drop down  menus

 

 

So I have

 

$cop = ($_POST['daya']);

$jp = ($_POST['dayb']);

 

$days = array('$cop', '$jp');

 

 

this does not work though.

 

It gives me this error

 

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 '' at line 1
Query: INSERT INTO clases (fecha, hora,hora2, teacher, duracion, grupo) VALUES

 

If I leave one as a day for example:

 

$days = array('$cop', 'friday');

 

 

it inserts classes on fridays but not on the other day.

 

I have checked that the post value is correct ( it is a day of the week)

 

Any ideas?

[AbraCadaver]

Where did you ever learn to put single quotes around variables?

 

Also, if you name the inputs like this:  name="days[]"

 

Then you will have an array and you can just do this in PHP:

 

$days = $_POST['days'];

Edited July 14, 2013 by AbraCadaver

[carrilo]

OK I have done this, both inputs (day 1 and day 2) are set as  name="days[]" and I have set $days to be  $days = $_POST['days']; as you say.

 

Same result, it just imputs one of the days (day 2) not both

Have I misunderstood?

 

(if I use double quotes I get error messages)