Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I am new to programming as you can probably tell from the code below I am curious if there is a way to create a loop so I don't have so many copy's of the same code with only different variable names. The way the code works is that for each variable that is represented as a day of the week the value is a string such as 01/20/13 I obtain these values from another piece of code so I don't have to hard code the date values in each time. I have included this code snippet as well. I have 4 places in my code that there is a similar approach used here where I have 7 separate code blocks where only a few variable names are changed. I also don't know if my method of repetitive MySQL query's are hurting performance at all or if there is a more efficient method to do this in one query instead of 7.

This is the repetitive code:

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                            AS `totalSunday` FROM `timeRecords` WHERE   `date` = :Sunday
                            AND `employeeID` = 1 GROUP BY `employeeID`;');
    $stmt->execute(array(':Sunday'=>$Sunday));
    $row = $stmt->fetch();
    $data['TotalSunday'] = $row['totalSunday'];

    $stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                            AS `totalMonday` FROM `timeRecords` WHERE   `date` = :Monday
                            AND `employeeID` = 1 GROUP BY `employeeID`;');
    $stmt->execute(array(':Monday'=>$Monday));
    $row = $stmt->fetch();
    $data['TotalMonday'] = $row['totalMonday'];

    $stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                            AS `totalTuesday` FROM `timeRecords` WHERE   `date` = :Tuesday
                            AND `employeeID` = 1 GROUP BY `employeeID`;');
    $stmt->execute(array(':Tuesday'=>$Tuesday));
    $row = $stmt->fetch();
    $data['TotalTuesday'] = $row['totalTuesday'];

    $stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                            AS `totalWednesday` FROM `timeRecords` WHERE   `date` = :Wednesday
                            AND `employeeID` = 1 GROUP BY `employeeID`;');
    $stmt->execute(array(':Wednesday'=>$Wednesday));
    $row = $stmt->fetch();
    $data['TotalWednesday'] = $row['totalWednesday'];

    $stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                            AS `totalThursday` FROM `timeRecords` WHERE   `date` = :Thursday
                            AND `employeeID` = 1 GROUP BY `employeeID`;');
    $stmt->execute(array(':Thursday'=>$Thursday));
    $row = $stmt->fetch();
    $data['TotalThursday'] = $row['totalThursday'];

    $stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                            AS `totalFriday` FROM `timeRecords` WHERE   `date` = :Friday
                            AND `employeeID` = 1 GROUP BY `employeeID`;');
    $stmt->execute(array(':Friday'=>$Friday));
    $row = $stmt->fetch();
    $data['TotalFriday'] = $row['totalFriday'];

    $stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                            AS `totalSaturday` FROM `timeRecords` WHERE   `date` = :Saturday
                            AND `employeeID` = 1 GROUP BY `employeeID`;');
    $stmt->execute(array(':Saturday'=>$Saturday));
    $row = $stmt->fetch();
    $data['TotalSaturday'] = $row['totalSaturday']  

Here is how I obtain the date string to be placed in the variables:

// set current date
$date7 = date("m/d/y");
// parse about any English textual datetime description into a Unix timestamp
$ts = strtotime($date7);
// calculate the number of days since Monday
$dow = date('w', $ts);
$offset = $dow;
if ($offset < 0) {
    $offset = 6;
}
// calculate timestamp for the Monday
$ts = $ts - $offset*86400;
// loop from Monday till Sunday
for ($i = 0; $i < 7; $i++, $ts += 86400){

    $date1 = date("m/d/y", $ts);
    $date3 = date("l", $ts);
    $date2 = $date1;
    $$date3 = $date1;
}
share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted

The first thing to do would be to change whatever can be changed in the code so that the behavior doesn't change but the code looks the same everywhere. Here's what I've done :

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                        AS `totalDay` FROM `timeRecords` WHERE   `date` = :myDay
                        AND `employeeID` = 1 GROUP BY `employeeID`;');
$stmt->execute(array(':myDay'=>$Sunday));
$row = $stmt->fetch();
$data['TotalSunday'] = $row['totalDay'];

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                        AS `totalDay` FROM `timeRecords` WHERE   `date` = :myDay
                        AND `employeeID` = 1 GROUP BY `employeeID`;');
