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

MYSQL loop through year columns and stop at first not found

This is absolutely a bad practice, I know, but it’s an abandoned project from a previous developer and I need to keep using the same structure.

Basically, there’s a table which has to be edited every year by adding a new column like: year_2023, year_2024, year_2025, year_2026 and so on, and someone already takes care of it.

This is DEFINITELY a bad approach and will clog the table with a large amount of columns over time, but I need to create a function which depends on ALL these columns (if they exist or not).

Let’s make a pratical example. I know I could simply do:

SELECT * 
FROM table 
WHERE year_2023 != '0000-00-00' 
OR year_2024 != '0000-00-00' 
OR year_2025 != '0000-00-00' 
OR year_2026 = != '0000-00-00'

To select, AT THE CURRENT STATE, all the columns which have a date set in such columns, but I wouldn’t want, next year, to edit this query by adding:

    ... OR year_2027 = != '0000-00-00'

So my question is, is there a way to loop through the year columns, and eventually stop looping once they find the first non-existing column?

As, if year_2027 doesn’t exist (yet), there would be no need to keep iterating to all years, so year_2028 and so on wouldn’t need to be checked as they won’t exist as well.

Tags:

2

Answers


  1. 0

    you can get column names by

    SELECT `COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_SCHEMA`='yourdatabasename'
    AND `TABLE_NAME`='yourtablename';
    Login or Signup to reply.
  2. 0

    To dynamically query columns in SQL, especially when the columns have a structured naming convention like year_XXXX, you can use dynamic SQL with the help of system tables or views that provide metadata about your database schema.

    For MySQL, you can query the information_schema.columns table to get information about columns in a specific table. Here’s an example of how you could construct a dynamic SQL query to achieve what you want:

    -- Constructing the dynamic SQL query
SET @sql = CONCAT('SELECT * FROM your_table WHERE ');

-- Get the maximum year present in your table columns
SELECT MAX(SUBSTRING_INDEX(column_name, '_', -1)) INTO @max_year
FROM information_schema.columns
WHERE table_schema = 'your_database' -- replace 'your_database' with your database name
  AND table_name = 'your_table';      -- replace 'your_table' with your table name

-- Loop through the years and construct the WHERE clause
SET @year = 2023; -- Starting year
WHILE @year <= @max_year DO
    SET @sql = CONCAT(@sql, 'year_', @year, ' != '0000-00-00' OR ');
    SET @year = @year + 1;
END WHILE;

-- Remove the last 'OR' from the query
SET @sql = LEFT(@sql, LENGTH(@sql) - 3);

-- Execute the dynamic SQL query
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

    This script dynamically constructs a SQL query to select rows from your table where any of the year_XXXX columns have a non-zero date value. It retrieves the maximum year present in your table columns and then constructs the WHERE clause dynamically for all years from 2023 up to the maximum year found in your table.

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

Back To Top
Search