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

    Join Date
    Oct 2000
    Posts
    43
    Rep Power
    14
    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?

    I was goint to Try using php and start by reading each choice in the Choices table, then holding the record and looping through each record in the top10 and adding points if they match. (Can you do this?) My design is below but it doesn't work:

    $result=mysql_db_query($database,"select choice from choices;");

    while ($myrow = mysql_fetch_array($result)) {

    $choice=$myrow["choice"];
    $result2=mysql_db_query($database,"select team1 from top10;");

    while ($myrow2 = mysql_fetch_array($result2)) {

    $number1=$myrow2["team`1"];
    if ($choice==$number1) {
    $points = $points+10;
    }
    }
    }

    I am trying to have 2 simultaneous mysql connections and holding one while looping the other. I would repeat what I am trying to do above for the positions 2-10.

    Thanks for the help.
  2. #2
  3. No Profile Picture
    Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2000
    Posts
    6
    Rep Power
    0
    Almost right

    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">code:</font><HR><pre>
    $result1=mysql_db_query($connection1,"select choice from choices;");

    while ($myrow = mysql_fetch_array($result)){
    $choice=$myrow["choice"];
    $result2=mysql_db_query($connection2,"select team1 from top10;");
    while ($myrow2 = mysql_fetch_array($result2)) {
    $number1=$myrow2["team`1"];
    if ($choice==$number1) $points = $points+10;
    }
    }
    [/code]

    it's seems that the record isn't loaded at once, but on the fly, so
    the connection is needed.
    Thats why 2 database connections, ..
    it should work this way

    ------------------
    Greetings lewi
  4. #3
  5. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15

    <<
    $number1=$myrow2["team`1"];
    >>

    what is that "team`1".just change that to "team1"

    $number1=$myrow2["team1"];



    ------------------
    SR -
    webshiju.com
    www.jobxyz.com-IT Career Portal
    ezipindia.com--WebStudio


    "The fear of the LORD is the beginning of knowledge..."

    [This message has been edited by Shiju Rajan (edited October 23, 2000).]

Similar Threads

  1. Replies: 3
    Last Post: February 11th, 2004, 08:30 AM
  2. MySQL output to table using phpBB
    By CCCMTech in forum PHP Development
    Replies: 0
    Last Post: February 2nd, 2004, 02:43 PM
  3. Replies: 1
    Last Post: January 30th, 2004, 06:04 PM
  4. how to keep table on top, not centered??
    By vinyl in forum HTML Programming
    Replies: 2
    Last Post: January 20th, 2004, 06:19 PM
  5. Scratch table error in MySQL
    By byte me in forum MySQL Help
    Replies: 11
    Last Post: January 20th, 2004, 11:51 AM

IMN logo majestic logo threadwatch logo seochat tools logo