while loop inside foreach loop problems

Question

I'm trying to update a range of dates with a Shift_ID but the Shift_ID that is entered depends upon what day of the week it is. So, for example, if I have a range of dates $from = "2012-12-01" $to = "2012-12-28" I'm trying to make it so that I can select a check box (for example: value="Fri" name = "offdays[]") to indicate what day is an offdays. If an offdays is matched with a day in the set range, then the Shift_ID = "6" otherwise it is a work day and Shift_ID = "3"

Hopefully this will all make sense in a minute, I'm doing my best to explain it as well as give you some useful variables.

So here is my code:

if(isset($_POST['submit']))
    {
    if(isset($_POST['offdays']))
        {
        //$off is set through the config settings//
        $shift = mysqli_real_escape_string($_POST['shift']);
        $from = mysqli_real_escape_string($_POST['from']);
        $to = mysqli_real_escape_string($_POST['to']);
        $emp_id = mysqli_real_escape_string($_POST['emp_id']);

        foreach($_POST['offdays'] as $checkbox)
            {
            echo "Checkbox: " . $checkbox . "<br>";//error checking
            $sql = ("SELECT Date FROM schedule WHERE (Date BETWEEN '$from' AND '$to') AND (Emp_ID = '$emp_id')");

            if(!$result_date_query = $mysqli->query($sql))
                {
                echo "INSERT ERROR 1 HERE";
                }
                echo "SELECT SQL: " . $sql . "<br>";//error checking

                while($row = $result_date_query->fetch_assoc())
                    {
                    echo "Date: " . $row['Date'] . "<br>";//error checking
                    $date = date('D',strtotime($row['Date']));

                    if($date == $checkbox)
                        {
                        echo "MATCHED! Date: " . $date . " Checkbox: " . $checkbox . "<br>";//error checking
                        $sql = ("UPDATE schedule SET Shift_ID = '$off' WHERE Date = '" . $row['Date'] . "' AND Emp_ID = '$emp_id'");

                        if(!$result_update_offdays_query = $mysqli->query($sql))
                            {
                            echo "INSERT ERROR 2 HERE";
                            }
                            echo "UPDATE DAYS OFF SQL: " . $sql . "<br><br>";//error checking
                        }
                    else
                        {
                        echo "NOT MATCHED! Date: " . $date . " Checkbox: " . $checkbox . "<br>";//error checking
                        $sql = ("UPDATE schedule SET Shift_ID = '$shift' WHERE Date = '" . $row['Date'] . "' AND Emp_ID = '$emp_id'");

                        if(!$result_update_shift_query = $mysqli->query($sql))
                            {
                            echo "INSERT ERROR 3 HERE";
                            }
                            echo "UPDATE SHIFT SQL: " . $sql . "<br><br>";//error checking
                        }               
                    }
                }       
            }
        }

When I look at my printed echo statements, everything is perfect! When a date lands on a Friday, it shows that it's entering Shift_ID = "6" and any other day shows Shift_ID = "3". The problem is the tables don't reflect what my statements are telling me, they all just get updated to Shift_ID = "3".

In the end I want to be able to select more than one offdays, but when I select more than one, it overwrites my previous day during the next loop, which makes sense.

I've got no idea what is going on, if anyone can give me a clue, I would really appreciate it.

UPDATE: When I add exit; after the days off update, like this:

echo "UPDATE DAYS OFF SQL: " . $sql . "<br><br>";//error
exit;

it does update the day off to Shift_ID = "6", so it must be something happening after that.

EDIT: It appears as though the problem was with user permissions. I can't explain why, but after deleting the user and creating a new one with full permissions, things started to work. I chose @andho's answer as he helped me get to the answer in the chat forum and also added a way to clean up my code.

For your own sake, you really should escape the $shift, $to,$from and $emp_id entries $emp_id = mysqli_real_escape_string($_POST['emp_id']); — Lucas
– Lucas, Commented Nov 28, 2012 at 12:39
14 hours without an answer, I am completely stumped and obviously everyone else is too! — Andrew Fox
– Andrew Fox, Commented Nov 29, 2012 at 2:46

andho · Accepted Answer · 2012-11-29 06:18:38Z

1

This doesn't solve your issue, but simplifies the solution!

if your offdays variable is as follows: $offdays = array('Fri', 'Sun');

You can first set all dates in range to $shift in one query:

UPDATE Schedule SET Shift_ID = '$shift' WHERE Date BETWEEN '$from' AND '$to' AND Emp_ID = '$emp_id'

Then you can loop through the foreach ($offdays as $offday) { and update those offdays:

UPDATE Schedule SET Shift_ID = '$off' WHERE Date BETWEEN '$from' AND '$to' AND Emp_ID = '$emp_id' AND DATE_FORMAT(Date, '%a') = '$offday'

That should update all the $offday's in the range to $off.

This will reduce your loops and queries, which gives leaner code.

answered Nov 29, 2012 at 6:18

andho

1,1611 gold badge15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Fox Over a year ago

Serious respect for all the help you gave me in chat, I really appreciate it!

Amit Garg · Accepted Answer · 2012-11-28 12:33:47Z

0

 $sql = ("SELECT `Date` FROM schedule WHERE (`Date` BETWEEN '$from' AND '$to') AND (Emp_ID = '$emp_id')");

Use backtick(`) to all the fieldname date because it is reserved in mysql.

answered Nov 28, 2012 at 12:33

Amit Garg

3,9071 gold badge30 silver badges37 bronze badges

1 Comment

Andrew Fox Over a year ago

I'm not sure why my comment was deleted? Anyways, Thank you for the reply, I have added the backticks as you suggested. This doesn't solve the problem unfortunately.

Collectives™ on Stack Overflow

while loop inside foreach loop problems

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related