Forum Moderators: coopster
//select time where day = monday
$query1 = "SELECT time FROM coursetimes WHERE day = 'Monday'";
$result1 = mysql_query($query1);
$record1= mysql_fetch_assoc($result1);
$time1 = $record1['time'];if ($time1!= '')
{
//foreach loop here
foreach ($mondaylist as $day) { //if TIME = BLANK GO NO FURTHER
$queryins1="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time1')";
mysql_query($queryins1) or die(mysql_error());
}
}
//select time where day = tuesday
$query2 = "SELECT time FROM coursetimes WHERE day = 'Tuesday'";
$result2 = mysql_query($query2);
$record2= mysql_fetch_assoc($result2);
$time2 = $record2['time'];
if ($time2!= '')
{
//foreach loop here
foreach ($tuesdaylist as $day) { //if TIME = BLANK GO NO FURTHER
$queryins2="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time2')";
mysql_query($queryins2) or die(mysql_error());
}
}
this continues for all days of the week! so I've got this 7 times over. I know i need to put the time part and the day name in an array but how do I do this so it inserts the correct data?
thanks
$daysofweek = array('Monday', 'Tuesday', 'Wedensday', 'Thursday', 'Friday', 'Saturday', 'Sunday');
foreach ($daysofweek as $dayofweek)
{
$query = "SELECT time FROM coursetimes WHERE day = '".$dayofweek."'";
$result = mysql_query($query);
$record= mysql_fetch_assoc($result);
$time = $record['time'];
if ($time!= '')
{
//foreach loop here
foreach ($mondaylist as $day) {
$queryins="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time')";
mysql_query($queryins) or die(mysql_error());
}
}
}
$daysofweek = array('Monday', 'Tuesday', 'Wedensday', 'Thursday', 'Friday', 'Saturday', 'Sunday'); foreach ($daysofweek as $dayofweek)
{
$query = "SELECT time FROM coursetimes WHERE day = '".$dayofweek."'";
$result = mysql_query($query);
$record= mysql_fetch_assoc($result);
$time = $record['time'];
if ($time!= '')
{
//foreach loop here
foreach ([b]$mondaylist[/b] as $day) {
$queryins="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time')";
mysql_query($queryins) or die(mysql_error());
}
}
}
mondaylist is a list of all the mondays of the selected month. Earlier on in the code i get all of the mondays of the specified month, and all of the tuesdays etc, these are stored as Mondaylist, tuesdaylist, or daylist is all off them. daylist is obtained by reading the database to see what days are present (eg monday, wednesday) and then storing the days of the month that these fall on. As it stands this code only inserts the mondays, if i change it to daylist it inserts 3 sets of data because it finds 3 days in the database.
I also need tuesdaylist through to sundaylist inserting, how can i implement this?
thanks
[edited by: nshack31 at 3:37 pm (utc) on Feb. 25, 2006]
$daysofweek = array('Monday', 'Tuesday', 'Wedensday', 'Thursday', 'Friday', 'Saturday', 'Sunday');
foreach ($daysofweek as $dayofweek) {
$query = "SELECT time FROM coursetimes WHERE day = '".$dayofweek."'";
$result = mysql_query($query);
$record= mysql_fetch_assoc($result);
$time = $record['time'];
if ($time!= '') {
//foreach loop here
foreach (${strtolower($dayofweek) . 'list'} as $day) {
$queryins="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time')";
mysql_query($queryins) or die(mysql_error());
}
}
}
Let's just make the actual day the key and the $daylist as the value:
$daysofweek = array(
'Monday' => $mondaylist,
'Tuesday' => $tuesdaylist,
'Wedensday' => $wedensdaylist,
'Thursday' => $thurdaylist,
'Friday' => $fridaylist,
'Saturday' => $saturdaylist,
'Sunday'=> $sundaylist
);foreach ($daysofweek as $dayofweek => $daylist)
{$query = "SELECT time FROM coursetimes WHERE day = '".$dayofweek."'";
$result = mysql_query($query);
$record= mysql_fetch_assoc($result);
$time = $record['time'];if ($time!= '')
{
//foreach loop here
foreach ($daylist as $day) {
$queryins="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time')";
mysql_query($queryins) or die(mysql_error());
}
}}
php.net/manual/en/language.types.array.php
The link above has alot of good info.
Just a final quick question, in order to avoid the data being inserted over and over i first clear the table...
//deletefirst
$query="Delete From coursedates Where month = '$monthcal' AND year = '$yearcal' AND courseid = '$courseid'";//delete
mysql_query($query) or die(mysql_error());
but thinking about it, if there is already data in the table for the month I dont want to empty it or reinsert it. Whats the best way to edit the delete query? would the following work?...
foreach ($daysofweek as $dayofweek => $daylist)
{
//deletefirst
$query="Delete From coursedates Where month = '$monthcal' AND year = '$yearcal' AND courseid = '$courseid' AND day!= '".$dayofweek."'";//delete
mysql_query($query) or die(mysql_error());
ive tried mysql affected rows but cant get it to work because of $day.
can anybody help? thanks
are these courses added anytime or are they added on some kind of schedule? Is this something that could be run once a month and not have to worry at all whether it exists, just nuke and then add.
I am having a hard time answering because I seem to be missing some part of the process. I know the code, but giving you the best answer would require a better understanding of the logic you are dealing with (I hate leading people astray if I can help it).
I see we are delaing with courses and days of the week (scheduler or school courses maybe) but I don't really get the criteria invoved with insertion and deletion.
else if (isset($_GET['addall']))
{
$monthcal=$_GET['month'];
$yearcal=$_GET['year'];
$courseid=$_GET['courseid'];function getDays($month, $weekdays = '', $year = '') {
$weekdays = empty($weekdays)? array('Monday') : $weekdays;
$year = empty($year)? date('Y') : $year;
$days = date('t', mktime(0,0,0,$month,1,$year));
$out = array();
for ($i = 1; $i <= $days; $i++) {
$day = mktime(0,0,0,$month,$i,$year);
if (in_array(date('l', $day), $weekdays))
$out[] = $i;
}
return $out;
}
$query = "SELECT day FROM coursetimes where time!= ''";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
$day[]=$row['day'];
}
$daylist = getDays($monthcal, $day, $yearcal);
$mondaylist = getDays($monthcal, array('Monday'), $yearcal);
$tuesdaylist = getDays($monthcal, array('Tuesday'), $yearcal);
$wednesdaylist = getDays($monthcal, array('Wednesday'), $yearcal);
$thursdaylist = getDays($monthcal, array('Thursday'), $yearcal);
$fridaylist = getDays($monthcal, array('Friday'), $yearcal);
$saturdaylist = getDays($monthcal, array('Saturday'), $yearcal);
$sundaylist = getDays($monthcal, array('Sunday'), $yearcal);
$daysofweek = array(
'Monday' => $mondaylist,
'Tuesday' => $tuesdaylist,
'Wedensday' => $wedensdaylist,
'Thursday' => $thursdaylist,
'Friday' => $fridaylist,
'Saturday' => $saturdaylist,
'Sunday'=> $sundaylist
);
//delete all dates from coursedates unless one has a booking
$query2="DELETE FROM coursedates WHERE courseid = '$courseid' AND month = '$monthcal' AND year = '$yearcal' AND bookingid NOT IN
(SELECT bookingid FROM bookings)";
mysql_query($query2) or die(mysql_error());
foreach ($daysofweek as $dayofweek => $daylist)
{
$query = "SELECT time FROM coursetimes WHERE day = '".$dayofweek."'";
$result = mysql_query($query);
$record= mysql_fetch_assoc($result);
$time = $record['time'];
if ($time!= '')
{
//foreach loop here
foreach ($daylist as $day) {
[b]//dont insert if courseid, day, month,year already present[/b]
$queryins1="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time')";// and not already present
mysql_query($queryins1) or die(mysql_error());
}
}
}
//foreach ($daylist as $day) {
// $queryins="INSERT INTO coursedates (courseid, day, month, year, time) VALUES ('$courseid', '$day', '$monthcal', '$yearcal', '$time')";
// mysql_query($queryins) or die(mysql_error());
//}
}
add all is when the admin chooses to auto add courses into the calendar. the courseid is read and also the days that this course is taught on are read. so there is a table (coursetimes) with the days that the course takes place on with a time for the course.
the problem is now that if the admin clicks on auto insert, i dont want all the records deleting and reinserting because some may be linked to the bookings table by bookingid, in which case bookings will have been made so i need to keep that one in the database because its linked by bookingid!
so what i do now is delete all values and reinsert them if they dont have any bookings in the bookings table, this works fine. The slight problem is that if it does have a booking, it is not deleted (which is great) but it is added to the coursedates table again (which is not good)!
sorry my explinations are as poor as my programming :(
no no no, you were right on. You were dealing with an isolated piece of code and we understood but when we starting moving to questions of logic as opposed to simple syntax then more explanation is needed as to what is going on so that we can better understand how to answer.
don't be so hard on yourself. ;)
onto the process
well, it is a bit of a problem, you could actually do a select every time and see if the num_rows is > 0 for each iteration, this would solve your problem outright. I don't know how often this function is used but that might (read wil) cause a fair amount of overhead. That could end up being a ton of queries.
Is there any way to change the steps involved with doing this maybe? Changing the process for the end user to alleviate the load on this single script some?
hmmm, what about something a litlle different. I can see a couple possibilities, all off the top of my head, I don't know if any of them will work or how much work they might entail
1. select all of the courses with bookings and store them somewhere else before the delete, these could then be collated somehow into your arrays of dates and you could even match up your ids again before your inserts
2. delete everything and then update your booking rows so the ids match again, if these are true foreign keys then you might have to use some type of interim step so that mysql doesn't freak out. Maybe temp table or file.
it is tough when you start deleting things because it messe with your data integrity. Deleting something that is linked makes things even more difficult. I guess in this case it also depends if these are true foreign keys enforced by the db or if they can just be changed.
for those of you following along at home ;)
I had it so that update where day = $day etc and then count the affected rows, if none were affected then the insert into would take place, this wouldnt work however because of the $day variable.
I guess the best thing now is to take a break from it and approach it afresh! I'll take a look at what you have suggested. thanks for your help so far
..edit maybe i could allow it to enter the duplicate entry, and then delete that later on (delete the 2nd value that wont have a booking in the bookings table)? - i'll take a look at this
that may just be the ticket
<added>
>> hope thats ok
that's just fine as it deals with the query method itself
something like
select where month = $monthcal AND year = $yearcal AND courseid = $courseid AND count = 2
?
then i can delete from the duplicate selection where bookingID not in table bookings
thanks for your help all :)
so the table has
Monday------Thursday
--5-7--------5 - 7---
and so on. So now I need to edit my code! im using the following to take the days of the month, and insert the time for each day of the month....
else if ($action == "addall")
{
$monthcal=$_GET['month'];
$yearcal=$_GET['year'];
$courseid=$_GET['courseid'];
include 'open.php';
function getDays($month, $weekdays = '', $year = '') {
$weekdays = empty($weekdays)? array('Monday') : $weekdays;
$year = empty($year)? date('Y') : $year;
$days = date('t', mktime(0,0,0,$month,1,$year));
$out = array();
for ($i = 1; $i <= $days; $i++) {
$day = mktime(0,0,0,$month,$i,$year);
if (in_array(date('l', $day), $weekdays))
$out[] = $i;
} return $out;
}
$daylist = getDays($monthcal, array('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'), $yearcal);
$mondaylist = getDays($monthcal, array('Monday'), $yearcal);
$tuesdaylist = getDays($monthcal, array('Tuesday'), $yearcal);
$wednesdaylist = getDays($monthcal, array('Wednesday'), $yearcal);
$thursdaylist = getDays($monthcal, array('Thursday'), $yearcal);
$fridaylist = getDays($monthcal, array('Friday'), $yearcal);
$saturdaylist = getDays($monthcal, array('Saturday'), $yearcal);
$sundaylist = getDays($monthcal, array('Sunday'), $yearcal);
$daysofweek = array(
'Monday' => $mondaylist,
'Tuesday' => $tuesdaylist,
'Wednesday' => $wednesdaylist,
'Thursday' => $thursdaylist,
'Friday' => $fridaylist,
'Saturday' => $saturdaylist,
'Sunday'=> $sundaylist
);
$querytime="SELECT * From centres WHERE centreid = '1'";
$resulttime = mysql_query($querytime);
$recordtime = mysql_fetch_assoc($resulttime);
$monday = $recordtime['Monday'];
$tuesday = $recordtime['Tuesday'];
$wednesday = $recordtime['Wednesday'];
$thursday = $recordtime['Thursday'];
$friday = $recordtime['Friday'];
$satday = $recordtime['Saturday'];
$sunday = $recordtime['Sunday'];
$weekdays = array(
'Monday' => $monday,
'Tuesday' => $tuesday,
'Wednesday' => $wednesday,
'Thursday' => $thursday,
'Friday' => $friday,
'Saturday' => $saturday,
'Sunday'=> $sunday
);
//foreach loop here
foreach ($daylist as $day) {
foreach ($weekdays as $weekday){
if (!empty($weekday)) {
$queryins1="INSERT INTO coursedates (courseid, centreid, day, month, year, time) VALUES ('$courseid', '1', '$day', '$monthcal', '$yearcal', '$weekday')";
mysql_query($queryins1) or die(mysql_error());
}//endif
}
}
include 'close.php';
header ("Location: admincourse.php?courseid=$courseid&month=$monthcal&year=$yearcal");
}//end else if
but this is inserting the data for every day (not just those with a value in the day field, and it is entering the data 3 times, because there are 3 values in total in the day field (monday = 5-7, thursday = 5-7 and friday = 4.30 - 6.30)
how can i edit my code?!
any suggestions?
thanks
$querytime="SELECT * From centres WHERE centreid = '1'";
$resulttime = mysql_query($querytime);$recordtime = mysql_fetch_assoc($resulttime);
$monday = $recordtime['Monday'];
$tuesday = $recordtime['Tuesday'];
$wednesday = $recordtime['Wednesday'];
$thursday = $recordtime['Thursday'];
$friday = $recordtime['Friday'];
$satday = $recordtime['Saturday'];
$sunday = $recordtime['Sunday'];
//foreach loop here
if (!empty($monday))
{
foreach ($mondaylist as $day) {
$queryins1="INSERT INTO coursedates (courseid, centreid, day, month, year, time) VALUES ('$courseid', '1', '$day', '$monthcal', '$yearcal', '$monday')";
mysql_query($queryins1) or die(mysql_error());
}
}
if (!empty($tuesday))
{
foreach ($tuesdaylist as $day) {
$queryins1="INSERT INTO coursedates (courseid, centreid, day, month, year, time) VALUES ('$courseid', '1', '$day', '$monthcal', '$yearcal', '$tuesday')";
mysql_query($queryins1) or die(mysql_error());
}
}
if (!empty($wednesday))
{
foreach ($wednesdaylist as $day) {
$queryins1="INSERT INTO coursedates (courseid, centreid, day, month, year, time) VALUES ('$courseid', '1', '$day', '$monthcal', '$yearcal', '$wednesday')";
mysql_query($queryins1) or die(mysql_error());
}
}
if (!empty($thursday))
{
foreach ($thursdaylist as $day) {
$queryins1="INSERT INTO coursedates (courseid, centreid, day, month, year, time) VALUES ('$courseid', '1', '$day', '$monthcal', '$yearcal', '$thursday')";
mysql_query($queryins1) or die(mysql_error());
}
}
if (!empty($friday))
{
foreach ($fridaylist as $day) {
$queryins1="INSERT INTO coursedates (courseid, centreid, day, month, year, time) VALUES ('$courseid', '1', '$day', '$monthcal', '$yearcal', '$friday')";
mysql_query($queryins1) or die(mysql_error());
}
}
if (!empty($saturday))
{
foreach ($daturdaylist as $day) {
$queryins1="INSERT INTO coursedates (courseid, centreid, day, month, year, time) VALUES ('$courseid', '1', '$day', '$monthcal', '$yearcal', '$saturday')";
mysql_query($queryins1) or die(mysql_error());
}
}
if (!empty($sunday))
{
foreach ($sundaylist as $day) {
$queryins1="INSERT INTO coursedates (courseid, centreid, day, month, year, time) VALUES ('$courseid', '1', '$day', '$monthcal', '$yearcal', '$sunday')";
mysql_query($queryins1) or die(mysql_error());
}
}
