# Create loop to shorten this code up

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; }  - ## 1 Answer 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.

-
I edited this answer to create the statement only once. Prepare once, use over and over! –  Rob Apodaca Jan 27 '13 at 1:03