[Resolved] select wildcard column names?

thebighere

Hey,

I'm all good with the Select * from table where blah LIKE '%blah%';

However, I can't seem to find how to select a partial column name.

I have a table where columns are named monthyearavgin; 122004avgin,112004avgin for example.

I would like to be able to call all column types with one phrase.

But this doesn't work:

Select '%avgin' from table where id = $id;

(or '%avgin%')

I couldn't find anything here or on mysql.com

Any thoughts ?

tia

csn

Can't be done, AFAIK.

YappyDog

Well, it sort of can. You'll need to get the list of column names from the table and then build your query after you step through them and pick out the ones that match your pattern.

thebighere

senor yap,

"
Well, it sort of can. You'll need to get the list of column names from the table and then build your query after you step through them and pick out the ones that match your pattern.
"

Do you mean I query for all column names, then query again with my known column names?

I may not be getting this, but I'm stuck in the same situation. I still need to list and know all the columns, correct?

Or am I missing this?

thanks

ahundiak

As YappyDog suggests, you can use mysql_list_fields() to get a list of columns in a given table and maybe use fnmatch()
to find the desired columns using wild cards. And then build your query.

But the real question is: why? Having lot's of similiarily named columns in a table usually indicates a design problem.

thebighere

As YappyDog suggests, you can use mysql_list_fields() to get a list of columns in a given table and maybe use fnmatch()
to find the desired columns using wild cards. And then build your query.

i'll try this, thanks

But the real question is: why? Having lot's of similiarily named columns in a table usually indicates a design problem.

well, i am new to this, and i'd be delighted with any assistance in design

here's the deal:

every month, i get statistics for usage -- with the same category names every month

i need to do graphs and reports with these numbers which compare month to month

i thought it would be easiest to do one table and call the different columns as necessary

this is problematic because i obviously don't want to add code to the query and reports every month, and i am not sure of the best way to do this, except to do a function so i only need to edit one place

any suggestions are greatly appreciated

thanks all

ahundiak

You want to make yourself a usage_statistics table with one record for each month. One column for the month/year and one column for the statistic.

thebighere

You want to make yourself a usage_statistics table with one record for each month. One column for the month/year and one column for the statistic.

maybe i am misunderstanding this, or maybe i didn't explain myself well enough

i have 300 records of stats for each month, each record has avgin,avgout,avgmax,95in,95out,95max

so (for example) a circuit from virginia to san jose has, for each month, avgin,avgout,avgmax,95in,95out,95max

but i have to track 300 different circuits

ahundiak

What does the 95 indicate? Do you track the same things for each circuit?

CREATE TABLE circuit
circuit_id
from
to

CREATE TABLE circuit_usage
circuit_id
month
avgin,avgout,avgmax,
95in,95out,95max

So for each circuit there would be one entry in the usage table for each month.

I don't think we are on the same track. In your original post you indicated that you would be duplicating the 6 attributes for each month. All I am suggesting is that you add month as it's own column. Then it's just a question of doing:
Select avgin from circuit_usage where circuit_id = $id;

thebighere

What does the 95 indicate? Do you track the same things for each circuit?

CREATE TABLE circuit
circuit_id
from
to

CREATE TABLE circuit_usage
circuit_id
month
avgin,avgout,avgmax,
95in,95out,95max

So for each circuit there would be one entry in the usage table for each month.

YOU ROCK, thank you

i don't know why I had such a hard time grasping this, but it's a great plan

to answer your questions, yes, same attributes for each, and the 95 indicates 95% usage

thanks again