whats wrong with the code

[seanlyons]

the following is code written to add data from an on-line test into a user database.

The idea being if the name is not on a list, then a new row is inserted, if they exist already vut the score is better, the score is updated, if they exist already but the score is lower, it is not added.

The messages recieved reflect the correct changes to the database, but the data is never updated. The new user insert line is the only one that seems to work.

<?php 

$name = $HTTP_POST_VARS['name']; 
$score = $HTTP_POST_VARS['score']; 


$db = mysql_connect("sql-01.portlandpremium.co.uk", "*****", "*****"); 

$link = mysql_select_db("******",$db); 

$query = mysql_query("SELECT * FROM test WHERE username='$name'");
$row = mysql_fetch_array($query);

if (mysql_num_rows($query) == '0') {
   $query2 = mysql_query("INSERT INTO test (username, sci_score1) VALUES ('$name', '$score')");
} else {

   $query3 = mysql_query("SELECT * FROM test WHERE username='$name' AND sci_score1 < '$score'");
      if (mysql_num_rows($query3) == '0') {
         echo "your score is lower than your last attempt, so the score hasn't been recorded";
      } else {
         $query2 = mysql_query("UPDATE test SET username='$name', sci_score1='$score' WHERE id='" . $row["id"] . "'");
          echo "Your score was better than the last time you sat the test, well done. The new score has been recorded";
      }
}

?>
 

any ideas please?

[jpmoriarty]

i think the error is in here:

$query2 = mysql_query("UPDATE test SET username='$name', sci_score1='$score' WHERE id='" . $row["id"] . "'");

It's always a good idea to define the query before running it, and i think it's getting confused as you sqap between " marks and ' marks. Try the following instead of that line:

$sql = "UPDATE test SET username='$name', sci_score1='$score' WHERE (id='".$row['id'].")";
$query2 = mysql_query($sql);

[xblue]

as always: use mysql_error()

$query2 = "UPDATE test SET username='$name', sci_score1='$score' WHERE id='" . $row["id"] . "'";
mysql_query($query2) or die("Error: ".mysql_error()." in query: ".$query2;