Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dynamic Column Names

Posted on 2013-12-03
6
Medium Priority
?
255 Views
Last Modified: 2013-12-17
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);
	return $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),
			`qc_regionalrank` int(10),
			`qc_overallrank` int(10),
			 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());

Open in new window


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'];
			}
		}
	}

Open in new window

0
Comment
Question by:t3chguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 39693468
You can either run a DESCRIBE <table> query to get the table structure, or as long as you have a record, you can just look at the keys of any record that is returned with mysql_fetch_assoc().

By the way, mysql_ functions are deprecated, so you may want to consider moving to the newer mysqli functions.
0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 39693469
0
 
LVL 1

Author Comment

by:t3chguy
ID: 39693482
How can I use the key from the mysql_fetch_assoc?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

Accepted Solution

by:
gr8gonzo earned 2000 total points
ID: 39693541
Use array_keys:

http://us3.php.net/array_keys

if(mysql_num_rows($getQC) > 0)
		{
		while($row = mysql_fetch_assoc($getQC))
			{
			$repId = $row['respid'];
                        $columns = array_keys($row); // <<<<<<<<<<<<<<<<<<<<<<
			}
		}
	}

Open in new window

0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 39693707
Try mysql_list_fields() to get field names

http://us2.php.net/manual/en/function.mysql-list-fields.php
(deprecated but still works beautifully)


 or mysql_query() to issue an SQL SHOW COLUMNS FROM table [LIKE 'name']

http://dev.mysql.com/doc/refman/5.0/en/show-columns.html
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39693720
You probably want to define DATE columns as data type DATE.  Then you can use the standard ways of getting the data.  There is a little more about how to handle DATE and DATETIME information in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

That said, I would expect that it might make sense to have the date values in a single column in each of the rows instead of in columns for each date.  One example of why that might be an advantageous design would emerge if the boss comes in and says, "Give me the last two years data."  If the dates are in one column you just change the query.  If the dates are in columns by month, you have to rewrite your script.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question