Concurrent MySQL access from PHP

[ldd76]

Hi gurus,

I am quite new to MYSQL. I would like to allow concurrent access to my leave application.
Not sure how to the code is to be done but according to some MySql docs...is this correct?


$db = mysql_connect("localhost","root","root");

if(!$db){
print("Could not connect to database. Please try again later.");
exit;
}

mysql_select_db("lv_mgmt_sys");

$query = "SET AUTOCOMMIT = 0";

mysql_query($query);

$query = "START TRANSACTION";

mysql_query($query);

$query = "SELECT * FROM superv_details WHERE (emp_id='$emp_number')";

mysql_query($db_query);

$query = "COMMIT";

mysql_query($query);

$numrows = mysql_num_rows($result);

if($numrows > 0){
$query = "UPDATE superv_details SET superv_name='$fullname',dept_code='$department',emp_status='$emp_status',email='$email_addr' WHERE emp_id='$emp_number'";
}

mysql_query($query);

$query = "COMMIT";

mysql_query($query);

Can someone give me an example of how it should be done?

Thanks

[steadyguy]

Pretty sure I don't fit into the 'guru' category, but it occurs to me that this doesn't seem like a situation which requires transactions: because you want to execute the SELECT query all the time, and not have it fail if the UPDATE fails. Instead, I would simply do this:

PHP Code:

$query = "SELECT * FROM superv_details WHERE (emp_id='$emp_number')";
$res = mysql_query($query);
if($res) { //because mysql_query() will return FALSE if a SELECT query has an empty result set (or on error)
    $query = "UPDATE superv_details SET
        sperv_name='$fullname', dept_code='$department', emp_status='$emp_status', email='$email_addr'
        WHERE emp_id='$emp_number'";
    mysql_query($query);
}


However, if you really need to ensure that no one is reading from the table while the script is running, you can obtain a lock which is more portable than a transaction - because it works with all MySQL table types.

PHP Code:

// ... same as above ...
if($res) {
    mysql_query('LOCK TABLES superv_details WRITE'); //now no one can read OR write from the table until it is unlocked.
    if (!mysql_error()) {
        $query = "UPDATE superv_details SET
            sperv_name='$fullname', dept_code='$department', emp_status='$emp_status', email='$email_addr'
            WHERE emp_id='$emp_number'";
        mysql_query($query);
        mysql_query('UNLOCK TABLES'); //unlock tables, don't forget this!!
    }
    else {
        print mysql_error();
        exit;
    }
}