[SOLVED] Populating individual database rows.

[Siggles]

I am trying to use a SELECT statement within a while loop.

 

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

calc_score($resultus, $row['resultmfc'], $resultthem, $row['resultother']);

mysql_query("UPDATE predictions SET score = '$sum' WHERE id = '$gottenid'");

 

The calc_score function returns a different value each time from the array. I know this cos I can echo the results of the calc_score function and it works fine.

 

The SELECT statement populates the last ever result of the calc_score function in to wach row in the dbase where id =$gottenid. But I want it to update each ind row with the correct score. What am I doing wrong?

[GingerRobot]

1.) $gottenid looks like its undefined.

2.) you need to assign the returned value of the calc_score function to $sum, otherwise this is undefined too.

[Siggles]

1.) $gottenid looks like its undefined.

2.) you need to assign the returned value of the calc_score function to $sum, otherwise this is undefined too.

 

gottenid is defined. It will only populate the rows that match $gottenid (not the tens of others) but it populates it with the last loop of the function. Another way of putting it, if the results are 10, 25 and 30, it will probably populate all rows with the correct $gottenid with each of those numbers but eventually end up with 30.

 

while($row = mysql_fetch_array($test)){

$gottenid = $row['id'];

}

 

function calc_score($score_home, $prediction_home, $score_away, $prediction_away)

{

global $sum;

 

if ($score_home==$prediction_home && $score_away==$prediction_away)  //correctly predicted score - Rule 1

$sum+=25;

elseif ($score_home==$score_away && $prediction_home==$prediction_away)  //both draw so 10 points  - Rule 2

$sum+=10;

elseif ($score_home>$score_away && $prediction_home > $prediction_away)  //both win so 10 points - Rule 2

$sum+=10;

elseif ($score_home<$three && $prediction_home < $prediction_away)  //both lose so 10 points - Rule 2

$sum+=10;

 

echo "$sum";

echo "<br>";

}

[GingerRobot]

So $gottenid is defined inside a different while loop? If so, then that is why it is only even updating the last id. Once that loop has finished, $gottenid will obviously contain the last id.

 

Surely it should be defined in the loop that is doing the updating.

[Siggles]

I think we are heading off track a bit. The gottenid bit of the script works fine. If I run the script so that the UPDATES run, it will update the correct rows in the dbase who share the same $gottenid. $gottenid doesn't need to and shouldn't need to change.

 

What is wrong is that it updates the rows score field with the last result that the function ever carries out. I know the function works cos inside the function I have echo $sum and it will echo 10, 25, 10, 5, etc etc. I need these figures to be inputted in to each row in the dbase in the same order, I guess, that the function works. So if the function outputs 10 from using the first $row['resultmfc'] for example, I need it to update the row where that $row['resultmfc'] came from with the score 10, then when the function runs again and the score this time is 25 I need it to input that in to the row. A bit like a counter I guess, going down the rows of the dbase.

 

I know where I am going wrong. It is the way I have written but I cant figure out how to do it best...

 

UPDATE predictions SET score = '$sum' WHERE id = '$gottenid'

 

[blocker]

Have you tried to make the update a function?  For example...

 

I would write a function called "AddPoints($idnum, $points);"

 

The variables sent to the function ($idnum & $points) would be inherited by your current script.  Here's how I would do it:

function calc_score($score_home, $prediction_home, $score_away, $prediction_away, $idnum)

{

 

if ($score_home==$prediction_home && $score_away==$prediction_away)  //correctly predicted score - Rule 1

AddPoints($idnum, 25);

elseif ($score_home==$score_away && $prediction_home==$prediction_away)  //both draw so 10 points  - Rule 2

AddPoints($idnum, 10);

elseif ($score_home>$score_away && $prediction_home > $prediction_away)  //both win so 10 points - Rule 2

AddPoints($idnum, 10);

elseif ($score_home<$three && $prediction_home < $prediction_away)  //both lose so 10 points - Rule 2

AddPoints($idnum, 10);

 

}

 

$idnum would be the id in the database of the user (or row) you are updating the score for (assuming you do have a primary key!).

 

AddPoints($idnum, $points) would basically do this:

Add $points to $idnum's score

 

Depending on the DB you are using, you just need to write the query to do it.  Are you using a MySQL database?

 

[Siggles]

I managed to get it working. My problem was, the while loop and function produced the correct scores but there was no field on the rows to differentiate one from the other. So I created a primary key, predictionid and added this to the SELECT query and the UPDATE query. Now it works great 

 

$test = mysql_query("SELECT id FROM fixtures WHERE gamenumber = '$e'");

while($row = mysql_fetch_array($test)){

$gottenid = $row['id'];

}

echo "id is $gottenid  ";

 

$result = mysql_query("SELECT predictions.predictionid, predictions.resultmfc, predictions.resultother FROM predictions WHERE predictions.id ='$gottenid'");

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

calc_score($resultus, $row['resultmfc'], $resultthem, $row['resultother']);

$predict = $row['predictionid'];

echo $predict;

mysql_query("UPDATE predictions SET score = '$sum' WHERE predictionid = '$predict'");

$sum = 0;

}