Reserving a tally - SELECT and UPDATE in one query

[laPistola]

PHP 5.3

MySQL 5.1

 

Hello

 

Im building some software for a training company and they require that there students upload evidence documents. Each document needs an id number starting from 1. Each student needs to start from 1. I have one table that holds all the data for the evidence so using a auto increment in the DB column is not suitable. In the students table there is a column "file_tally" that starts from 1 and increases every time they submit evidence. This tally number is stored in the evidence row also.

 

This is the code:

// find tally value
		 mysql_select_db($dbn,$db);
		 $tsql = "SELECT file_tally FROM students WHERE sid = ".$_SESSION['student_id'];
		 $tquery = mysql_query($tsql,$db) or die(mysql_error());
		 $tally = mysql_result($tquery,0);
		 intval($tally)++;
		 // update tally
		 $utsql = "UPDATE students SET file_tally = '".$tally."' WHERE sid = ".$_SESSION['student_id'];
		 if(isset($_SESSION['student_id']) && !empty($_SESSION['student_id'])) {
			 mysql_query($utsql,$db) or die(mysql_error());
		 } 
		 // insert record
		 $sql = "INSERT INTO evidence (`file`,sid,tally) VALUES ('".$name."','".$_SESSION['student_id']."','".$tally."')";
		 mysql_query($sql, $db) or die(mysql_error());	

 

Now it works fine but it had occurred to me that if a staff member was uploading a document for the student at the every same split second (unlikely I know) they may both then get the same tally value and two documents get inserted into the DB with the same tally number.

 

Is there a way we can write the first command

 

$tsql = "SELECT file_tally FROM students WHERE sid = ".$_SESSION['student_id'];

 

In a way where it increases the tally value in the command string?

This could then keep that value in reserve and if there is an error I could adbopt the script to then "give" that value back by decreasing the value in the students file_tally.

[gristoi]

you could have a look at using a mysql trigger. These basically listen for an event that you want and perform an action when needed. For example it will listen for an insert into table A and will update table B accordingly:

 

• user uploads file and record is inserted into evidence
  
• trigger sees insert and incriments that users tally
  

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

 

 

[laPistola]

Thanks. Never played with triggers before.