如何将硬编码值转为变量？批量获取数据库varchar字段长度方法

Got it, let's refactor your code to ditch the hardcoded values and capture varchar lengths for all columns you need—whether that's a single table or your entire database. Here's a step-by-step breakdown:

1. Replace Hardcoded Values with Parameterized Variables #

First, we'll swap the fixed table/column names with variables, and use parameterized queries (the ? placeholders) to avoid SQL injection risks. This makes your code flexible to target any table:

// Define your target table as a variable (can be pulled from config/user input too)
$targetTable = 'VTIGER_LEADDETAILS';

// Query to get ALL varchar columns and their lengths for the table
$lengthQuery = $adb->pquery(
    "SELECT column_name, CHARACTER_MAXIMUM_LENGTH 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_NAME = ? 
     AND DATA_TYPE = 'varchar'", // Filter only varchar type columns
    array($targetTable)
);

2. Store Results in an Array #

Next, loop through the query results and populate an array where each key is the column name, and the value is its varchar[n] length n:

$varcharLengths = [];

// Fetch each row and add to the array
while ($row = $adb->fetchByAssoc($lengthQuery)) {
    $varcharLengths[$row['column_name']] = $row['CHARACTER_MAXIMUM_LENGTH'];
}

// Example output: ['email' => 100, 'lead_title' => 255, ...]
print_r($varcharLengths);

3. Bonus: Scale to Entire Database #

If you need to get varchar lengths for every table in your database, add a database name variable and adjust the query:

$targetDb = 'your_database_name'; // Replace with your actual DB name

$allTablesQuery = $adb->pquery(
    "SELECT TABLE_NAME, column_name, CHARACTER_MAXIMUM_LENGTH 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_SCHEMA = ? 
     AND DATA_TYPE = 'varchar'",
    array($targetDb)
);

// Organize lengths by table name
$allVarcharLengths = [];
while ($row = $adb->fetchByAssoc($allTablesQuery)) {
    $allVarcharLengths[$row['TABLE_NAME']][$row['column_name']] = $row['CHARACTER_MAXIMUM_LENGTH'];
}

// Example output: ['VTIGER_LEADDETAILS' => ['email' => 100, ...], 'VTIGER_ACCOUNTS' => [...]]
print_r($allVarcharLengths);

Key Tips #

• Parameterized Queries: Never concatenate variables directly into SQL strings—using ? placeholders keeps your code safe from SQL injection.
• Cross-DB Compatibility: The INFORMATION_SCHEMA.COLUMNS view is standard in most relational databases (MySQL, PostgreSQL, SQL Server, etc.), so this approach works across systems.
• Permissions: Ensure your database user has read access to the INFORMATION_SCHEMA tables (most default users do, but double-check if you run into errors).

内容的提问来源于stack exchange，提问作者Besart Marku