Select on empty table but still get column names

Question

I want to do a SELECT on an empty table, but i still want to get a single record back with all the column names. I know there are other ways to get the column names from a table, but i want to know if it's possible with some sort of SELECT query.

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

SELECT * FROM cf_pagetree_elements WHERE 1=0;

But i'm using PHP + PDO (FETCH_CLASS). This just gives me an empty object back instead of an row with all the column names (with empty values). So for some reason that query doesn't 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

Anyone any idea if there's another method that i can try?

Answer 1

Adding on to what w00 answered, there's 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 just WHERE 1

Answer 2

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

Learn to read.

---

Anyway, to answer your question; No you can't. You cannot select a row from an empty table. Not even a row with empty values, from an empty table.

There is however a trick you can apply to do this.

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

Table: dummy

dummy_id: 1

That's all. Now you can do a select statement like this:

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

This will always return one row. It does however contain the 'dummy_id' column too. You can however just ignore that ofcourse and do with the (empty) data what ever you like.

So again, this is just a trick to do it with a SELECT statement. There's no default way to get this done.