SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating one field

    I'm trying to update a single field, but I'm not having much luck. I need it so that the field doesnt have to be the same one each time.

    I'll try and explain a bit better


    currently I'm sending a series of variables from flash -> PHP -> MySQL

    I have 2 rows with the following data

    Code:
    id    score0    score1    score2    score3    score4
    =======================================================
    1     20        80         70        30        10
    2     30        10         50        20        60
    what I want to be able to do is send the variables from flash so that the php script will update the relevant score on the relevant row

    at the minute I'm using this script

    PHP Code:
    <?php
       
    //Capture data from $_POST array
       
    $userScore0 $_POST['score0'];
       
    $userScore1 $_POST['score1'];
       
    $userScore2 $_POST['score2'];
       
    $userScore3 $_POST['score3'];
       
    $userScore4 $_POST['score4'];

       
    //Connection to database
       
    $connect mysql_connect("localhost""user""password");
       
    mysql_select_db ("database"$connect);
       
       
    //Perform the query
       
    mysql_query("INSERT INTO student (id, score0) VALUES (1, '$userScore0') ON DUPLICATE KEY UPDATE score0= '$userScore0'");
       
    mysql_query("INSERT INTO student (id, score1) VALUES (1, '$userScore1') ON DUPLICATE KEY UPDATE score1= '$userScore1'");
       
    mysql_query("INSERT INTO student (id, score2) VALUES (1, '$userScore2') ON DUPLICATE KEY UPDATE score2= '$userScore2'");
       
    mysql_query("INSERT INTO student (id, score3) VALUES (1, '$userScore3') ON DUPLICATE KEY UPDATE score3= '$userScore3'");
       
    mysql_query("INSERT INTO student (id, score4) VALUES (1, '$userScore4') ON DUPLICATE KEY UPDATE score4= '$userScore4'");
    ?>
    this works fine but I have to send the 5 variables for one row from flash every time the data gets updated.

    what I wanted to do was something like this

    PHP Code:
       $scoreNo$_POST['scoreNumber'];
       
    $idNo$_POST['idNumber']

    $query "UPDATE test SET scoreNumber= '$scoreNo' WHERE idNumber= '$idNo'";
    if(
    mysql_query($query)){
    echo 
    "updated";}
    else{
    echo 
    "fail";} 
    but there are 5 scores (0-4) how can I get PHP to know which one flash is taking about?

    the reason I want to know is that I need to scale this up over 200 different scores and I dont really want to have to have 200

    PHP Code:
    $userScore0 $_POST['score0']; 
    and

    PHP Code:
    mysql_query("INSERT INTO student (id, score0) VALUES (1, '$userScore0') ON DUPLICATE KEY UPDATE score0= '$userScore0'"); 
    I hope someone understands what I'm trying to say (I know what needs doing but trying to make it legible so someone else understands is another thing!! )

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    A different database structure would make this so, so much easier.
    Code:
    GameID  -  ScoreNo  -  Score
        1              1            20
        1              2            80
        1              3            70
        1              4            30
        1              5            10
        2              1            30
        2              2            10
        2              3            50
        2              4            20
        2              5            60
    Then just post 3 things to PHP - the GameID, the ScoreNumber and the Score.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you need a different/extra table, with only one score column, as well as an indicator column which says which "number" (0 through 4) the score represents

    thus, where you have 1 row now with 5 score columns, you will have 5 rows with 1 score column instead

    this is the only design that scales


    Edit: jake beat me to it -- good one, jake

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    5 whole minutes, Rudy - I think your new clock's a bit out of sync

    Good to know my DB theory is approved by the resident SQL god
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please -- SQL Consultant, full stop

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •