CODEWITHSUNDEEP
phpmysqlsql
Magic Lasso
Magic Lasso

Reputation: 1542

What is the syntax in mysql to get the column names of a table?

Can somebody please enlighten me on the use of SHOW COLUMNS specifically in regards to PHP/MySQL because I have not found anything of much use through search.

What I want to do is get all the column names out of a table, then use those to get the correct data from session to do an update.

what i have so far is:

function get_Columns($con, $table){

    $sql_statement = "SHOW COLUMNS FROM " . NAME_TABLE_BASE . $table ;

    $temp = mysql_query($sql_statement) or die(mysql(error)) ;

    return mysql_fetch_array($temp) ;
}
?>

The problem is this is only returning the first column and not all of them. What do I need to change? Also is there anything I can add to $sql_statement to only get the fields and not all the other info like key type etc?

Upvotes: 2

Views: 329

Answers (2)

Marc B
Marc B

Reputation: 360882

SHOW COLUMNS ... is the oldschool method. For MySQL v5.x installs you can query the database's INFORMATION_SCHEMA database directly for anything related to the databases/tables/fields it's hosting:

select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where (TABLE_SCHEMA = 'your database') AND (TABLE_NAME = 'your table')

There's many more fields with specific field data in that table as well, documented here.

Upvotes: 3

Halcyon
Halcyon

Reputation: 57721

Put it in a while loop to fetch the other column names

$sql_statement = "SHOW COLUMNS FROM " . NAME_TABLE_BASE . $table;
$temp = mysql_query($sql_statement) or die(mysql(error));

while($row = mysql_fetch_array($temp)) {
    print_r($row);
}

Upvotes: 2

Related Questions