Select in an empty table, but still get the column names

Question

I want to do SELECT on an empty table, but I still want to get one record with all the column names. I know there are other ways to get column names from a table, but I want to know if this is possible with some sort of SELECT query.

I know this works when I run it directly in MySQL:

SELECT * FROM cf_pagetree_elements WHERE 1=0; 

But I am using PHP + PDO (FETCH_CLASS). It just returns me an empty object instead of a row with all column names (with empty values). Therefore, for some reason, this request does not work with PDO FETCH_CLASS.

 $stmt = $this->db->prepare ( $sql ); $stmt->execute ( $bindings ); $result = $stmt->fetchAll ( \PDO::FETCH_CLASS, $class ); print_r($result); // Empty object... I need an object with column names 

Does anyone know if there is another way I can try?

Answer 1

To the other answers that are posted about SHOW COLUMNS and the information scheme. The OP clearly said: " I know there are other ways to get the column names from the table , but I want to know if this is possible with some sort of SELECT query."

Learn to read.

---

In any case, to answer your question; No, you can’t. You cannot select a row from an empty table. Even a row with empty values ​​from an empty table.

However, for this you can apply the trick.

Create an additional table called 'dummy' with one column and one row in it:

Table: dummy

 dummy_id: 1 

It's all. Now you can make a select clause as follows:

 SELECT * FROM dummy LEFT OUTER JOIN your_table ON 1=1 

This will always return a single row. However, it does contain a dummy_id column. However, you can simply ignore this, and do whatever you like with the (empty) data.

So this is just a trick to do this with the SELECT statement. There is no default way to do this.

Answer 2

Adding to what w00 answered , there is a solution that doesn't even need a dummy table

 SELECT tbl.* FROM (SELECT 1) AS ignore_me LEFT JOIN your_table AS tbl ON 1 = 1 LIMIT 1 

In MySQL, you can change WHERE 1 = 1 to WHERE 1

Answer 3

You may try:

 SELECT * FROM information_schema.columns WHERE table_name = "cf_pagetree_elements" 

Not sure about your specific PHP + PDO approach (there may be difficulties), but this is the standard way to get column headers (field names).

Answer 4

this will be a list of ANY query columns for PDO drivers that support getColumMeta. I use this with a SQL server and work fine even with very complex queries with aliases, subqueries and joins. Gives me columns even if the result is zero

 <?php // just an example of an empty query. $query =$PDOdb->query("SELECT * from something where 1=0; "); for ($i=0; $i<$query->columnCount(); $i++) { echo $query->getColumnMeta($i)['name']."<br />"; } ?> 

Answer 5

Even without a PDO on the way, the database will not return a structure without at least one row. You can do this and ignore the data line:

 SELECT * FROM cf_pagetree_elements LIMIT 1; 

Or you could just

 DESC cf_pagetree_elements; 

and process one line in a field.

WHERE 1=0 does not work for me. It always returns empty set .

Answer 6

 SHOW COLUMNS FROM cf_pagetree_elements; 

This will give a set of results explaining the structure of the table. You can easily analyze the result using PHP.

Another method is to query the information system schema table:

 SELECT column_name FROM information_schema.columns WHERE table_name='cf_pagetree_elements'; 

Not recommended though!

Answer 7

The latest PDO for SQLSVR definitely works with get column metadata.

Just set up your statement and use it to get an array of useful information:

 $stmt->execute(); $meta= array(); foreach(range(0, $stmt->columnCount() - 1) as $column_index) { array_push($meta,$stmt->getColumnMeta($column_index)); } 

Answer 8

You can use MetaData with;

  $cols = mysql_query("SHOW COLUMNS FROM $tableName", $conn);