Thanks Chris
I tweaked the query and database a bit so I only do 1 query and use PHP with
the search results twice:
- initially to calculate the number of records which are in the RSS Feed already
- if that number is less than 15 I make the new rss_feed.xml file using the
same search results because the SELECT queried for the information I am using
for this RSS Feed.
Ron
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
From: Ron Piggott
Sent: Sunday, December 19, 2010 11:38 PM
To: php-db@lists.php.net
Subject: SUM() Math in mySQL
I am working on the query below. It’s purpose is to manage the RSS Feed for
the site.
The part of the query I am struggling with is:
SUM(`include_in_rss_feed`) AS current_rss_feed
What I need is the total value of *ALL* the `include_in_rss_feed` , not the
specific row.
`include_in_rss_feed` is an integer. It only ever has a value of 1
My thinking is if the total value of `include_in_rss_feed` and if it is less
than 15 the RSS Feed needs to be regenerated because of recent updates to the
site.
Thanks in advance for helping me. This is for a clients site, not mine. I
really appreciate it.
Ron
SELECT new_rss_feed_listings.reference, new_rss_feed_listings.last_update,
SUM(`include_in_rss_feed`) AS current_rss_feed,
`ministry_profiles`.`organization`, `ministry_profiles`.`city`,
`ministry_profiles`.`province_state`, `ministry_profiles`.`postal_zip_code`,
`ministry_profiles`.`country`
FROM (
(
SELECT `reference` , `last_update`
FROM `ministry_profiles`
WHERE `live` =1
ORDER BY `last_update` DESC
LIMIT 15
)
UNION ALL (
SELECT `ministry_profiles_reference` , `last_update`
FROM `ministry_profiles_activity`
WHERE `live` =1
GROUP BY `ministry_profiles_reference`
ORDER BY `last_update` DESC
LIMIT 15
)
UNION ALL (
SELECT `ministry_profile_reference` , `last_update`
FROM `ministry_profiles_listing_details`
WHERE `live` =1
GROUP BY `ministry_profile_reference`
ORDER BY `last_update` DESC
LIMIT 15
)
UNION ALL (
SELECT `ministry_profiles_reference` , `last_update`
FROM `ministry_profile_categories`
WHERE `live` =1
GROUP BY `ministry_profiles_reference`
ORDER BY `last_update` DESC
LIMIT 15
)
) AS new_rss_feed_listings
LEFT OUTER JOIN `rss_feed_listings` ON new_rss_feed_listings.reference =
`rss_feed_listings`.`ministry_profiles_reference`
INNER JOIN `ministry_profiles` ON `ministry_profiles`.`reference` =
new_rss_feed_listings.reference
ORDER BY new_rss_feed_listings.last_update DESC LIMIT 15
The Verse of the Day
“Encouragement from God’s Word”
http://www.TheVerseOfTheDay.info
