Topic: mysql if/cmpstr question

i'm having a terrible time getting this query to run:

  $s[0] = "SELECT wlwl_post.comments, wlwl_post.post_id, wlwl_post.date,
           wlwl_post.user_id, wlwl_post.body, wlwl_user.username,
           MATCH(wlwl_post.body) AGAINST ('$string') AS score
           FROM wlwl_post
           LEFT JOIN wlwl_user ON wlwl_post.user_id=wlwl_user.user_id
           WHERE MATCH(wlwl_post.body) AGAINST ('$string')
           ORDER BY score DESC
           UNION
           SELECT wlwl_comment.comment_id, wlwl_comment.post_id, wlwl_comment.date,
           if(strcmp(wlwl_comment.type,'user'),wlwl_comment.name,0), wlwl_comment.body,
           if(strcmp(wlwl_comment.type,'user'),wlwl_user.username,wlwl_comment.name), 
           MATCH(wlwl_comment.body) AGAINST ('$string') AS score
           FROM wlwl_comment
           if(strcmp(wlwl_comment.type,'user'),LEFT JOIN wlwl_user ON wlwl_comment.user_id=wlwl_user.user_id,)
           WHERE MATCH(wlwl_comment.body) AGAINST ('$string')
           ORDER BY score DESC";

for instance, i want the query to do the "LEFT JOIN wlwl_user ON wlwl_comment.user_id=wlwl_user.user_id" if and only if wlwl_comment.type=='user'.

thus i did this:

if(strcmp(wlwl_comment.type,'user'),LEFT JOIN wlwl_user ON wlwl_comment.user_id=wlwl_user.user_id,)

mysql does not like it, though. mysql says:

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'if( strcmp( wlwl_comment . type , 'user' ) , LEFT JOIN wlwl_use

infos for you:

-- 
-- Table structure for table `wlwl_comment`
-- 

CREATE TABLE `wlwl_comment` (
  `comment_id` smallint(6) NOT NULL auto_increment,
  `post_id` smallint(6) NOT NULL default '0',
  `date` int(11) NOT NULL default '0',
  `name` varchar(12) NOT NULL default '',
  `type` enum('user','guest') NOT NULL default 'guest',
  `ip` varchar(15) NOT NULL default '',
  `body` longtext NOT NULL,
  PRIMARY KEY  (`comment_id`),
  KEY `post_id` (`post_id`),
  FULLTEXT KEY `body` (`body`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=642 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `wlwl_post`
-- 

CREATE TABLE `wlwl_post` (
  `post_id` smallint(6) NOT NULL auto_increment,
  `date` int(11) NOT NULL default '0',
  `comment_date` int(11) NOT NULL default '0',
  `comments` smallint(6) NOT NULL default '0',
  `user_id` smallint(6) NOT NULL default '0',
  `body` longtext NOT NULL,
  PRIMARY KEY  (`post_id`),
  KEY `user_id` (`user_id`),
  FULLTEXT KEY `body` (`body`)
) TYPE=MyISAM AUTO_INCREMENT=268 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `wlwl_user`
-- 

CREATE TABLE `wlwl_user` (
  `user_id` smallint(6) NOT NULL auto_increment,
  `username` varchar(12) NOT NULL default '',
  `password` varchar(40) NOT NULL default '',
  `moderator` enum('0','1') NOT NULL default '0',
  `unsketch` int(11) NOT NULL default '0',
  `profile` longtext NOT NULL,
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `password` (`password`,`username`)
) TYPE=MyISAM AUTO_INCREMENT=9 ;

Re: mysql if/cmpstr question

I don't use MySQL so I'm just curious as of why you have "MATCH(wlwl_post.body) AGAINST ('$string') AS score" in SELECT.

About your problem: You've added a comma inside the parenthesis at the end of the line.

<wintellect> NetBSD users are smart enough to accept that there's no 3D support tongue

Re: mysql if/cmpstr question

I think the parenthesis needs to be there, IF() accepts three arguments.

the MATCH/AGAINST is a full-text search

Re: mysql if/cmpstr question

I see why you put it in WHERE but what does it do in SELECT ?

I don't know why it's not working then.
I'm not a MySQL user and I find it weird to be able to use ifs in queries. That's not standard so I don't like it smile

<wintellect> NetBSD users are smart enough to accept that there's no 3D support tongue