Hey there..
First of all, sorry if this has been discussed before, I tried traversing
the archives but nothing could be found. This problem might be extremly
simple, but anyhow.. :)
The Problem:
I have a php script/Perlscript (should be possible in both :), which needs
to find the next "available" row in a table. This table looks like this:
A_TABLE:
id INT NOT NULL auto_increment default '0',
pre_value char(50) NOT NULL default 'a_predetermined_value',
post_value char(50) NOT NULL default 'table_to_be_updated_with',
in_use datetime
primary key(id)
TYPE=BDB
The table is filled with some values in pre_value so that there is something
there already, though I have no idea how many there may be, that is another
problem which is solved somewhere else :)-
The semantics are very simple:
begin;
select id from A_TABLE where datetime IS NULL;
update A_TABLE set post_value="my_input_from_scripT" where ID=<the id I
got from the above select>;
commit;
The idea is to prevent anyone from seeing the row as "available" once the
transaction has started. My idea in php would be something like:
--- Start Snip
if (!mysql_query("begin",$db)) {
echo "SQL-ERror: ". mysql_Error($db));
exit;}
if (!$res=mysql_query("select id from A_TABLE where datetime IS NULL",$db))
{
echo "SQL-ERror: ". mysql_Error($db));
exit;}
$id = mysql_result($result,id);
if (!mysql_query("update A_TABLE set post_value="$input" where ID=$id")) {
echo "SQL-ERror: ". mysql_Error($db));
exit;}
if (!mysql_query("commit")) {
echo "SQL-ERror: ". mysql_Error($db));
exit;
}
--- End Snip
I'm well aware of the "LOCK TABLE" mechanism's but I've seen what happens if
someone forgets to unlock the table or the script simply dies, so if I can
prevent this, I'd like to :)
Anyone with an idea or can see what on earth I'm missing? :)
Friendly greetings
Søren P. Skou
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
