尝试通过从所选表中获取列名来构建我的查询

Question

I'm working automatically and dynamically generating SQL queries for inserting CSV data into a selected database. Now I have a list of 10 different databases. Now I'm curious if it is possible to build a part of my query dynamically (the table names) by fetching the column name from the database?

This is the code I have right now but it doesn't quite work:

function getTableDetails($table_name) {
    global $con, $user;

    $describeTable = mysqli_query($con, "DESCRIBE " . $user . "." . $table_name);

    $fields = [];
    while($show = mysqli_fetch_fields($describeTable)) {
        $fields['column_name'][] = $show['COLUMN_NAME'];
        $fields['column_length'][] = $show['CHARACTER_MAXIMUM_LENGTH'];
        $fields['column_type'][] = $show['COLUMN_TYPE'];
    }

    return $fields;
}

How I try to fetch them

$table = getTableDetails($settings_type);
foreach ($table['column_name'] as $columnName) {
    print_r($columnName);
}

Answer 1

I've changed the function slightly to pass in the fields which you access using global (as this isn't recommended). So you will have to alter the call to getTableDetails().

mysqli_fetch_fields() is used to return the fields which are part of the result set, as this is from a describe, you were fetching the fields which were the return values of the describe rather than the fields in the table. Instead you need to use mysqli_fetch_assoc() which returns the rows of data from the statement.

The other thing to always check is if you have problems with fetching data is to use print_r() to check what is being returned.

I've also indexed the data by the column name as well, this can be useful sometimes, but you could also just use $fields[] = [....

As the field length isn't part of the field set being returned, I've added code which will extract it from the data type, so int(11) has the value 11 extracted from between the brackets using preg_match().

function getTableDetails( $con, $user, $table_name) {
    $describeTable = mysqli_query($con, "DESCRIBE " . $user . "." . $table_name);

    $fields = [];
    while($show = mysqli_fetch_assoc($describeTable)) {
        $columnName = $show['Field'];
        // Extract length from field type (if any)
        preg_match('#\((.*?)\)#', $show['Type'], $match);
        $fields[$columnName] = ['column_name' => $show['Field'],
            'column_length' => $match[1]??0,
            'column_type' => $show['Type']];        
    }

    return $fields;
}

$table = getTableDetails( $con, $user, "articles");
foreach ($table as $columnName) {
    print_r($columnName);
}