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 :confused:


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!! :lol:)

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.

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

:)

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 :)

please -- SQL Consultant, full stop

:cool: