Is it possible to extract information from a count query?
I'm going to be querying a lof of very big database tables as part of an online encyclopedia. It tentatively looks like I'll have to make at least three major UNION ALL queries.
The first query is a COUNT query to determine if a URL matches a value in my database and a page should therefore be displayed. The second query would probably target the field URL on each table and look something like this:
SELECT URL, 'World' AS MySection, 'GW' AS MySiteID
This allows me to assign a website and section ID to each database table. The third query would then group tables with the same website and/or section ID together and query them for additional information.
Anyway, I wondered if I could eliminate the second query by assigning site and section ID's in my count query. I think the answer is NO, but I thought I read somewhere that it can be done. Can anyone tell me how to do it, or is this a lost cause? The way I'm doing it below doesn't work for obvious reasons.
$sql = "SELECT
SUM(num) AS num
FROM
(
SELECT COUNT(URL) AS num, 'World' AS MySection, 'GW' AS MySiteID, gw_geog.`URL` as URL
FROM gw_geog
WHERE URL = :MyURL AND G1 = 1
UNION ALL
SELECT COUNT(URL) AS num, 'World' AS MySection, 'GW' AS MySiteID, gw_geog_political.`URL` as URL
FROM gw_geog_political
WHERE URL = :MyURL
) AS X
WHERE X.URL like :MyURL
LIMIT 1";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();
while ($row = $stm->fetch())
{
$MySiteID = $row['MySiteID'];
$MySection = $row['MySection'];
}
