Fix for SQL error on MYSQL 3.23

Here is the note from the README: (I just noticed it was in 4.5 README but not HEAD, so I just added it to HEAD too.)

The workspace module is not compatible with MySQL 3 because it uses
a UNION clause, which is supported by MySQL 4 and higher only. The
UNION clause is necessary to unify nodes and comments (which are
not nodes).

There is a workaround for MySQL 3's lack of a UNION clause:

http://www.google.com/search?&q=union+%22mysql+3%22

but I have no intentions of implementing this myself because I use
MySQL 4.

workspace.module,v 1.4.2.6 2005/02/05 00:53:11 jvandyk Exp

Hi folks,

first I tried to fix the 'workspace' module's problem with MySQL 3.x using the suggested UNION workaround (see jinxidoru.com/tutorials/union.html).
However, the workaround described on that page is not appropriate for the needs of the 'workspace' module, as far as I can judge it. One reason is that the problematic SQL statement in the method workspace_list() does already use an INNER JOIN which can not be combined with the UNION workaround which also uses JOINs.
The other problem with the workaround is, that in the module's UNION query two tables are used which do not have a compatible data structure.

So I chose a different approach, which not only led to the desired result, but moreover is very simple and does not need a "dummy" database table as the workaround does.

I renamed the 'workspace' module's method workspace_list() to _callbackworkspace_list() and changed it slightly:
function _callback_workspace_list($sql) {
global $user;
$maxnodes = $user->workspaces ? $user->workspaces['default']['maxnodes'] : 50;
$maxfilenames = $user->workspaces ? $user->workspaces['default']['maxfilenames'] : 50;
if (!is_numeric($maxnodes)) {$maxnodes = 50;}
if (!is_numeric($maxfilenames)) {$maxfilenames = 50;}

$comments_enabled = module_exist('comment');

$output = '';
// the rest is unchanged
}

Then I added a new method workspace_list() , which is very simple:
function workspace_list() {
global $user;
$output = '';

$output .= workspace_addform();
$output .= '

';

$sql_1 = "SELECT n.nid, n.type, 0 AS cid, n.title, n.status, n.changed, s.comment_count, 1 AS node ";
$sql_1 .= "FROM {node} n LEFT OUTER JOIN {node_comment_statistics} s ON n.nid = s.nid ";
$sql_1 .= "WHERE n.uid = $user->uid ";
$output .= '

'.t('Comments').'

';
$output .= _callback_workspace_list($sql_1);

$sql_2 = "SELECT c.nid AS nid, '' AS type, c.cid, c.subject AS title, c.status, c.timestamp AS changed, c.pid, 0 ";
$sql_2 .= "FROM {comments} AS c ";
$sql_2 .= "WHERE c.uid = $user->uid";
$output .= '

'.t('Publications').'

';
$output .= _callback_workspace_list($sql_2);

return $output;
}

And that's all.

The difference in comparison to the original 'workaround' module's output is, that the user's comments and his stories, pages etc. are now in separate tables.

Hope this helps some Drupal friends!

Bye,
Vishnu

Hi Vishnu..

can you prepost your fix properly..I'm looking for a fix or the same problem

Dub