Get column names with SHOW COLUMNS

[binaryWeapon]

How would I retrieve just the column names using SHOW COLUMNS or DESCRIBE in PHP? It doesn't matter if its as mysql_result_assoc or mysql_result_array or anything else, just so long as I can retrieve the values. Using mysql_result_array it only seems to retrieve information about the first column.

I have this: $result = mysql_query("SHOW COLUMNS FROM ".$contentGroup);
if (!$result) {
echo 'Could not run query: ' . mysql_error();
}
$fieldnames=array();
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
// What to put here?
}
}
I just need to know how to navigate that array to retrieve the field names. I thought it would be $row["field"] or $row[0] but neither worked.

[Fou-Lu]

I'm not sure what the trouble is that code is correct. If you do a print_r($row) within you're loop it should list each of the table fields.
The code you're looking for should just be $fieldnames[] = $row['Field'];. That may be the trouble you're having now that I see it, I'm pretty sure that show columns returns keys that are title cased, so you need to use uppercase Field instead of field. Try that out, see if that one works for you.

[oesxyl]

How would I retrieve just the column names using SHOW COLUMNS or DESCRIBE in PHP? It doesn't matter if its as mysql_result_assoc or mysql_result_array or anything else, just so long as I can retrieve the values. Using mysql_result_array it only seems to retrieve information about the first column.

I have this: $result = mysql_query("SHOW COLUMNS FROM ".$contentGroup);
if (!$result) {
echo 'Could not run query: ' . mysql_error();
}
$fieldnames=array();
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
// What to put here?
}
}
I just need to know how to navigate that array to retrieve the field names. I thought it would be $row["field"] or $row[0] but neither worked.
this kind of problem are easy to solve using print_r or var_dump. A print_r($row) will show you the keys and the value of each item so further you can use the key to retrive value.

column name in mysql is not case sensitive but I guess in php is, so must work with $row['Field'] as Fou-Lu said.regards

[binaryWeapon]

Alright thanks, using $row["Field"] works, I didn't realize it was case-sensitive.

Thanks! :)

[drooh]

Here is a script that may be helpful for some stumbling upon this post. This will allow you to see your DB names, Tables names and Field names.

Simply enter your mysql host, username and password

<style>
h1, a, span {
font-family: arial;
}
a, span {
font-size: 11px;
}
h1 {
margin: 0px 0px 5px 0px;
font-size: 14px;
}
</style>
<?
$dbhost = 'l';
$dbuser = '';
$dbpass = '';
$con = mysql_connect($dbhost, $dbuser, $dbpass);

print '<h1>Databases</h1>';
$db_list = mysql_list_dbs($con);
$cnt = mysql_num_rows($db_list);
for($i=0;$i<$cnt;$i++){
print '<a href="?db='.mysql_db_name($db_list, $i).'">'.mysql_db_name($db_list, $i).'</a><br />';
}
print '<hr />';

if(isset($_GET['db'])){
print '<h1>Tables</h1>';
$db = addslashes($_GET['db']);
$sql = 'SHOW TABLES FROM `'.$db.'`';
$result = mysql_query($sql);
while ($row = mysql_fetch_row($result)) {
print '<a href="?db='.$db.'&table='.$row[0].'">'.$row[0].'</a><br />';
}
mysql_select_db($db,$con);
mysql_free_result($result);
print '<hr />';
}

if(isset($_GET['table']) && isset($_GET['db'])){
print '<h1>Fields</h1>';
$table = addslashes($_GET['table']);
$result = mysql_query('SHOW COLUMNS FROM `'.$table.'`');
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
print '<span>'.$row['Field'].'</span><br />';
}
}
mysql_free_result($result);
}
?>

[Old Pedant]

Above post works, but if you wanted to get more information, you'd be better off simply using the data available in the INFORMATION_SCHEMA database.

[drooh]

could u show an example with that?

[Old Pedant]

Easier to tell you to experiment yourself.

Using a MySQL DB tool, connect to your server.

Then change to the INFORMATION_SCHEMA database (or you can just prefix all table names in your queries with information_schema.).

And then just examine some of the tables in that database.

In particular, look at these tables:
-- shemata
-- tables
-- columns

For example, do DESCRIBE COLUMNS (or DESCRIBE information_schema.COLUMNS if you didn't say "USE").

That shows you that, for each column in every table on that server, you can find
-- catalog name
-- schema name
-- table name
-- column name
-- column order
-- column type
-- column default
and more.

So, for example, you could do:

SELECT * FROM information_schema.columns
WHERE table_schema = '...your database name..' AND table_name = '...your table name...'

But also look at some of the other tables in that database. A *lot* of information available there.