Delixe

I've indented your code to show the problem. You are doing the group by on the individual queries before you union them. This will still give you duplicates. The group by should be around the entire query, including the union, not on each component of the union. How do I apply the GROUP BY to both UNIONed SELECTS, would you mind doing it for me, I tried. I got rid of the first GROUP BY but I then get the error message: #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

I tried: SELECT id, itm_desc, itm_link, itm_title, itm_categories, itm_author, itm_date, feed_id, cat_id, archived_on, MAX(MATCH(itm_desc, itm_title, itm_link, itm_author, itm_categories) AGAINST('XSS')) as score FROM fmc_archive_dev WHERE ((MATCH(itm_desc, itm_title, itm_link, itm_author, itm_categories) AGAINST('XSS')) AND (archived_on > 1181277356) AND (cat_id = 3)) GROUP BY id, itm_desc, itm_link, itm_title, itm_categories, itm_author, itm_date, feed_id, cat_id, archived_on UNION SELECT id, itm_desc, itm_link, itm_title, itm_categories, itm_author, itm_date, feed_id, cat_id, archived_on, MAX(MATCH(itm_desc, itm_title, itm_link, itm_author, itm_categories) AGAINST('WAF')) as score FROM fmc_archive_dev WHERE ((MATCH(itm_desc, itm_title, itm_link, itm_author, itm_categories) AGAINST('WAF')) AND (archived_on > 1181320556) AND (cat_id = 1 OR cat_id = 3 OR cat_id = 4 OR cat_id = 11 OR cat_id = 12 OR cat_id = 13 OR cat_id = 14 OR cat_id = 15 OR cat_id = 16 OR cat_id = 23 OR cat_id = 43 OR cat_id = 44)) GROUP BY id, itm_desc, itm_link, itm_title, itm_categories, itm_author, itm_date, feed_id, cat_id, archived_on ORDER BY score DESC SELECT id, itm_desc, itm_link, itm_title, itm_categories, itm_author, itm_date, feed_id, cat_id, archived_on, MAX( MATCH ( itm_desc, itm_title, itm_link, itm_author, itm_categories ) AGAINST ( 'XSS' ) ) AS score FROM fmc_archive_dev WHERE ( ( MATCH ( itm_desc, itm_title, itm_link, itm_author, itm_categories ) AGAINST ( 'XSS' ) ) AND ( archived_on >1181277356 ) AND ( cat_id =3 ) ) GROUP BY id, itm_desc, itm_link, itm_title, itm_categories, itm_author, itm_date, feed_id, cat_id, archived_on UNION SELECT id, itm_desc, itm_link, itm_title, itm_categories, itm_author, itm_date, feed_id, cat_id, archived_on, MAX( MATCH ( itm_desc, itm_title, itm_link, itm_author, itm_categories ) AGAINST ( 'WAF' ) ) AS score FROM fmc_archive_dev WHERE ( ( MATCH ( itm_desc, itm_title, itm_link, itm_author, itm_categories ) AGAINST ( 'WAF' ) ) AND ( archived_on >1181320556 ) AND ( cat_id =1 OR cat_id =3 OR cat_id =4 OR cat_id =11 OR cat_id =12 OR cat_id =13 OR cat_id =14 OR cat_id =15 OR cat_id =16 OR cat_id =23 OR cat_id =43 OR cat_id =44 ) ) GROUP BY id, itm_desc, itm_link, itm_title, itm_categories, itm_author, itm_date, feed_id, cat_id, archived_on ORDER BY score DESC LIMIT 0 , 300 It gave me duplicates still: ROW: 128388 <p>I’ve been thinking about this for a long ... http://ha.ckers.org/blog/20070608/the-virtues-of-w... The Virtues of WAF Egress a:2:{i:0;s:3:"XSS";i:1;s:9:"Webappsec";} RSnake 1181324069 57 3 1181325343 8.03860855102539 And DUPLICATE ROW: 128388 <p>I’ve been thinking about this for a long ... http://ha.ckers.org/blog/20070608/the-virtues-of-w... The Virtues of WAF Egress a:2:{i:0;s:3:"XSS";i:1;s:9:"Webappsec";} RSnake 1181324069 57 3 1181325343 4.19421482086182 Notice they both have different scores: 8.03860855102539 and 4.19421482086182 I'd like the result to pick out the row with 8.03860855102539 as the score and get rid of the item with the score of 4.19421482086182

No it needs get all the columns from the fmc_archive_dev table and have a column for score. What I have is a database full of content but I want to search the content on numerous keywords, so what I end up doing is UNION-ing a bunch of SELECTs together and score the items. As you know MySQL will provide a score based on the keyword match and will make another column for it however if you're matching against different keywords obviously you will get a different score per keyword. What ends up happening is a new column with different scores so I virtually get duplicate items that have the same columns but with different scores making them _not_ unique. What I want is to get all the columns with unique items, if any duplicate item results, I want to filter out the lowest scored item of the duplicate items. It is possible I know for their to be more than 2 duplicate items. I hope this makes sense.

I need all the data and the score. The score should be relative to all the data gained from all of the unions. I am thinking that I may not be able to UNION and will have to customize it without UNIONs in order to get all the data, scored, and unique.

You have to do ORDER BY city DESC DESC can be ASC, etc.

SELECT * , ( MATCH ( itm_desc, itm_title, itm_link, itm_author, itm_categories ) AGAINST ( 'XSS' ) ) AS score FROM fmc_archive_dev WHERE ( ( MATCH ( itm_desc, itm_title, itm_link, itm_author, itm_categories ) AGAINST ( 'XSS' ) ) AND ( archived_on >1181277356 ) AND ( cat_id =3 ) ) UNION SELECT * , ( MATCH ( itm_desc, itm_title, itm_link, itm_author, itm_categories ) AGAINST ( 'WAF' ) ) AS score FROM fmc_archive_dev WHERE ( ( MATCH ( itm_desc, itm_title, itm_link, itm_author, itm_categories ) AGAINST ( 'WAF' ) ) AND ( archived_on >1181320556 ) AND ( cat_id =1 OR cat_id =3 OR cat_id =4 OR cat_id =11 OR cat_id =12 OR cat_id =13 OR cat_id =14 OR cat_id =15 OR cat_id =16 OR cat_id =23 OR cat_id =43 OR cat_id =44 ) ) btherl: Thank you so much, if you take a look in my query you'll notice that different keywords have a score, the rows have different scores which make them unique. Is there anyway to apply a score to all of those on a global level so I do not get duplicate rows?

UNION is MySQL is not supposed to provide duplicate rows however: Produces duplicate columns, 3 rows in total, 2 with the same id. I want unique rows, what am I doing wrong?

I found one way which is great with speeds: SELECT * FROM `fmc_archive_dev` WHERE (MATCH(itm_desc) AGAINST('Zune') OR MATCH(itm_desc) AGAINST('xss') OR MATCH(itm_desc) AGAINST('ajax') OR MATCH(itm_desc) AGAINST('rsnake') OR MATCH(itm_desc) AGAINST('virus') OR MATCH(itm_desc) AGAINST('porn') OR MATCH(itm_desc) AGAINST('final fantasy') OR MATCH(itm_desc) AGAINST('domain') OR MATCH(itm_desc) AGAINST('web 2.0') OR MATCH(itm_desc) AGAINST('web development') OR MATCH(itm_desc) AGAINST('steve nash') OR MATCH(itm_desc) AGAINST('reverse engineering')) AND archived_on > 1175150209 How do you add multiple columns to the MATCHES? Is there a way to optimize this more?

So I have a quick question, what do you think is the best way to fully integrate PHP and AJAX. I used to use a 'raw' variable in my queries and spit out the content or is it better to just have a folder named /js/ and use those php files only to spit out the raw content for an AJAX request? How do you do it and what do you recommend?

$sql = "SELECT * FROM $tbl_members WHERE email = \"$email\""; Might be a fantastic idea to escape your variables in a MySQL query to avoid SQL injection.