column name LIKE??

[willwill100]

i have a table which is structured as follows:

[img src=\"http://i7.photobucket.com/albums/y254/willwill100/structure.gif\" border=\"0\" alt=\"IPB Image\" /]

the thing is that users will create new race columns; basically I can have unlimited number of race columns.

these 'race columns' will have positions in them, i want the database to lookup all the 'race columns' add all their values together and them insert that value into score.

is there a way of selecting all the column names using the LIKE function or something like that? or is this impossible?

Thanks in advance

[Barand]

DON'T extend your data by adding columns to table. Have another table to hold the race data.

I'm guessing "sail_number" is the unique identifier so you tables would be

entrant
[code]Name  |  sail_number |  classs   |  score   |[/code]

and

race_result
[code]sail_number | race   |   position   |[/code]


Now you simply join to get the get the scores

[code]SELECT e.Name, SUM(r.position) as score
FROM entrant e INNER JOIN race_result r ON e.sail_number = r.sail_number
GROUP BY e.Name[/code]

[willwill100]

sorry, i dont have time to restructure my table. what you suggested would mean that I could not display individual results which I dont want to do. There are going to be several number of race columns eventually.
In other words i am going to have unlimited number of "race_result"s and so you code does not account for that. I have several tables with the same structure as the one in my first post but they are for separate competitions.
Your code would work fine if I only had one compeition and one result, unfortunately I have an unlimited number of each....

Is there a way that i can select all columns except:

"Name | sail_number | classs | score "

and then add the data in them and then insert that value into 'score'

I cannot consider a restructure at this point, can anyone hink of a workaround.

[willwill100]

can no one help me?

[redbullmarky]

[!--quoteo(post=357674:date=Mar 23 2006, 05:17 PM:name=WillWill)--][div class=\'quotetop\']QUOTE(WillWill @ Mar 23 2006, 05:17 PM) [snapback]357674[/snapback][/div][div class=\'quotemain\'][!--quotec--]
can no one help me?
[/quote]

barand is pretty much spot on with his suggestion. in the time that's gone by since he first suggested it, you could have done it already a few times over.

[willwill100]

okay ill give it a go

[keeB]

[!--quoteo(post=357681:date=Mar 23 2006, 05:35 PM:name=WillWill)--][div class=\'quotetop\']QUOTE(WillWill @ Mar 23 2006, 05:35 PM) [snapback]357681[/snapback][/div][div class=\'quotemain\'][!--quotec--]
okay ill give it a go
[/quote]

Yeah, look into constructing a relational database. The key is to have 'unique' fields in tables that correlate so that you can join without unwanted data.

For you, it seems like you're going to need at least 4 tables.. 1) competitions, 2) competitionEntrants 2) races, 3) entrants

As an example.. entrants table will have an entrantid being auto_increment and not null. Then you'll have competitions, competitionID which is auto and not null as well.. this will hold all of your competitions. competitionEntrants would hold an entrantID, competitionID, etc.. do you see where we're headed with this?

Small amount of planning will go a long way :)

GOOD LUCK!

[willwill100]

this way has actually worked well, thanx for the help.

more help needed tho...

[code]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''topper main points' e INNER JOIN topper main points Results r ON e.Sail Number ' at line 1[/code]

with the code:

[code]$sql = "SELECT e.Name, SUM(r.Position) as Score FROM '$compl' e INNER JOIN $compres r ON e.Sail Number = r.Sail Number GROUP BY e.Name";[/code]

could you explain what the code does?

does it take all the results from a certain sail number, add them together and then shuv them into score in the main table?(coz thats what i want to happen,lol)

thanx again guys

[Barand]

[!--quoteo(post=357729:date=Mar 23 2006, 07:01 PM:name=WillWill)--][div class=\'quotetop\']QUOTE(WillWill @ Mar 23 2006, 07:01 PM) [snapback]357729[/snapback][/div][div class=\'quotemain\'][!--quotec--]
with the code:

[code]$sql = "SELECT e.Name, SUM(r.Position) as Score FROM '$compl' e INNER JOIN $compres r ON e.Sail Number = r.Sail Number GROUP BY e.Name";[/code]

could you explain what the code does?

does it take all the results from a certain sail number, add them together and then shuv them into score in the main table?(coz thats what i want to happen,lol)

thanx again guys
[/quote]

It will total all the race results for a sail_number but it doesn't update the score in the table, That would be unnecessary as you can just recalculate with above query. If you store it in the DB it becomes out of date every race, so why bother?

If you want a list with highest scores first,

[code]$sql = "SELECT e.Name, SUM(r.Position) as Score FROM '$compl' e INNER JOIN $compres r ON e.Sail Number = r.Sail Number GROUP BY e.Name ORDER BY Score DESC";[/code]