I have two tables linked with a common identifier called "ShowID", and I need to do fulltext searching. I wasn't able to figure out how to construct my select query just by visiting dev.mysql.com, but I'm sure it's got to be doable.
The first table is called Guests, and I need to do a fulltext search of the fields "Expertise", "KnownAs", and "Bio". The second table is called Shows, and I need to do a fulltext search of the fields "Title", "Quote", "Intro", "Summary1", "Summary2", "Hook", and "TipList".
I want to return one result, picked randomly, based on a seed value that will change once per day. I also need to calculate the number of rows that would have been returned if the Limit were not used. The columns to be returned are "RadioShowID", "PersonID", and "TextReference" in Guests, and then from Shows I need "Hook", "Segment1", "Title", and "Redirect".
I hope that's all the information someone will need. I'm using php, so I thought perhaps I could use "ORDER BY RAND('.strtotime(date('Y-m
-d')).')" to randomize the result based on the day the search is run, but I wasn't sure if FULLTEXT, with it's own ordering built-in, would even be compatible with using the ORDER BY modifier at the end of the query.
I am using MySQL version 4.1.22.
Start Free Trial
