updating a cell - only if blank

[menuserve]

Right now i can insert into a cell in my table from my website using the UPDATE call as follows:

$sql="UPDATE certnumbers SET owner='{$_SESSION['logname']}' WHERE id='$_POST[verinum]'";

I'm basically allowing the user to attach their loginname to a cell (owner) in a row identified by the id of the row.

I would ONLY like the user to add their name if the cell is blank, if its not blank and someone else has added their name already, it should give a message back to the user saying its already owned by someone else and NOT update.

Any help would be appreciated.

[SJH]

Assuming that owner is NULL if nobody has claimed it yet, you can just adapt your existing query with a single condition:

UPDATE certnumbers SET owner='{$_SESSION['logname']}' WHERE id='$_POST[verinum]' AND owner IS NULL

By the looks of things you're using PHP, so you can simply use mysql_affected_rows to find out whether the UPDATE query actually changed anything - if this function returns zero, then presumably the owner had already been set and you can display the error message.

[menuserve]

SJH - I made the change from

$sql="UPDATE certnumbers SET owner='{$_SESSION['logname']}' WHERE id='$_POST[verinum]'";

to

$sql="UPDATE certnumbers SET owner='{$_SESSION['logname']}' WHERE id='$_POST[verinum]' AND owner IS NULL";

Now it doesn't update any cells regardless of null or not, as to where before it just updated the cell

[SJH]

Are you sure the values of the cells you're trying to update are actually NULL? And not am empty string?

Tell you what, run this query in phpMyAdmin or wherever:

SELECT * FROM certnumbers WHERE id = [value of verinum variable] AND owner IS NULL

If this doesn't return anything then you'll need to reorganise things so that owner, by default, is NULL.

[menuserve]

The query says completed, no results found.

when I go into DBADMIN it shows NULL as yes and Default as NULL.

So it should be set correctly already

[SJH]

Oops, my logic was extremely dodgy in my first post

I of course meant to type IS NOT NULL at the end of the query.

Sorry for the confusion!

[menuserve]

hmmm... now i'm confused... so i currently have the following:

$sql="UPDATE certnumbers SET owner='{$_SESSION['logname']}' WHERE id='$_POST[verinum]' AND owner IS NOT NULL";

this seems backwards.. if someone other user's name is in the owner column, this updates it to the new owner. it shouldn't. it should only be updating when the owner field has nothing in it.

I thought you had it right the first time, it just didn't update anything, owner in the field or not.

[r937]

there is a big difference between blank (length=1), empty string (length=0), and NULL (length=undefined)

it should be easy to find out which it is...

[menuserve]

I got it thanks for the help... The column default was in fact NULL... however the column was added recently and the existing rows needed to be updated to reflect NULL

[SJH]

I thought you had it right the first time, it just didn't update anything, owner in the field or not.

Oops, sorry, that'll teach me for trying to post from work while trying to do a million other things!

[menuserve]

Thats alright... you got me 98% to the solution and i greatly appreciate it. I'm new to mysql programming!