how to write a select query with condition ?

[windowsxp]

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 ?

[Pullo]

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):

<?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();
  }
?>

[windowsxp]

No PHP please.

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

[Pullo]

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.

[r937]

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 --

WHERE studentid = $input

so your comment "No PHP please" is unreasonable

that being said, you can try it like this --

WHERE studentid = $input OR -1 = $input

[windowsxp]

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 --

WHERE studentid = $input

so your comment "No PHP please" is unreasonable

that being said, you can try it like this --

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

[r937]

what i gave you ~is~ a single query

and besides, in a stored procedure, you ~can~ use an IF

[windowsxp]

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 ?

[r937]

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 --

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

[windowsxp]

the solution i provided gives you exactly that

perhaps you were confused because i did not post the entire query, so here you go --

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.

[guido2004]

also OR part is not going to execute

Why?

[windowsxp]

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)

[r937]

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

[guido2004]

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?