My project has one table that is a list of keywords that apply to
another table "Locations". At intervals I will run a script to count
the number of records in "Locations" that each keyword uses and store
that number in the "Keywords" table, so that I can show users how
many locations each keyword applies to. Another table "kw_link"
holds the actual references between the two.
I've figured out a SELECT statement that yields the proper information:
SELECT kw_link.kw_id, keywords.english, COUNT(locations.name)
FROM locations, keywords, kw_link
WHERE locations.locationid = kw_link.locationid AND keywords.kwid =
kw_link.kw_id
GROUP BY kw_link.kw_id;
...but it appears that I can't use that to UPDATE the Keyword table.
Ideally I'd simply update the LocationCount field in Keywords with
the appropriate count.
The solution would appear to be that I rewrite the Keyword table each
time I run the counting function, using an INSERT statement
incorporating the SELECT as above.
Or would I be better off writing the 'count' data to a temporary
table and then UPDATE-ing from there?
Or is there a better solution altogether? I know I could do the
"count" each time the user looks at the keyword list, but that seems
wasteful of processing time - instead I plan to only run the count
once per day.
Thank you,
Nelson
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
