I'm building a PHP script that will run monthly, to gather data from different tables and bring it into a new table.
As it stands right now, I am dropping the tables monthly, and re-creating them in this script. The column names consist of the month name and year, and therefore will change monthly.
For example, the table I'm working in right now has a column for november12, december12, january12, all the way through december13.
I need to extract the data from the columns in order to calculate averages, and more importantly, display the data on the page.
My question is how to structure my SELECT query based on these dynamic column names, and then how to put them into variables to display on the page in my WHILE loop?
Here is how I am naming the columns.
#Get Months between last month and 12 months ago
function getAllMonths($date1, $date2)
$time1 = strtotime($date1);
$time2 = strtotime($date2);
$my = date('mY', $time2);
$months = array(date('Y-m', $time1));
while($time1 < $time2)
$time1 = strtotime(date('Y-m-d', $time1).' +1 month');
if(date('mY', $time1) != $my && ($time1 < $time2))
$months = date('Y-m', $time1);
$months = date('Y-m', $time2);
$months = array_unique($months);
#Define Months to use
$thisMonth = date("Y-m", strtotime($today));
$yearAgo = date("Y-m", strtotime("-1 year", strtotime($thisMonth)));
$allMonths = getAllMonths($yearAgo, $thisMonth);
#Build the quality calls table!
$crt = "CREATE TABLE IF NOT EXISTS `repanalysis_qualitycalls`
`id` int(20) NOT NULL auto_increment,
`respid` int(6) NOT NULL,";
foreach($allMonths AS $monthRange)
$monthNames = date("Fy", strtotime($monthRange));
$crt .= '`'.$monthNames.'` decimal(7,2) default 0,';
$crt .= "`averageqc` decimal (15,2),
`avgtogoal` decimal (15,5),
PRIMARY KEY (`id`),
KEY `respid` (`respid`)
ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0";
$result = mysql_query($crt) or die("Cannot create quality calls table: " . mysql_error());
I tried doing a select * but I'm not sure how to get the column names out of that query:
//Extract Quality Calls for Calculations.
$getQC = mysql_query("SELECT * FROM repanalysis_qualitycalls") or die("Cannot pull quality calls: " . mysql_error());
if(mysql_num_rows($getQC) > 0)
while($row = mysql_fetch_assoc($getQC))
$repId = $row['respid'];