Results 1 to 8 of 8
  1. #1
    Join Date
    May 2011
    Posts
    6

    Unanswered: Nullifying parts of a statement [resolved]

    Hi,

    Oracle SQL being run from PHP

    Is there any way to nullify or cancel a specific part of a statement? We are designing a reporting tool which will take user input and dynamically create an SQL statement based on it. The problem is, if the user doesn't enter any data we want to then pull everything (even null values). So we can't just say where x is not null if they don't type anything.

    Example:
    select * from tableA where id = :id and happy = :happy
    - if the user enters a number into the ID field, we simply fire that off and build the statement
    select * from tableA where id = 10 and happy = 'TRUE'

    Example (the problem):
    select * from tableA where id = :id and happy = :happy
    - if the user doesn't enter an ID, how can we build this statement without doing some crazy string manipulation of rewritting the whole statement?
    - We want to be able to say "where id = anything"


    The only solution I could come up with is this:
    select * from tableA where id like :id and happy like :happy
    -if the user doesn't enter an ID, replace :id with '%'

    But that's a very bad solution for many reasons. Any thoughts?
    Last edited by georgewashinton1; 10-27-11 at 13:51.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    typical solution is to always code WHERE clause that starts with below

    WHERE 1 = 1

    now you are free or not to include more "AND" conditions
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2011
    Posts
    6
    I was hoping to be able to store the entire query without building a new one for each parameter the user chooses.

    Given your solution I would store:
    select * from tableA where 1=1

    Then have a bunch of parameters stored which are glued onto the query through PHP.

    I would like to just store:
    select * from tableA where id = :id
    or select * from tableA where 1=1 and id = :id

    Then the id = :id is either ignored when no input is given or glued the user input into the query.





    I did find this: Dynamically changing the WHERE clause, without actually changing it

    However, this would require non trivial modification to all our existing reports.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You won't like the following any better.
    code every combination/permutation of SQL & only invoke the one SQL which matches the inputs.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2011
    Posts
    6
    Quote Originally Posted by anacedent View Post
    You won't like the following any better.
    code every combination/permutation of SQL & only invoke the one SQL which matches the inputs.
    Ha!


    If it comes down to it I'll sit down and write the PHP to glue on the parameters

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    While you consider the SELECT as variations on underlying theme, SQL engine views them as separate, distinct & different statements; since different result sets get returned for each.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2005
    Posts
    57
    have you tried

    Code:
    select * from tableA where id = nvl(:id,id) and happy = nvl(:happy,happy)

  8. #8
    Join Date
    May 2011
    Posts
    6
    That just might work...I can't believe how simple the solution was....



    select * from tableA where id = id and happy = happy


    That seems a bit redundant but if it gets around tedious parameter generation I think I'd be willing to deal with it. This is exactly what I was looking for. Thanks!

Posting Permissions

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