Updating mysql table

[Franky]

I want to use a query that will update a table if a specific entry exists, or inserts the data if it doesn't. The code below shows what i have so far (taken from a previous post and modified)

[PHP]
$result = mysql_query("UPDATE answersmod".$ModNum." SET answer='$A1' WHERE user_id='$lg_name' AND Q_Num = '$Q_Num'");
if (mysql_affected_rows($result) == 0)
{
// No records updated, so add it
$result = mysql_query("INSERT INTO answersmod".$ModNum." (User_ID, Q_Num, Answer) VALUES ('$lg_name', '$Q_Num', '$A1')");
if (mysql_affected_rows($result) == 0)
{
echo "No insert or update performed";
} else { echo "All done";}// end if
}//end if
[/PHP]

It does not update or insert. instead i get the following:

Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in C:\Program Files\xampp\htdocs\Lilly\PageFiles\ModDisplayQuestions.php on line 62
Warning: mysql_affected_rows(): supplied argument is not a valid MySQL-Link resource in C:\Program Files\xampp\htdocs\Lilly\PageFiles\ModDisplayQuestions.php on line 67
No insert or update performed
 

previous posts have said that this is often caused by an incorrect connection, but my connection to the db works fine on all other pages (it is in a seperate module so doesn't change).

any ideas? I have to have this completed by tuesday afternoon so a speedy reply would be much appreciated!

thanks in advance for any help

[Franky]

I have changed the code slightly and have a different issue:

[PHP]
function displayQuestions($ModNum){
global $result, $query, $a;

//If submit is not set do this, else do next (process the form)
if (!isset($_POST['Submit']))
{
dbConnect();

$query = "SELECT * FROM questionsmod".$ModNum." ORDER BY RAND() LIMIT 5";
$result = mysql_query($query); ?>

<p>Below is a set of questions reviewing the content covered in this module. Answer all questions and click the 'Submit' button at the bottom of the page. You will then get your results.</p>
<TABLE>
<FORM method="post" name="Answers" action="<?php echo $PHP_SELF; ?>">

<?php
$a=1;
while ($row = mysql_fetch_assoc($result))
{ ?>
<TABLE>
<TR><TD><b><? echo "$a. ", $row ['Question']. "<br/>" ?></b></TD></TR>
<TR><TD><input type="radio" name="<?php print 'Question'.$a ?>" value="1"> <? print $row ['Opt_1']. "<br/>" ?></TD></TR>
<TR><TD><input type="radio" name="<?php print 'Question'.$a ?>" value="2"> <? print $row ['Opt_2']. "<br/>" ?></TD></TR>
<TR><TD><input type="radio" name="<?php print 'Question'.$a ?>" value="3"> <? print $row ['Opt_3']. "<br/>" ?></TD></TR>
<TR><TD><input type="radio" name="<?php print 'Question'.$a ?>" value="4"> <? print $row ['Opt_4']. "<br/>" ?></TD></TR>
</TABLE>
<?php
print "<br/>";
$a++;
}//end while
?>

<TR><TD><input type="submit" name="Submit" value="Submit"></TD></TR>
</FORM>
</TABLE>
<?php
} else {
//Call to ModDBQueries to connect to the database
dbConnect();

$lg_name = $_SESSION['author'];

$i=1;
while ($i <= 5)
{
$Answer = $_POST['Question'.$i];
echo "Answer ".$i." = ".$Answer."<br>";

//Inserting answer into database
$result = mysql_query("INSERT INTO answersmod".$ModNum." (User_ID, Q_Num, Answer) VALUES ('$lg_name', 'Question', '$Answer')");
$i++;
}
}//end if
}//end function "displayQuestions"
[/PHP]


The first section shows the 5 questions and options selected from the db. The SELECT * part gets this info, including the question number of the randomly selected questions.

The second part displays the users answer for each question and (in theory) updates the relevant database table with their answer. However, to do this successfully, as i loop through their answers i need the question number (i.e. the one from the questions table in the db, not the one on the page, i.e. 1,2,3,4 or 5).

Any way i can get this from the array output going on the in the loop in the first section? Can i append a variable name with the incrementing $a and store it in that (i.e. just above - print "<br/>"; $a++; - have $Num,$a = $row['Q_Num']; where $Num,$a would be Num1, Num2, etc)?

Or is there a better way to display and process the form (including updating the db)?