Columns as Variable

tjbarr

Hi,
I am querying from a database in which the years are the columns. I have given the user the option of picking whatever years that they want to see and then sending them out to the page.

e.g. column1=city column2=state column3=1978 column4=1979 column5=1980

The user picked Cleveland OH 1979.

so the user choice city is "Cleveland" The user's choice state is "ohio" but how do I say that the user choice year is 1979 if 1979 is an actual column?

tjbarr

I can't transpose the columns/rows because then I would have too many columns.
Maybe I can store the year choices in an array and match to the column names in the database table. Not sure of the syntax.

Sxooter

You should probably rethink this, to something like having a single column that holds the year. Using a column for each year is going to make things more difficult as you go along.

tjbarr

Thanks, This is how the data comes to me but I know you are right. I just hated to transpose the table cause I don't want to mess up the tables.

Sxooter

But you'd be fixing the data were you to put it into a proper relational format.

Any chance of teaching the people providing the data how to put it into a better format, or will you always be stuck with it as it is?

Roger_Ramjet

Just for future reference, not in this case:

To use column or table names dynamically in a query you just insert a var name into the query string in the same way you would for a value

$col = $_POST['var1'];
$tbl = $_POST['var2'];
$sql = "SELECT id, $col FROM $tbl";

tjbarr

Thanks,
My problem is I am sent these tables in this format and they won't change the format. Also with just the major and cnty fields with the 35 years that I have - on a query on all the data it could get up to 3000 counties 400 majors 35 years or 42 million records

tjbarr

I am trying this but it doesn't work

$cnty_ch=$GET['cbocnty'];
$yr_ch=$GET['cboyear'];
$col = $yr_ch;

          $query="select ecnty, major, $col FROM emp_major WHERE ecnty=$cnty_ch;"; 	   
          $result_set = pg_Exec ($conn, $query);
    	  $rows = pg_NumRows($result_set);					
          for($i=0;$i<$rows;$i++){
		   		echo "<TD width=200><FONT size=2>" . pg_result($result_set,$i,"major") . "</FONT></TD>";  
		        echo "<TD width=200><FONT size=2>" . pg_result($result_set,$i,"ecnty") . "</FONT></TD>";    
				echo "<TD width=200><FONT size=2>" .pg_result($result_set,$i, $yr_ch) . "</Font></TD>"; 
	      }    
?>

tjbarr

Neither of my variables show up.

$cnty_ch or $col.

Sxooter

What does

print_r($_GET);

show you?

tjbarr

Array ( [cbocnty] => Array ( [0] => DE005 ) [cbovar] => 100 [count_st] => 1 [cbostate] => 10 [cboregion] => 1 [cbonaics] => 2 [cboyear] => _983A1
[submit] => Submit )

So it is getting the county which is in Delaware and its getting the variable 100 which is employment
its counting the number of states which is one
its showing the chosed state #10 - Delaware.
Its showing the region -1 which is county
Its showing the naics level which is 2
and its showing the year _983A1 which is 1983.

Not sure what the problem is. In the database the years are stored as 1983A1 and in my select boxes the hidden first column is 1983Al

tjbarr

I think I figured something out. If cbocnty is an array can I set it equal to $cnty_ch?

Roger_Ramjet

Yes, is the short answer.

tjbarr

What is the long answer?

tjbarr

Still having trouble with this: any suggestions?

Roger_Ramjet

tjbarr wrote:
What is the long answer?

$cnty_ch=$_GET['cbocnty'];
// now $cnty_ch is an array and you can
print_r($cnty_ch);
// which should yield  ..  Array ( [0] => DE005 )

tjbarr wrote:
Not sure what the problem is. In the database the years are stored as 1983A1 and in my select boxes the hidden first column is 1983Al

Now, you seem to have some problems with the form that submits all these values, so post the form code here please and I'll be able to understand what is going on a bit better. Bear in mind that the value returned from a select list is designated by

<select name="cboyear">
   <option value="_1983A1">1983</option>
   <option value="_1984A1">1984</option>
</select>

But in the meantime, to use the selected year as the column name

$year=$_GET['cboyear'];
$query = "SELECT " . $year . " FROM table WHERE ";