-1

Many people will think this is a duplicate question and answers to such question is already given.But I have a different problem, here I don't know the number of columns and name of columns!

I have a text input type in html in which a user can directly manipulate database. User can query any table in database. All tables have different column names and number of columns. And i can't use 'describe' and 'show column' sql statement since name of the column is unknown.

All the answers to this question considers programmer already know column name and number of columns in table.

So the question is:

  1. how to get number of columns in table?

  2. how to get column names of table to display it in table heading tag?

Pritam Parab
  • 3
  • 4
  • 1
    Possible duplicate of [MySQL query to get column names?](https://stackoverflow.com/questions/4165195/mysql-query-to-get-column-names) – ssemilla Nov 08 '18 at 15:30
  • @ssemilla I guess you didn't understand the question. There are many tables in the database. User can query any table at runtime, so name of the table is unknown. The possible duplicate question you showed assumes the name of the table is already known! – Pritam Parab Nov 08 '18 at 15:46
  • You should have specified that the user is also unaware of the table names. In any case, you just need to query the table names e.g. `SELECT table_name FROM information_schema.tables;` and combine this with the answers in https://stackoverflow.com/questions/8334493/get-table-names-using-select-statement-in-mysql – ssemilla Nov 08 '18 at 16:00

3 Answers3

1

You can use DESC TABLE_NAME.

Itere the return to know the amount of fields.

8biT
  • 135
  • 1
  • 8
  • Thank you for replying, but I found another way of getting number of fields in table. That is, mysqli_num_fields($result). Now all i want to get get headers of this columns – Pritam Parab Nov 08 '18 at 15:51
  • DESC "TABLE_NAME" returns some records and one of them [Field] is the name of these columns. Does not that answer your problem? – 8biT Nov 08 '18 at 16:00
1

Use SHOW COLUMNS FROM your_table_name_here and when you fetch the results the count of the number of rows will tell you how many columns there are.

Part of the data that is returned includes in the name of the columns which you may use for your table headings.

$ColsQ = $yourdb->prepare('SHOW COLUMNS FROM ' . $your_table_name_here);
$ColsQ->execute();
$ColsD = $ColsQ->fetchAll(PDO::FETCH_ASSOC);

echo 'There are ' . count($ColsD) . ' columns in the table';

foreach ($ColsD as $Column) {
    echo 'Column name is ' . $Column['Field'];
}
Dave
  • 5,108
  • 16
  • 30
  • 40
  • Thank you for replying, but still this does't solve my problem. It expects the programmer knows name of a column already. In my website a user can query any table in the database, so the table name is not known till user types the query. – Pritam Parab Nov 08 '18 at 15:50
  • No, it does not. The first query retrieves the names of the columns. The foreach loop reports what each name is. Have you tried what I posted? – Dave Nov 08 '18 at 16:05
  • The only piece of information needed, which you said your web site asks for, is the name of a table. – Dave Nov 08 '18 at 16:20
0

So i got the answer of my question from php documentation! Here i will post snippet of my code which executes 'select' SQL statement written run-time which shows header plus records of table from result i got from executing query. 

      if(strcmp($words[0], "select")==0) //for making sure its select statement
    {
        $result= mysqli_query($conn, $sql);
        if($result)
        {
            echo '<table border=1 style="border-collaspe=collaspe" class="table table-striped table-bordered table-hover dataTable no-footer">';
            echo '<tr>';
            for($i=0;$i<mysqli_num_fields($result);$i++)
            {
                $column_info=mysqli_fetch_field_direct($result, $i); 
               /*this function returns all the information about table metioned in the query.
               variable i over here refers to field no*/
                echo "<th>".$column_info->name."</th>"; //here fetching only name from whole information
            }
            echo '</tr>';
            while ($row = mysqli_fetch_array($result))
            {
                echo '<tr>';
                for($i=0;$i<mysqli_num_fields($result);$i++)
                {
                    echo '<td>'.$row[$i].'</td>';
                }
                echo '</tr>';
            }
            echo '</table>';
        }
        else
        {
            echo "<script>alert('Error occured, Please check your syntax or internet connection')</script>";
        }
    }

For detailed information: PHP Documention page for mysqli_fetch_field_direct ( mysqli_result $result , int $fieldnr ) function

Pritam Parab
  • 3
  • 4