$stmt->execute(array(':myDay'=>$Monday));
$row = $stmt->fetch();
$data['TotalMonday'] = $row['totalDay'];

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                        AS `totalDay` FROM `timeRecords` WHERE   `date` = :myDay
                        AND `employeeID` = 1 GROUP BY `employeeID`;');
$stmt->execute(array(':myDay'=>$Tuesday));
$row = $stmt->fetch();
$data['TotalTuesday'] = $row['totalDay'];

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                        AS `totalDay` FROM `timeRecords` WHERE   `date` = :myDay
                        AND `employeeID` = 1 GROUP BY `employeeID`;');
$stmt->execute(array(':myDay'=>$Wednesday));
$row = $stmt->fetch();
$data['TotalWednesday'] = $row['totalDay'];

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                        AS `totalThursday` FROM `timeRecords` WHERE   `date` = :myDay
                        AND `employeeID` = 1 GROUP BY `employeeID`;');
$stmt->execute(array(':myDay'=>$Thursday));
$row = $stmt->fetch();
$data['TotalThursday'] = $row['totalThursday'];

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                        AS `totalDay` FROM `timeRecords` WHERE   `date` = :myDay
                        AND `employeeID` = 1 GROUP BY `employeeID`;');
$stmt->execute(array(':myDay'=>$Friday));
$row = $stmt->fetch();
$data['TotalFriday'] = $row['totalDay'];

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                        AS `totalDay` FROM `timeRecords` WHERE   `date` = :myDay
                        AND `employeeID` = 1 GROUP BY `employeeID`;');
$stmt->execute(array(':myDay'=>$Saturday));
$row = $stmt->fetch();
$data['TotalSaturday'] = $row['totalDay']  

Then you can check what's the same and what's different in the different iterations.

The typical iterations looks like this :

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                        AS `totalDay` FROM `timeRecords` WHERE   `date` = :myDay
                        AND `employeeID` = 1 GROUP BY `employeeID`;');
$stmt->execute(array(':myDay'=>$THIS_PART_IS_DIFFERENT));
$row = $stmt->fetch();
$data['Total' . THIS_PART_IS_DIFFERENT] = $row['totalDay'];

Finally, you can make an array out of the different parts. Here, it's pretty simple : only 2 elements are changing so we can use a simple associative array.

The result would be something like

$day = arrays(
    'Sunday' => $Sunday,
    // etc
    'Monday' => $Monday,
);

foreach ($days as $name => $value)
{
    $stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                            AS `totalDay` FROM `timeRecords` WHERE   `date` = :myDay
                            AND `employeeID` = 1 GROUP BY `employeeID`;');
    $stmt->execute(array(':myDay'=>$value));
    $row = $stmt->fetch();
    $data['Total' . $name] = $row['totalDay'];
}

Because we associate each name to the value of the corresponding variable, please note that you could also use the double dollar sign to do something like :

$day = arrays(
    'Sunday',
    // etc
    'Monday',
);

$stmt = $conn->prepare('SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                            AS `totalDay` FROM `timeRecords` WHERE   `date` = :myDay
                            AND `employeeID` = 1 GROUP BY `employeeID`;');
foreach ($days as $name)
{
    $stmt->execute(array(':myDay'=>$$value));
    $row = $stmt->fetch();
    $data['Total' . $name] = $row['totalDay'];
}

The whole thing could probably be done in a clearer way if we also wanted to update the code you use to get the string in the different variables.

share|improve this answer
    
I edited this answer to create the statement only once. Prepare once, use over and over! –  Rob Apodaca Jan 27 '13 at 1:03
add comment
   //All the queries look same therefore you can make a function to prepare and 
    //return the result              

   var $day = 'Sunday'; //I guess the value of $day will be dynamic;

   var execution_array = array(
          ':day' => $day
    );

   var $query = 'SELECT `employeeID`, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) 
                    AS `totalDay` FROM `timeRecords` WHERE   `date` = :day
                    AND `employeeID` = 1 GROUP BY `employeeID`;' ;

    $row = execute_query($query,$execution_array);

    $data['Total' . $day] = $row['totalDay'];    

    //function to execute query with corresponding array 
    function execute_query($query,$execution_array){

            $stmt = $conn->prepare($query);
           $stmt->execute($execution_array);
           return $stmt->fetch();
    }
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.