SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    updating a cell - only if blank

    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.

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Assuming that owner is NULL if nobody has claimed it yet, you can just adapt your existing query with a single condition:

    Code sql:
    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.

  3. #3
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  7. #7
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  10. #10
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by menuserve View Post
    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!

  11. #11
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    904
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats alright... you got me 98% to the solution and i greatly appreciate it. I'm new to mysql programming!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •