autopopulate a category field by using keywords from another field?


Hi all,

I have two problems that got be baffled.

#1 - I need to add a category to each row in a MySql DB. issue is, the DB is large and grows by about 20,000 rows per week… so categories can’t be added manually.

What I’d like to do is have a category field within the DB automatically populated by script that searches for keywords from another field.

For Ex:
the ‘Title’ field contains: “Three Hours of Professional Residential Cleaning from The Cleaning Express”

So, I’d like to add the keyword ‘Residential Cleaning’ to a list that would populate this row’s category as ‘Services’.
The list could go on and on for about 7 parent categories. It may pick up the word ‘Jewelery’ and put the row into a ‘shopping’ category etc etc…
I’d even like to eventually add more category options under the parent as well.

#2 - The DB may contain several instances of a row, (deal) as above, but in different cities. So all the fields are identical, except for the city field. The revenues from all cities are added together. So if the deal sells $1000 in A city, and $9000 in B city… each city listing will show sales of $10,000.

Problem is, when trying to calculate total revenue, it add all the cities together, showing a total revenue of $20,000… when it’s actually $10,000.

I can’t delete the duplicate deal titles because they are unique by city and it is important to keep that for various search metrics.

So… how would I go about finding a way to get an accurate total revenue? I’d need to search the DB, without including the city and not count duplicate titles? If so… no idea how to do that.

Thanks for any help on this. I’m really stuck