Execute Select Statement based on condition and user input MySQL

Question

I am trying to allow a particular SELECT statement to execute based on a particular string sent to the IF condition. For Example, if the user selects support from the dropdown, a variable key is sent to the sql statement, and the appropriate SELECT Statement is executed.

DECLARE @ strg
SET @strg = 'input string'
IF(SELECT * FROM tickets WHERE ticket_section = @strg)
BEGIN
SELECT
usr_users.uname AS `user`,
support_chat_user.message AS user_message,
support_chat_user.created AS user_chat_date,
support_ticket.`subject` AS ticket_subject,
support_ticket.`status` AS ticket_state,
support_ticket.created AS ticket_date,
support_ticket.modified AS updated_date,
tickets.state
FROM
tickets
INNER JOIN support_chat_user ON support_chat_user.tickets_id = tickets.tick_id
INNER JOIN support_ticket ON support_ticket.tickets_id = tickets.tick_id
INNER JOIN usr_users ON support_chat_user.reporter_id = usr_users.uid
WHERE
tickets.tick_id = '9' AND
usr_users.uid = '1'
END

IF EXISTS(SELECT * FROM tickets WHERE ticket_section = @strg)

BEGIN
SELECT
usr_users.uname AS `user`,
support_chat_user.message AS user_message,
support_chat_user.created AS user_chat_date,
support_ticket.`subject` AS ticket_subject,
tickets.state
FROM
tickets
INNER JOIN support_chat_user ON support_chat_user.tickets_id = tickets.tick_id
INNER JOIN support_ticket ON support_ticket.tickets_id = tickets.tick_id
INNER JOIN usr_users ON support_chat_user.reporter_id = usr_users.uid
WHERE
tickets.tick_id = '9' AND
usr_users.uid = '1'

END

Gordon Linoff · Accepted Answer · 2018-10-06 15:11:24Z

1

MySQL does not support IF, unless you put the code in a programming block. Your question is a bit hard to follow, because the text mentions one query but the code as two that seem to be identical.

You can move the conditional logic to the outermost WHERE clause in your query:

SELECT u.uname AS `user`, scu.message AS user_message, scu.created AS user_chat_date,
       st.`subject` AS ticket_subject, st.`status` AS ticket_state, st.created AS ticket_date, st.modified AS updated_date,
       t.state
FROM tickets t JOIN
     support_chat_user scu
     ON scu.tickets_id = t.tick_id JOIN
     support_ticket st
     ON st.tickets_id = t.tick_id JOIN
     usr_users u
     ON scu.reporter_id = u.uid
WHERE t.tick_id = 9 AND  -- presumably this is not numeric
      u.uid = 1 AND      -- presumably this is not numeric
      EXISTS (SELECT 1 FROM tickets t2 WHERE t2.ticket_section = @strg)

I'm not sure this is what you really want. I suspect you want:

WHERE t.tick_id = 9 AND  -- presumably this is not numeric
      u.uid = 1 AND      -- presumably this is not numeric
      (t.ticket_section = @strg OR @strg IS NULL)

However, that is merely informed speculation on what would be useful in the situation you describe.

Notes:

Table aliases make the query easier to write and to read (as does proper indentation).
Numeric constants should not be introduced with single quotes. I am guessing the ids are really numbers. If they are strings, then single quotes are appropriate.

answered Oct 6, 2018 at 15:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Augustus Buckman Over a year ago

,Thanks for the answer, but what i want to achieve is this, i have 3 different tables that i want query from, all these tables are linked to a central table called tickets. so if a ticket is issued by someone, it goes to the ticket table which then sub segments it to the appropriate table based on the ticket_section. so now i want to Run a query that would first check which sub table the request belongs to, then executes a particular SELECT Clause. Note: All the tables are not of the same structure, so different results would be achieved

Collectives™ on Stack Overflow

Execute Select Statement based on condition and user input MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related