building sql statements by user choices

tjbarr

I have a table that is structured like this

var_code fips_code pop1969 pop1970
1010 01000 111 222
1011 01000 22 11
1012 01000 23 25
1013 01000 .26 .22

I have a dropdown listbox in which the user selects years on a previous page. So assuming that selection is 1969 and is called $yr_ch ---
How on this page can I write my sql statement to pick only the values under 1969?

The table is too large to put all the years under one column called years.

I need to build an sql statement depending on the choices the user makes.

e.g. the user picks 1969
select from tbl_pop pop_1969;

horndog

give the drop-down box a name, and then it automatically gets a variable.

So if you name the drop down " drop"

the default variable is $drop

So now you can do a select statement from that:


$sql = "SELECT * FROM $table_name WHERE year = \"$drop\" ";

let me know if you need further help 🙂

tjbarr

How will the program know what year is? the columns are 1969, 1970, 1971 etc.

Rodders

The table is too large to put all the years under one column called years.

I don't understand this. By having data in your column names you are restricting the growth of your table. So by having columns called 1969, 1970 etc. you are going to struggle when it comes to 1985 or something you don't have a column for.

I would strongly advise improving your design by having a YEAR field which takes any value for year. You can make this an index so that your queries can quickly find the required rows of data.

Otherwise, just build your SQL in pieces to make it easier, then echo it out when testing to see if it's doing what's required.

$chosen_year = '1969';

$sql = "SELECT pop" . $chosen_year . " FROM .......";