Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    486
    Thanks
    10
    Thanked 33 Times in 32 Posts

    changing field on fly

    The issue I have is that I need to change a field value return based on the results of its other column. I cannot change the value of the database. Let me give you an example.
    I have a table like this
    id, prescription, insurance_pay
    If a prescription's name ='bob' then I need to change insurance_pay to 0 in the results.
    So, when it is returned to php, bob will have the value of 0 in insurance pay instead of what the original value in the database table is.

    I appreciate the help.
    Last edited by Chris Hick; 10-09-2013 at 05:44 AM.
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,223
    Thanks
    80
    Thanked 4,454 Times in 4,419 Posts
    I have a table like this
    id, prescription, insurance_pay
    So there are 3 (relevant) fields in that table.

    If a prescription's name ='bob'
    But none of the relevant fields you listed have a field name of name. Where in the world did the field name come from?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    486
    Thanks
    10
    Thanked 33 Times in 32 Posts
    Wow, that is my bad Pendant. I meant if the word in prescription is bob. I should have proofread my post.
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,223
    Thanks
    80
    Thanked 4,454 Times in 4,419 Posts
    Trivial then:
    Code:
    UPDATE yourtablename
    SET insurance_pay = 0 
    WHERE prescription = 'bob'
    If you meant that you wanted to change insurance_pay if "bob" appears ANYWHERE in the prescription field, then:
    Code:
    UPDATE yourtablename
    SET insurance_pay = 0 
    WHERE prescription LIKE '%bob%'
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,223
    Thanks
    80
    Thanked 4,454 Times in 4,419 Posts
    Hmmm...but maybe you meant that you don't want to change the contents of the table and only want to change what the SELECT returns???

    Code:
    SELECT id, prescription, IF(prescription='bob', 0, insurance_pay) AS insurance_pay
    FROM yourtablename
    Again, you could use LIKE there instead:
    Code:
    SELECT id, prescription, IF(prescription LIKE '%bob%', 0, insurance_pay) AS insurance_pay
    FROM yourtablename
    You could even create a VIEW of the table that would do this so that you could join that VIEW to other tables in more complex queries.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Oct 2010
    Location
    Florence, MS
    Posts
    486
    Thanks
    10
    Thanked 33 Times in 32 Posts
    Could you elaborate on the View part you said?? I haven't actually used a View yet.
    Notice: If you post a problem and it gets fixed, please remember to go back and place it as solved. ;)
    I always recommend the HEAD First series of books for learning a new coding language. ^_^

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,223
    Thanks
    80
    Thanked 4,454 Times in 4,419 Posts
    Code:
    CREATE VIEW bobPaysNotihingView
    AS
    SELECT id, prescription, IF(prescription='bob', 0, insurance_pay) AS insurance_pay
    FROM yourtablename;
    And now anyplace you would have used yourtablename before, you just use bobPaysNothingView instead.

    The view will act like a table, but it will "filter" the results as you wanted.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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