Select distinct and all
Hello everybody. I may be asking a question with an answer that's painfully obvious, but I'm pretty new with this, so please excuse me.
I've written a php script to search my database, but I need to figure out a way to filter out the duplicates by the auto increment 'ID' value. Here are my tables:
[cont]
ID - value based on the ID value in stat.
pName
pCategory
pReview
pDatePosted
pEmail
pRating
[stat]
ID - autoincrement
pRating
pViewed
pDateLastVote
pDateLastComment
pDateLastView
Stat contains a list of posts, each one with the unique 'ID' identifier. Basic statistics are stored in the stat table, while the cont table contains each post.
For instance, if I were to make a post:
pName = pear
pCategory = fruit
pReview = 'tasty things'
pRating = 9 // 1-10 scale.
ID = whatever ID in stat table.
That information would all be available in the cont table, while the information in the stat table would reflect the following:
ID - Name and Category ID.
pRating = average rating
pViewed = how many times viewed.
Multiple comments under the same ID can be made in the cont table...
Well, I hope that I've described that to satisfaction... Anyway, I created my search engine to search through the pName, pCategory, and pReview fields for references to a search term, it then displays the following results:
Name Rating Posts Views Last Activity
Pears
Fruits 6 1 1 Wed Feb 16 12:48:04 Mountain Standard Time 2005
Pears 2
Fruits 6 1 1 Wed Feb 16 13:16:06 Mountain Standard Time 2005
Pears 3
Fruits 6 1 1 Wed Feb 16 13:16:15 Mountain Standard Time 2005
Pears 4
Fruits 6 1 1 Wed Feb 16 13:16:25 Mountain Standard Time 2005
Pears 5
Fruits 6 1 1 Wed Feb 16 13:16:39 Mountain Standard Time 2005
Pears 6
Fruits 5 2 1 February 16, 2005, 2:37 pm
Pears 6
Fruits 5 2 1 February 16, 2005, 2:37 pm
(The date has been reformatted to the bottom value. I'm using varchar and the php date functions, as I don't really need to calculate anything regarding the date...
As you can see, all of the comments under a given name and category are displayed twice, and I want them to be displayed only once.
The above was created via the following command:
$query = "SELECT * ,
MATCH(pName, pCategory, pReview) AGAINST('$t' in boolean mode) AS relevance
FROM cont
WHERE MATCH(pName, pCategory, pReview) AGAINST('$t' in boolean mode)
order by relevance desc limit ".$b.", ".$u."
";
I have tried many different variations, but I need to be able to do something like:
$query = "SELECT DISTINCT ID FROM(SELECT * ,
MATCH(pName, pCategory, pReview) AGAINST('$t' in boolean mode) AS relevance
FROM cont
WHERE MATCH(pName, pCategory, pReview) AGAINST('$t' in boolean mode)
order by relevance desc limit ".$b.", ".$u.")
";
But of course, the above gives me an error. If anybody can help I'd really appreciate it. Thanks for your time,
Sincerely,
James