How to get the Names of the columns in MYSQL

Question

I'm new to PHP and I want to get the column names of a table.The following code doesn't return any errors, but it doesn't show any columns names either.

Can anyone see what mistake I'm making please?

<?php

$q = "SELECT column_name FROM USER_TAB_COLUMNS WHERE table_name = '!!mytablename!!'";
$real_q = mysql_query($q);
foreach ($r = mysql_fetch_row($real_q) as $taxokey => $taxovalue ) {
    if ($taxokey != 'name') {
    ?>
        <option value="<?php print($taxokey);?>"><?php print($taxovalue);?></option>
    <?php
    }
}

?>

Please see the answer here to select columns of a MySQL table: http://stackoverflow.com/questions/17846387/mysql-show-columns-from-multiple-tables/17846511#17846511 — vee, Aug 08 '13 at 22:31
Anyway, I think the reason I cannot see the result is because of 'mysql_fetch_row' because it shows only one column name. Is there way to show all column names? — user2499003, Aug 08 '13 at 22:59

Preet Sangha · Accepted Answer · 2013-08-08T23:18:14.197

1

Replace your Query with

"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='mytablename'"

edited Aug 08 '13 at 23:18

answered Aug 08 '13 at 22:37

Preet Sangha

64,563
18
145
216

... `AND TABLE_SCHEMA='databasename';` / `AND TABLE_SCHEMA=database();` – Wrikken Aug 08 '13 at 22:49
I think the reason I cannot see the result is because of 'mysql_fetch_row' because it shows only one column name. Is there way to show all column names? – user2499003 Aug 08 '13 at 23:11
@user2499003: instead of `foreach ($r = mysql_fetch_row($real_q) as $taxokey => $taxovalue `, use `while($r = mysql_fetch_row($real_q))`, every column is a new row here, so multiple rows have to be fetched. – Wrikken Aug 09 '13 at 11:11

score 0 · Answer 2 · answered Aug 08 '13 at 22:47

For MySQL you can just do

  $q = "desc mytable;";

That will give you column names and datatype information both.

What is !!mytablename!!? Is that supposed to be your table name or an attempt to put the value from a variable in the string? Because if its the latter, that's your problem.

How to get the Names of the columns in MYSQL

2 Answers2