Hello everybody, would like to thank you for your time in advance, I really appreciate it.
Anyway, I'm not really the best with mySQL, but I'm trying to implement a fulltext search using a few tables that I have set up. Here's an overview:
Schemata : Rate
Okay. I want to allow users to rate a product with pName and pCategory on a scale of one to ten (pRating) while giving a comment (pComment). I want them to be able to post as many comments as they want (for questions, arguments, etc), but only vote once. When they make a vote, I enter their e-mail in the 'votes' table, and search that table whenever they try to make another vote for the same product. If they wish only to make a comment, the comment is associated with the reviewID in the pReview field. Table 'init' allows temporary data storage while the user is sent an e-mail with an activation code. After the code is entered, the data is transferred from init to the corresponding tables, and deleted from int.
I want to be able to search the pName and pCategory field in table 'fin' and the pReview field in table 'rev' at the same time (so users can search the site for the information that they want that would be available in any of these areas).
I'm giving the following command at the moment:
Where $t is my search term.
$query = "SELECT *,
MATCH(rev.pReview, fin.pName, fin.pCategory)
AGAINST('$t') AS relevance
FROM (rev, fin)
WHERE MATCH(rev.pReview, fin.pName, fin.pCategory)
But don't get the proper results (relevance is always 1 and results that shouldn't be in the listing often turn up).
I did some reading on the internet about similar issues, but found everybody saying that this was impossible unless my tables all had the same columns and I just combined the results.
I don't want to set up a table that repeats this information for every row, as I want this to take up as little space as possible (I could afford to go higher, but I strive for code efficiency).
I'm wondering if someone out there knows how this stuff is normally done. Is my database scheme completely off base? Should it be easier than that? Frankly, I've been working on this all night and am starting to reevaluate my worth as a programmer . But seriously, can somebody lend me a hand?