Jump to content


Photo

column name LIKE??


  • Please log in to reply
8 replies to this topic

#1 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 23 March 2006 - 08:01 AM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 23 March 2006 - 08:25 AM

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
Name  |  sail_number |  classs   |  score   |

and

race_result
sail_number | race   |   position   |


Now you simply join to get the get the scores

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 23 March 2006 - 11:46 AM

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.

#4 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 23 March 2006 - 05:17 PM

can no one help me?

#5 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 23 March 2006 - 05:24 PM

[!--quoteo(post=357674:date=Mar 23 2006, 05:17 PM:name=WillWill)--][div class=\'quotetop\']QUOTE(WillWill @ Mar 23 2006, 05:17 PM) View Post[/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.

"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#6 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 23 March 2006 - 05:35 PM

okay ill give it a go

#7 keeB

keeB
  • Staff Alumni
  • Advanced Member
  • 1,078 posts
  • LocationCalifornia

Posted 23 March 2006 - 06:06 PM

[!--quoteo(post=357681:date=Mar 23 2006, 05:35 PM:name=WillWill)--][div class=\'quotetop\']QUOTE(WillWill @ Mar 23 2006, 05:35 PM) View Post[/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!


Come visit my site to see my latest projects
http://nick.stinemates.org/wordpress/


#8 willwill100

willwill100
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 23 March 2006 - 07:01 PM

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

more help needed tho...

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

with the 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";

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

#9 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 23 March 2006 - 08:58 PM

[!--quoteo(post=357729:date=Mar 23 2006, 07:01 PM:name=WillWill)--][div class=\'quotetop\']QUOTE(WillWill @ Mar 23 2006, 07:01 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
with the 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";

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,

$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";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users