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

Can MySQL provide field names dynamically in where clauses in queries?

I have a table that read day_sunday, day_monday, etc…
So I need to check that for today’s column if value is true then what data is stored.
For example 17 may => Wednesday so the query will be

select * from table where day_wednesday

Similarly for tomorrow the query won’t change it should use field day_thursday in where caluse.

I have written below query but it is not working.

select restaurant_id from day_matrix where "day_"||lower(dayname(now())) = 1;

Any help will be appreciated. This is just one query which data needs to get joined in another query to retrieve data.

Tags:

2

Answers


  1. 0

    Columns and expressions in the WHERE clause must be fixed at the time you prepare the query. There’s no way to parameterize columns or expressions in SQL.

    The way most people solve this problem is by writing application code to add terms to your WHERE clause based on application logic and variables. In other words, append fragments of SQL syntax to a string inside code constructs like if (). The specific methods for doing this depend on the programming language you’re using.

    To help in this technique, sometimes frameworks provide a query builder API.

    Reference: https://stackoverflow.com/a/7476727/4476745

    Login or Signup to reply.
  2. 0

    Fix your data model! These columns should be represented as rows in a separate table, as in:

    create table restaurant (
    id int primary key
    -- more columns for other restaurant attributes
);

create table restaurant_schedule (
    restaurant_id   int not null references restaurant (id),
    week_day        int not null,   -- weekday as a number (eg : Monday=0 .. Sunday=6)
    is_active       int not null,   -- 0 or 1
    primary key (restaurant_id, week_day)
);

    Now, looking up restaurants that are active today is as simple as a join:

    select r.*
from restaurant r
inner join restaurant_schedule rs on rs.restaurant_id = r.id
where rs.week_day = weekday(current_date) and is_active = 1

    If for some reason you are stuck with your current schema, then a simple solution is boolean logic – but it requires enumarating all column names in the query:

    select *
from mytable
where 1 = case dayname(current_date)
    when 'Monday'  then day_monday
    when 'Tuesday' then day_tuesday
    ...
    when 'Sunday'  then day_sunday
end
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search