update not insert...sometimes

seanlyons

Thanks to all the help from people at this site, I have finally cracked my problem...or nearly

The following code takes data from a form, and transfers it to a mysql database

<?php

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

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

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

$sql = "INSERT INTO test (name, score) VALUES ('$name', '$score')";

$result = mysql_query($sql) or die(mysql_error());

Brilliant, heres the problem, the database is recording the scores from a test. If the person has already sat the test, I want their score to update, not to create a new record, but if they have not sat the test, I would like a new record to be created......

Is this to much for my limited skills

Look forward to hearing from someone you lovely people.

Sean

cgraz

How would you determine whether they have already taken the test? Is it just if their name is already in the database?

Cgraz

seanlyons

their name is a unique username, and this is pretty low security stuff

cgraz

Then do a query to see if they're in there. If they are, update, if not, insert.

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

if (mysql_num_rows($query) == '0') {
   $query2 = mysql_query("INSERT INTO test (name, score) VALUES ('$name', '$score')");
} else {
   // this is assuming you have an id field
   $query2 = mysql_query("UPDATE test SET name='$name', score='$score' WHERE id='" . $row["id"] . "'");
}

Cgraz

seanlyons

thats perfect... works great

Just one more question, if I was trying to get only the highest mark achieved, is their any way to update the score field only if the new value for the score is greater than the existing score value?

Thanks for your help so far

Sean.

cgraz

Sure. Try this

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

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

   $query3 = mysql_query("SELECT * FROM test WHERE name='$name' AND score < '$score'");
      if (mysql_num_rows($query3) == '0') {
         echo "your score is lower than before. nothing was updated";
      } else {
         $query2 = mysql_query("UPDATE test SET name='$name', score='$score' WHERE id='" . $row["id"] . "'");
          echo "score updated";
      }
}

Cgraz

seanlyons

Thank you very much, your help is really appreciated. I think I am beginning to get the hang of this.

I'm a high school teacher in New Zealand, and this is being done for my students next year, even if they don't know it, thay are all very grateful to you too!

Sean

cgraz

no problem. Good luck with your project.

Cgraz