Trying to build my query by fetching column names from the selected table

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);
}

the variable $user is in this case the database name – mvandiepen Dec 10 '18 at 16:44 — mvandiepen, Dec 10 '18 at 16:44
See also `information_schema.columns`. – Rick James Dec 10 '18 at 18:30 — Rick James, Dec 10 '18 at 18:30

Nigel Ren · Accepted Answer · 2018-12-10T17:04:22.080

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);
}

Thanks for the quick help! Is there also a way to skip the first 3 items in the array, since the CSV document doesn't include these items (id, uid, tid) And they may not be used in the insert query since they are given from the server side — mvandiepen, Dec 10 '18 at 17:10
If you just need to remove the items of the start of an array [`array_shift()`](http://php.net/manual/en/function.array-shift.php). — Nigel Ren, Dec 10 '18 at 17:16
Thank you for your help, It solved my issue :D Could you maybe explain why I should avoid using "global"? — mvandiepen, Dec 10 '18 at 19:11
Have a read of https://stackoverflow.com/questions/12445972/stop-using-global-in-php which has a lot of info on it. — Nigel Ren, Dec 10 '18 at 19:14

Trying to build my query by fetching column names from the selected table

1 Answers1