SELECT fulltext problems...

[Hilly_2004]

Hey guys,

Wondered if you could help me, Im doing a full text search and my select statement looks like this:

[code]
        $sql = "SELECT Submissions.Submission_ID, Submissions.Conference_ID, Submissions.Subject_ID, Submissions.Title, Submissions.Summary, Submissions.Keywords, Submissions.Timestamp, forum_members.M_FIRSTNAME, forum_members.M_SURNAME, forum_members.M_NAME, forum_members.MEMBER_ID
        
               MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywords)
               AGAINST ('$searchstring') AS score FROM Submissions LEFT JOIN forum_members on Submissions.MEMBER_ID = forum_members.MEMBER_ID
              
               WHERE MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywords)
               AGAINST ('$searchstring') ORDER BY score DESC";
[/code]

Problem is I only want it to select the current conference (which is stored in a variable called $CurrentConfID). So normally I would put "WHERE Submissions.Conference_ID = '$CurrentConfID'" somewhere in there but it doesn't seem to work anywhere I put it, any help?

[Hilly_2004]

Please?

[Hilly_2004]

Sorry guys, don't want to seem like Im being annoying but any help whatsoever would be really really good.

[Hilly_2004]

[img src=\"http://www.phpfreaks.com/forums/style_images/1/folder_post_icons/icon9.gif\" border=\"0\" alt=\"IPB Image\" /]

[hitman6003]

Try this:

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] Submissions.Submission_ID,
Submissions.Conference_ID,
Submissions.Subject_ID,
Submissions.Title,
Submissions.Summary,
Submissions.Keywords,
Submissions.Timestamp,
forum_members.M_FIRSTNAME,
forum_members.M_SURNAME,
forum_members.M_NAME,
forum_members.MEMBER_ID
MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywords) AGAINST ([color=red]'$searchstring'[/color]) [color=green]AS[/color] score
[color=green]FROM[/color] [color=orange]Submissions[/color] [color=green]LEFT[/color] [color=green]JOIN[/color] forum_members on Submissions.MEMBER_ID [color=orange]=[/color] forum_members.MEMBER_ID
[color=green]WHERE[/color] MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywords) AGAINST ([color=red]'$searchstring'[/color]) [color=blue]AND[/color] Submissions.Conference_ID [color=orange]=[/color] [color=red]'$CurrentConfID'[/color]
[color=green]ORDER BY[/color] score [color=green]DESC[/color] [!--sql2--][/div][!--sql3--]

[Hilly_2004]

Thanks for the reply but thats not working, the error I get is:

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 'MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywo......

[Hilly_2004]

Im getting there....Ive changed it to:

[code]
        $sql = "SELECT Submissions.Submission_ID, Submissions.Conference_ID, Submissions.Subject_ID, Submissions.Title, Submissions.Summary, Submissions.Keywords, Submissions.Timestamp, forum_members.M_FIRSTNAME, forum_members.M_SURNAME, forum_members.M_NAME, forum_members.MEMBER_ID
FROM Submissions LEFT JOIN forum_members on Submissions.MEMBER_ID = forum_members.MEMBER_ID
  
WHERE Submissions.Conference_ID = '$CurrentConfID' AND
MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywords)
   AGAINST ('$searchstring')";
[/code]

But Ive got a problem ordering them by score now.

[Hilly_2004]

Nah still not having any luck...