How to select from single empty table

ppowell

Based upon an article I read in http://archives.neohapsis.com/archives/mysql/2004-q1/0337.html I am trying to retrieve an "empty row" of fields from a table that may be empty.

If I have this query:

SELECT id, fname, mi, lname FROM folks LIMIT 1

This will return 1 single row whereby I have the table columns within an object resultset I can manipulate in PHP. However, if the table is empty I have nothing and there is not object for PHP to manipulate, which is required for the code I'm building (I'm doing a logical join between database metadata and objects in a directory that may/may not sync).

How would I then write my query to always produce 1 row consisting of all fields I need even if the table is empty?

Thanx
Phil

eoghain

I'm confused as to what you are trying to do. The link that you posted goes to a thread that explains how to use a LEFT OUTER JOIN to retireve rows from 1 table even if there are no rows in the second table.

If all you want is a result set with no data re-write your query to be this:

select null as id,
       null as fname,
       null as mi,
       null as lname
  from folks
 limit 1

This will get 1 empty result set with the named fields. But I still don't get what this is for, if you already know what the fields are you can do all of your synching in code.

ppowell

Yep that's exactly what I did. What stopped me before was that I was unaware that in PHP you can set an object the same way you can set everything else - you just set it:

foreach (array('id', 'fname', 'lname') as $val) $result[0]->$val = '';

Thus, no need for any database pooling, connection, querying, anything.

Phil