Variable Name VS Table Column Name

[xProteuSx]

I am trying to write a function that I have never attempted before, but would save me TONS of code if I can get it to work.

 

I have a table with an expanding number of columns.  The data is annual, so the table column names go something like this:

data_2010, data_2011, data_2012, data_2013, etc.

With every coming year another column is added, obviously.

 

On the webpage that accesses the data, I have set up variables for each column.  However, I have to edit this code every time a new column is added.  I am trying to create variables based on column names.

 

Something like this:

 

-> get column names (done!)

-> add each column name to an array (done!)

-> create variable for each column in the array

 

Here is what I have:

 

function getValueColumns()
{
$res = mysql_query('DESCRIBE table_name');
$yearcol_array = Array();
while($row = mysql_fetch_array($res)) 
{
$outputrow = "{$row['Field']}";
if ($outputrow != 'val_id') //ignores the table primary key column
{
$year = substr($outputrow, -4);  //checks if the year is included in the table column name
if (is_numeric($year))  //if the last four digits of the table name are a number, as in a year ...
{array_push($yearcol_array,$outputrow);}
}
}
return $yearcol_array;
}

So now I have to write the code that will create the variables, and collect the data from those columns at a certain row.  Something like this:

function returnYearlyData()
{
$yearswithdata = Array();
foreach ($yearcol_array as $yearcol)
{
$select = "SELECT * FROM table_name WHERE val_id = x;";
$[column_name_here] = mysql_result(mysql_query($sql), 0, 0);
if ($[column_name_here] != '')
{
array_push($yearswithdata,$[column_name_here]);
}
return $yearsdiwthdata;
}

So now I would have an array with variables named after the table column IF the row and column specified in the select statement is not empty.

 

I am not sure whether I have explained this well, but I cannot figure this out myself.  I've always been curious as to whether you can append text to a variable name, for example, which is a related scenario.  Either way I would appreciate help.

 

Cheers guys and gals!

 

Oh, and happy new year!  TIA!

 

 

 

 

[mac_gyver]

this is a bad database design, that's treating the database table like it is a spreadsheet, resulting in a ton of code to manipulate or retrieve any piece of data, which is why you have to write two relatively slow operating functions just to retrieve any data. research 'database normalization' to find the correct way of storing data in a database table.

 

each piece of data should be stored in a separate row in your database table and there should only be a row when there is a piece of data. if there is a year, date, or datetime value associated with each piece of data, the database table would have a column to hold the year, date, or datetime value. to query for data for a specific year, you would just query to find the row(s) with the year value you are interested in (the mysql YEAR() function would be used to get the year portion of a date or datetime value.)

 

next, the mysql_ functions are obsolete and have been removed from the latest version of php. you should be using either the PDO or mysqli_ php api to access your database. the PDO api is more constant and easier to use.

[xProteuSx]

mac_gyver,

 

Thanks for your reply.  I am totally going to have to look into this PDO API stuff.  I am putting together some long term projects, and I definitely don't want to be updating all my MySQL queries in a year.

 

In the mean time, database normalization aside, can you help me build the function that I've outlined?

 

Cheers.

[mac_gyver]

i/we could, but the db server gods wouldn't be happy.

 

whatever you think you are gaining by storing your data this way, is lost by all the extra code it is taking to insert, update, delete, or find any of the piece(s) of data and all the time you have spent maintaining the database structure and code just because a new January 1st rolled around. a normalized design won't require any of this, because a new year is just a value changing in an existing column of data.

[xProteuSx]

You know, I've always been very grateful for the help that I've gotten on this website.

 

I've always looked into the different options and suggestions that I have received because I know that I am not an expert coder.  I've always come back here, and contributed whenever I could (not often because I am  not an expert coder).

 

However, the last 10 times of so I've kindly asked for help, I've come up against some prick such as yourself, how deems me too stupid to be worthy of help, or himself too awesome to help.  Its really sad to see the website going in that direction.

 

If you don't want to help, just stay out of it.

Edited January 2, 2016 by xProteuSx

[ginerjm]

Your attitude sets you up to be ignored by us and others who truly want to help you.

 

That said - You are being advised nicely that your database design is bad. It is not how dbs are meant to be done. There are whole sets of documents and rules on the subject of data normalization out there. This is not one person's pet theory. It is a long and durable method that has been in place for decades. You are just finding out about it and not realizing how far out of the loop you are here. There are steps involved in getting a normalized database that go into the 5th, 6th, and (maybe) even the 7th levels before the data is truly structured properly. Helping you "solve your problem" here would be an injustice to you and be bad advice that would live on forever in the internet archives and be the source of some other person's future path down the same rocky road.

 

Do yourself a favor and apologize to the forum and then do some research on "data normalization" and see what the story is.

 

And have a great new year!

[benanamen]

For gawd sake, why do you people continue to insist to do things the wrong way. You have a whole team with a combined hundreds of years of expertise at your fingertips telling you how things need to be done right and you still insist to do it the wrong way and then want to attack the people that are helping you for FREE! SHAME ON YOU!

Edited January 2, 2016 by benanamen

[Stooney]

While your last comment was quite rude, it looks like you're trying to find out how to name a variable based on another variable's value.

 

Here is the answer to that question:

http://php.net/manual/en/language.variables.variable.php

 

The fact still remains that your DB design is bad and should be reworked.  

[mac_gyver]

However, the last 10 times of so I've kindly asked for help ...

 

 

must have been some other site, because i have reviewed the most recent page of threads you have started here and the results don't support your claim.

 

i particularly reviewed a recent thread that i helped solve, where it turned out you were including files incorrectly. i also thoroughly read another thread where you were trying to roll your own base64 encryption/decryption and were insistent that someone not try to tell you to use a different offset with a string function, then a top forum member demonstrated that it was that string offset that was wrong.

 

that you would get bent out of shape over someone advising you that there is a better, shorter, faster, correct way of doing something, indicates you need to show some flexibility in your approach to asking others to help you with programming that you cannot do yourself. you basically didn't get someone to jump and convert your pseudo code into real code for you and you think the problem is somewhere than where it is really at.

 

the reason you may be only getting advice on what you should be doing, is because i/we/people in general would like to help you completely and correctly FIX a problem, not just patch it up so that it 'works', so that i/we/people in general don't have to see what you are doing keep showing up in forum threads when the next problem with it, just because of a bad design, needs solving.

[Jacques1]

While your last comment was quite rude, it looks like you're trying to find out how to name a variable based on another variable's value.

 

No, no, no.

 

Stuffing a sequence of values into dozens of separate variables like $data_2010, $data_2011, $data_2012, $data_2013, ... is yet another bad idea and will turn the already shaky codebase into a nightmare. Even a trivial task like iterating over the years now requires dynamic variable gymnastics, because the only data structure which actually knows all values is the internal PHP symbol table. Good luck using that for more complex tasks.

 

This is not helpful. Right now, the OP has one problem. With the function above, he has two problems and will be back in a week asking us to write a workaround for the workaround.

[benanamen]

Here is where we are going for a simple problem  Rube Goldberg anyone?