SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to write a select query with condition ?

    I want to pick students based on user input to a select query.


    Example:
    ----------

    if input = -1 which means all students .. then I want to select all students

    if input = studentId then I want to select that specific student only.



    my problem is , How do I feed these conditions into where clause of the select query ?


    As far as I know I can not use IF ELSE in where clause. What is the way to select students based on conditions ?

    What should be the structure of the query ?

  2. #2
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,890
    Mentioned
    211 Post(s)
    Tagged
    12 Thread(s)
    Hi,

    If you are working in PHP, for example, you could use that to build your query based on your user input before executing it.

    e.g. using PHP and PDO to access the database (not tested):

    Code PHP:
    <?php
      $select = 'SELECT *';
      $from   = ' FROM table';
      $where  = ' WHERE TRUE';
     
      $input= $_POST['yourUserInput'];
      $placeholders = array();
     
      if ($input != -1)
      {
        $where .= " AND id = :id";
        $placeholders[':id'] = $input;
      }
     
      try
      {
        $sql = $select . $from . $where;
        $s = $pdo->prepare($sql);
        $s->execute($placeholders);
      }
      catch (PDOException $e)
      {
        $error = 'Something went wrong!';
        include 'error.html.php';
        exit();
      }
    ?>

  3. #3
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No PHP please.

    I want to do it in the DB side ....trying to write a mysql select query with a conditional where clause.

  4. #4
    Gre aus'm Pott gold trophysilver trophybronze trophy
    Pullo's Avatar
    Join Date
    Jun 2007
    Location
    Germany
    Posts
    5,890
    Mentioned
    211 Post(s)
    Tagged
    12 Thread(s)
    That's cool!
    It'll also probably be why you posted in the database forum and not the PHP forum.
    I've no idea how to build logic into MySQL queries, or in fact if it can be done at all.
    I'll watch this thread with interest.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by windowsxp View Post
    No PHP please.
    yeah, but you have to pass in the "input" value of the id somehow, right?

    so the query you're trying to construct has to have the input variable in it --
    Code:
    WHERE studentid = $input
    so your comment "No PHP please" is unreasonable

    that being said, you can try it like this --
    Code:
    WHERE studentid = $input OR -1 = $input
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yeah, but you have to pass in the "input" value of the id somehow, right?

    so the query you're trying to construct has to have the input variable in it --
    Code:
    WHERE studentid = $input
    so your comment "No PHP please" is unreasonable

    that being said, you can try it like this --
    Code:
    WHERE studentid = $input OR -1 = $input
    yea .. I'm passing those values from JSP to DB ....don't worry about this part...Its done already..... data has come to the database store procedure safely.



    Now I am trying to write a single select query inside stored procedure here.


    create procedure 'sp_get_student'( studentId varchar(50))
    BEGIN

    //I want a single select query here with conditional where clause as explained earlier

    END

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what i gave you ~is~ a single query

    and besides, in a stored procedure, you ~can~ use an IF
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    what i gave you ~is~ a single query
    I don't want a PHP solution. What you have posted is a workaround ...not the answer to my original query.

    see ...I write JSP the same you write PHP. I could do the same logic in JSP side too and solve it easily ! ...But that is not what I wanted.....I want a solution from DB tier.
    [QUOTE]

    and besides, in a stored procedure, you ~can~ use an IF
    Thats right .... But as far as I know I can not use IF - ELSE in a single select query.....Can we ?

    Looks like you did not understand my query. Please let me know which part you don't understand ?

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by windowsxp View Post
    if input = -1 which means all students .. then I want to select all students

    if input = studentId then I want to select that specific student only.
    the solution i provided gives you exactly that

    perhaps you were confused because i did not post the entire query, so here you go --
    Code:
    SELECT ...
      FROM ...
     WHERE studentid = $input 
        OR -1 = $input
    i don't care whether you use jsp or c-flat or whatever language, but you ~must~ feed the value of the input number (which you said was going to be either a studentid or -1) into the query somehow, and in the solution i gave you, that would be $input, which perhaps confused you because it looks like a php variable

    this solution ~is~ in "the DB tier"

    this solution ~is~ in the WHERE clause

    please, give it a try before you say i didn't understand your requirements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the solution i provided gives you exactly that

    perhaps you were confused because i did not post the entire query, so here you go --
    Code:
    SELECT ...
      FROM ...
     WHERE studentid = $input 
        OR -1 = $input
    i don't care whether you use jsp or c-flat or whatever language, but you ~must~ feed the value of the input number (which you said was going to be either a studentid or -1) into the query somehow, and in the solution i gave you, that would be $input, which perhaps confused you because it looks like a php variable

    this solution ~is~ in "the DB tier"

    this solution ~is~ in the WHERE clause

    please, give it a try before you say i didn't understand your requirements
    OK...well...in your example suppose I send $input = -1 ....then your query becomes...

    SELECT ...
    FROM ...
    WHERE studentid =-1
    OR -1 = -1


    are you trying to say this query will retrieve all students ?...I dont think so ..because there is no -1 really in the studentid column you know... also OR part is not going to execute.

    I guess this is the reason we may need to plug an IF ELSE here ....and precisely this is what I'm looking for.

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by windowsxp View Post
    also OR part is not going to execute
    Why?

  12. #12
    SitePoint Addict
    Join Date
    Dec 2009
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Why?
    because red part will execute to fetch zero records ....skipping OR part.

    WHERE studentid =-1
    OR -1 = -1




    I think we may need to use a CASE here...

    WHERE (CASE WHEN $input<>1 THEN studentid=$input ELSE -1=-1)

    This looks complete ....but anyway can we use a input variable in the CASE statement this way ? ( assuming $input as a store proc variable)

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by windowsxp View Post
    are you trying to say this query will retrieve all students ?
    i'm not "trying" to say it... i ~am~ saying it

    i also kindly asked you to test it... have you bothered to test it yet?

    you don't need a CASE expression, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by windowsxp View Post
    because red part will execute to fetch zero records ....skipping OR part.
    You didn't try it did you? I don't know what DB you are using, but I've never seen anyone that does what you are describing. What use would OR be if the second part would never be executed?


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
  •