Question 262076 in Mysql Question posted in
The official MySQL documentation can be found here.

MYSQL SELECT but with conditions

Is it possible within a MYSQL SELECT statement to include conditions dependent on user selection? Edited for clarity.
Something that does this:

IF user *does A* SELECT * FROM myTable
IF user *does B* SELECT colB, colC, colD FROM myTable
ELSE SELECT * FROM myTable WHERE ID < 5

Original Post references proprietary frontend but I just want to see if there is a solution in a MYSQL Query alone.

I have a MYSQL Query that returns a filtered view of a table based on a dropdown selection of a column value. I want to include an option to Return All of the table if the dropdown doesn’t match a value from the column or if the user selects ‘Show All’. Something like this:

Is there a way to use ELSE like this:

SELECT colA FROM mytable
WHERE *userChoice* = colA
ELSE SELECT * FROM myTable

Or, I think it can be done with a case statement but I can’t figure out the syntax:

    CASE
        WHEN userPick = colA THEN SELECT colA FROM myTable
        WHEN userPick = colB THEN SELECT colB FROM myTable
        WHEN userPick = selectAll THEN SELECT * FROM myTable
        ELSE SELECT * FROM myTable
    END

I want to add a clause to the query that will allow me to return something else if the user doesn’t select a distinct value from colA.
Rough image of what I currently have.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT *
FROM myTable
WHERE (colA) LIKE '%SearchString%'
OR (SelectAll) LIKE '%SA%'

    I found a workaround, I added a SelectAll column and referenced that in my SELECT query


  2. 0

    you need to build query string, check this thread Mysql dynamically build query string in a stored procedure based on logic

    with below query like this

    CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(input VARCHAR(15))
BEGIN
SET @input = input;

if @input="asc" then
    SET @sort = " order by ActivityLogKey asc";
elseif @input = "desc" then
    SET @sort = " order by ActivityLogKey desc";
else
    SET @sort ="";
end if;

SET @query = CONCAT('select * from activitylog ',@sort,' limit 0, 5');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search