Submit button to update MySQL DB

[webguync]

I am pretty sure this can be done, but not sure how to go about doing it.

 

 

I need to create a button that will update some information in a MySQL table. The button needs to reset an individuals ID so the SQL would be:

 

UPDATE `TableName` SET `name` = 'Joe Smith',

`username` = 'joe.smith@email.com',

`pwid` = 'JS101',

`user_id` = '562' WHERE `user_id` =561 LIMIT 1 ;

 

 

this is for on online exam and once the user takes the test the first time, they are locked out unless the user_id field is updated, which is the need for this type of button. It also needs to be done in Flash, and I know this isn't a flash forum, but help with the PHP part is what I am looking for.

 

[gevans]

Have you written any code yet? html, php or actionscript?

[haku]

You can go about this a better way. In your DB table, create a column called 'test_taken', and make it tinyint. Set it to be zero by default. After they have taken the test, change it to one. When they go to take the test, check to see if they variable is zero or one. If it's one, don't let them take the test.

[webguync]

I think I have what I need for the AS or at least I can post issues with that on another forum. What am needing is a button that will change the DB info so that I don't have to go in manually and reset. The creation of the column for test taken will be better for manually updating, but Ideally I want a button that the user can click that will enable them to retake the test.

[Maq]

haku is right, you should have some sort of flag to tell whether that id has taken the test instead of changing their userid, cause then you can't keep track of them.

 

You mean something like this:

 

if(isset($_POST['update'])) {
  $sql = "UPDATE `TableName` SET `name` = 'Joe Smith',
         `username` = 'joe.smith@email.com',
 `pwid` = 'JS101',
 `user_id` = '562' WHERE `user_id` = 561 LIMIT 1";
  mysql_query($sql) or die(mysql_error());
  echo "Succesfully updated!";
}
?>



</pre>
<form method="POST" action="<?php%20echo%20%24_SERVER%5B'PHP_SELF'%5D;%20?>">

</form>
<b

 

I don't know how you're getting the userid but this is how you would go about creating a button that would execute the query when it is pressed.

[haku]

I'm not sure that you understand what is going on behind the scenes when a button is pushed on web pages. What happens is the button sends the information to a script. Now the information can be as simple as just the fact that the button has been pushed, or it can be insanely complex.

 

The script then takes the information, and does something with it - in the case of a database, it changes the information in the database.

 

The info I gave you above had nothing to do with the script, it was database theory - the way to structure your database. When I said, for example, 'change it to one', I didn't mean manually, I meant 'get your script to change it to one'.

 

Trust me when I tell you that they way I told you to structure your database is definitely the way you want to go. What comes after is writing scripts that will affect that column in the database that I told you to create.

[webguync]

ok, I see what you guys are saying about the DB, thanks for the pointer.

[Maq]

Use both of our suggestions.  Have a submit button to update the DB but you should have an extra field like "taken_exam".  1 means the user has taken the exam and 0 means they have not.

 

I'm not sure where you get the userid from, but assuming it's in the session:

 

 

if(isset($_POST['update'])) {
  //don't need LIMIT 1 as the ID should be unique (auto-incremented)
  $sql = "UPDATE taken_exam = 0 WHERE user_id = '{$_SESSION['userid']}'";
  mysql_query($sql) or die(mysql_error());
  echo $_SESSION['userid'] . " is able to retake the exam!";
}
?>



</pre>
<form method="POST" action="<?php%20echo%20%24_SERVER%5B'PHP_SELF'%5D;%20?>">

</form>
<br><br><br><b

[webguync]

actually it's a text field in flash. They enter their name and user prior to taking the test.

[Maq]

okay... so instead of using SESSION use $_POST['user_field'];

[webguync]

correction, user ID is only in MySQL and is populated when there is a data dump of participants.

[Maq]

They enter their name and user prior to taking the test.

 

I assume username is unique...  so use that since you have it.

 

  //clean it first, may want to use additional cleaning functions.
  $user_name = mysql_real_escape_string($_POST['userid']);
  $sql = "UPDATE taken_exam = 0 WHERE username = '$user_name'";

[webguync]

back to this again...

 

I am still trying to determine how to determine how to allow access to take the exam again when the field taken_exam is set to zero and to update to 1 after the exam has been taken.

 

The code right now to determine if the exam should be disallowed or allowed is:

 

<?php
//include Database Class
require_once('databaseClass.php');

$username = $_POST['username'];
$pw = $_POST['pword'];


$sql = "SELECT user_id FROM roster WHERE username='$username' AND pwid='$pw'";

$result = $db->query($sql);
$result->get_rows();
if ($result->num_rows) {
$row = $result->fetch_assoc();
$userID = $row['user_id'];

$checkLog = "SELECT log_id FROM btval_log WHERE user_id=$userID";

$logHit = $db->query($checkLog);
$logHit->get_rows();
if($logHit->num_rows) {
	$logrow = $logHit->fetch_assoc();
	$logID = $logrow['log_id'];

	$checkScores = "SELECT results_id FROM btval_results WHERE user_id=$userID AND log_id=$logID";

	$scores = $db->query($checkScores);
	$scores->get_rows();
	if($scores->num_rows) {
		echo ('access=deny&record=true&uid='.$userID);
		}else {
		echo ('access=deny&record=none&uid='.$userID.'&lid='.$logID);
		}
	}else {
	echo ('access=accept&uid='.$userID);
	}
}else {
echo ('access=invalid');
}

?>

 

this is determining if there is already and entry and if so denying access. I need to re-allow access if this one field is set to zero. What is the best way to do this?