#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    1

    How to update null values in db


    Hey, I want to give users the option to add their address, second phone number, pretty much more info to their account after they've registered their username, first name, last name, email, phone and password. I have it working great to register their initial information and then for the other values in the row of that user such as the address value, second phone value, etc. I just have it so nothing gets inputed in there until they login to their account and then can enter that input through their account . However, I have it working so that they can update their information that they initially inserted but the other values don't get updated. So I'm not really sure why they null values can't be updated and how I would go about doing it because everything I've tried so far to my knowledge hasn't worked. Any help is greatly appreciated! or if there is a tutorial that could help with this a link to that would be great! Thanks for your time. I can post my code here too but it's fairly long so I don't want to overwhelm anyone from reading this thread and posting some help. Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,232
    Rep Power
    593
    Since we are not clairvoyant how about posting your code.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    11
    Post your code, definitely... and rethink your data model. It sounds like what you really have is a schema problem in disguise. Don't feel bad, there are frightfully few web projects out there based on decent schemas, which is why sites stop growing past a certain point in functionality (they get too hacked up and hard to maintain, and thus undergo total rewrites instead of migrating data forward).

    So let's say you've got one big table with a first and second telephone number on it (among other things). This is not wise because it limits users to however many addresses or phone columns you give them. This forces you to make totally blind assumptions about users, that people will still have phone numbers for as long as your website will exist, and completely confuses the meaning of NULL between "None" (representing the fact that we know there is nothing here) and "unknown" (the actual meaning of NULL).

    You should't have any NULL values to deal with, because nothing about the user input is unknown. The solution is to split the phone number and address data off into their own tables (along with anything else that isn't intrinsic to being a user). This solves your problem in two ways.

    Consider these schemas:
    sql Code:
    -- A typical (dumb) schema
    CREATE TABLE USER
       (email     VARCHAR(256) PRIMARY KEY,
        username  VARCHAR(30) UNIQUE NOT NULL,
        phone1    VARCHAR(15),
        phone2    VARCHAR(15),
        address1  VARCHAR(40),
        address2  VARCHAR(40));

    Here is the typical sort of thing we find in web schemas (and in Access databases built by secretaries and nephews). The idea is that we provide a place for a phone number (phone1), but leave it as a nullable field in case they don't have one. We also give them a second column for a second phone number just in case they have two (like most people do) and leave that nullable as well for the same reason. But what if they have three? Or a very important user comes along and has five? Are you going to expand the table, spawning users*columns number of blank data spaces in your database just to store one guy's third phone number because he's a Big Deal? That's ridiculous. So is the assumption that a phone number has anything to do with being a user -- unless you're a phone company, of course.
    sql Code:
    -- A proper schema
    CREATE TABLE USER
       (email     VARCHAR(256) PRIMARY KEY,
        username  VARCHAR(30) UNIQUE NOT NULL);
     
    CREATE TABLE phone
       (id          SERIAL UNIQUE NOT NULL,
        USER        VARCHAR(256) REFERENCES USER ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
        NUMBER      VARCHAR(15) NOT NULL,
        created_on  timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (USER, NUMBER));
     
    CREATE TABLE address
       (id          SERIAL UNIQUE NOT NULL,
        USER        VARCHAR(256) REFERENCES USER ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
        line        VARCHAR(40) NOT NULL,
        created_on  timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
        PRIMARY KEY (USER, line));

    I'll skip over the data theory explanations of why this is always a better way to do things and Instead just focus on how this solves your problem.

    In your form you have a field for email, username, phone number and address. The email and username fields are mandatory but not the address or phone number. Obviously the first step is to try creating the user with the given email and username. The database will force both of these to be unique already, so you can just catch the database error if either one isn't and refresh the page according if and what the error is (email or username being already used).

    The second step would be to create a phone number linked to the user you just created if the phone field is filled in. The third step is to do the same with the address field.

    Simple. To get their data you just query the user joined to the phone and address tables. If you want to only see the oldest (or most recent) phone or address they entered (making the assumption this is their primary one) you just order the address and phone tables by user and created_by timestamp, and join to only the top of each (how to do this varies based on whether you're sending actual SQL queries to your db or if a framework is handling it, but that's just an implementation detail -- unless you use a framework that can't do this, in which case ditch it and start writing SQL (its not hard)).

    In the case that a user wants to add another address or phone number, you present a page with a phone or address field (or even all their data with all their phone numbers/addresses plus an extra entry field of each type to let them enter a new one). When they submit if the relevant field is filled in, just create a new entry in the appropriate table the same way you did in steps two and three above.

    No NULLs, no limit on how many (or even none) phone numbers and addresses a user can register, and no mucking about with sorts, sting manipulations or other unexpected insanity based on fields that might be NULL and exhibit some really wacky behavior because of how NULL fields behave in different databases.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    1
    Woah I didn't realize anyone replied to this thread. Thanks! I'm going to read your post and see if I can make sense of it and I'll post back here with my results. Thanks for your time and help.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    1
    Awesome! more information than I was expecting! I will definitely go with that model for acquiring user information. I'm not going to pretend I'm an expert with php I still consider myself a beginner even though I can use some php functions and sql statements comfortably but the main issue I'm running into is that I can't seem to update a column that contains a 0. If I enter in data like lets say the column is named price and I enter $5, then go back and UPDATE that information, I it will work, but if I put a 0 in the column because I don't have a price to put in there yet, then go to update it, the information won't change, just stays 0. I posted my code for that issue here, third comment on the thread, Code. I went back a number of times to check and see if I made any spelling mistakes and I can't find any, and like I said the fields update no problem with inputed data but if a 0 is put in a field, the whole form won't submit any data to the db. If you could help me with that it would be greatly appreciated! Thanks again for all your help and suggestions.
  10. #6
  11. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    501
    Rep Power
    127
    Originally Posted by zxq9
    I'll skip over the data theory explanations of why this is always a better way to do things and Instead just focus on how this solves your problem.
    And like many theories, a bit of an overkill in most common practices. It makes sense for sure if you want infinite (or 2+) of something attached, but in this case I don't think it's worth it.

    By the logic that only data that's intrinsic to being a user should be in the user table, you'd have to apply the same to every potentially null field--even if the specification only calls for one value per user.

    To take your typical forum software as an example, the signature, biography, location, avatar, etc., are all common singular optional elements that might be null, that I would never split off into tables of their own. The multiple inserts, updates, and JOINs just aren't worth it.

    And if you utilize the active record pattern religiously you'd be talking about a lot of object bloat.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    1
    Hey thanks, the project im working on isnt that in depth to really hve to break the tables up but it seems like it would be good practice for a bigger database. Would u be able to help me with the issue i posted before you post about 0 values in a column not updating with link to the code i postwd in another thread thats related to this thread? It would be greatly appreciated. Thanks again

IMN logo majestic logo threadwatch logo seochat tools logo