#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    43
    Rep Power
    15
    I just found out that MySql currently does not support updating one table based upon values in another table (this was hard for me to believe because I'm fairly new to this, and I just assumed this would be supported).

    So, now I have to get around it. i've seen references to being able to get aroung this problem but have never seen it explained.

    I have one table TOP10, that has people's votes for their top10 (login, number1, number2, number3, etc). The other table CHOICES has a list of the choices for the Top10. Basically, I want to come up with an aggregate top10 by assigning points to each of the choices in CHOICES Based upon the votes in TOP10. I was hoping to just do: update choices set choices.points=choices.point+10 where TOP10.number1=choices.choices; and then repeat this for numbers2-9. But I can't do this, so how do I get around it? Any suggestions would be appreciated? And if you have a generic solution to this problem please feel free to post that, as all I really need to do is get around the problem of not being able to update based upon values in two tables.

    Thank you.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    43
    Rep Power
    15
    one more thing. I'm using mysql 3.22.25 so I can't create temporary tables from select statements.
  4. #3
  5. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    This should be relatively simple but it would help to know how you have your form set up.

    Also, do you want to update the choices table whenever a vote is processed or periodically?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    43
    Rep Power
    15
    My form already is dynamic in the sense that the list where they vote for each of their top10 is pulled from the choices table. There is a way to enter a choice not listed but then it is added to the choice table. All the voter sees is a top10 tabe with drop down menus with a list of each of the choices.

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    This should be relatively simple but it would help to know how you have your form set up.

    Also, do you want to update the choices table whenever a vote is processed or periodically?
    [/quote]
  8. #5
  9. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Let me rephrase. How are the selections being passed? IOW, what variable name(s) are you using or are you using an array?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    43
    Rep Power
    15
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    Let me rephrase. How are the selections being passed? IOW, what variable name(s) are you using or are you using an array?[/quote]

    WHen they vote I am using an array. It's called team[0] to team[9]. I then take these values and put them into the database. The database has variables team1-team10. I think I see where you are headed to update the scores with each vote instead of doing it at the end like I am trying to do now.
  12. #7
  13. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You should set up your Top10 table like this:

    userid | teamid | rank

    This way each userid would have ten entries. Use an inverse for storing the rank (ie if the ranked a team #1 then give it a score of 10 in rank).

    Now, to get the ranks you can do this:

    select sum(rank) as total from top10 group by teamid;

    to store this in your choices table, assuming that the teamid column is unique, you can do this instead:

    replace into choices (teamid,score) select teamid,sum(rank) from top10 group by teamid;

  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    43
    Rep Power
    15
    Thanks!

Similar Threads

  1. Replies: 2
    Last Post: January 28th, 2004, 08:07 AM
  2. Inserting from form to multiple tables
    By barth in forum MySQL Help
    Replies: 0
    Last Post: January 25th, 2004, 10:27 AM
  3. Replies: 2
    Last Post: January 18th, 2004, 11:10 PM
  4. selecting distring records across multiple tables
    By kubicon in forum MySQL Help
    Replies: 3
    Last Post: January 9th, 2004, 06:41 PM
  5. selecting common cols in multiple tables
    By phprook in forum MySQL Help
    Replies: 8
    Last Post: December 10th, 2003, 02:48 PM

IMN logo majestic logo threadwatch logo seochat tools logo