循环列名 MYSQL [英] Loop in column name MYSQL

问题描述

我正在使用 MYSQL.我的表包含列名称为 Revenue2000,Revenue2001,Revenue2002,....,Revenue 2016,Revenue 2017

I am using MYSQL.My table contains column name as Revenue2000,Revenue2001,Revenue2002,....,Revenue 2016,Revenue 2017

传统方式(手动选择所有列):

Traditional way(to select all column manually):

select Revenue2005,
    Revenue2006,
    Revenue2007,
    Revenue2008,
    Revenue2009,
    Revenue2010
from table_name

想要的方式:

我想写一个动态选择语句.应该有 2 个变量开始"和结束"，以便我可以使其动态化.用户可以选择指定开始年份和结束年份，并可以查看所需的结果.

I want to write a Dynamic select statement .There should 2 variables "start" and "end" so that i can make it dynamic.User has the option to specify the starting year and ending year and can view the desired result.

在上述情况下，开始年份 =2005年底=2010

In above case, Start year =2005 End Year=2010

推荐答案

正如已经指出的数据库设计是可怕的.你真的应该把它正常化，这是值得的.

As already pointed out the database design is horrible. You should really normalize it, it's worth the effort.

但是，如果目前无法实现，以下代码应该完全满足您的需求:

However if that is not possible at the moment the follow code should do exactly what you need:

// Connect to DB
$mysqli = new mysqli("localhost", "USERNAME", "PASSWORD", "DATABASE");

// Get column names
$columns = $mysqli->query('SHOW COLUMNS FROM revenue')->fetch_all();
$columnNames = array_column($columns, 0);

// Extract years from column names
$years = array_map(function($columnName) {
    return (int) substr($columnName, -4);
}, $columnNames);

// Get max and min year
$maxYear = max($years);
$minYear = min($years);

// Input year start and end
$start = (int) $_POST['start']; // User-input
$end = (int) $_POST['end']; // User-input

// Avoid wrong inputs
if($start > $end || $start < $minYear || $end > $maxYear) {
    die('Error');
}

// Create the SQL-query
$selectColumns = [];
for ($i = $start; $i <= $end; $i++) {
    $selectColumns[] = "revenue" . $i;
}
$queryString = "SELECT " . implode(", ", $selectColumns) . " FROM TABLE";

// Run the query
// ...

这篇关于循环列名 MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！