将数据附加到num查询

Question

I'm using the following query to display pages whose URL's are stored in two different tables. (Actually, I'll soon have four or five joined tables.)

$sql = "SELECT SUM(num) as num FROM (
 SELECT COUNT(URL) AS num FROM gw_geog WHERE URL = :MyURL
 UNION ALL
 SELECT COUNT(URL) AS num FROM gw_world_urls WHERE URL = :MyURL
 ) AS X";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

Of course, this query only returns 0, 1, 2, etc. I just wondered if it's possible to extract additional information from such a query.

For example, is it possible to modify it so that every page derived from table gw_geog has the value $MyKind = 'This', while every page derived from table gw_world_urls has the value $MyKind = 'That'?

Or suppose table gw_world has a field named Rank with various values, like 1, 2, 3. Is it possible to say that a URL derived from that table (e.g. arizona) has a rank of 2, where the row values are arizona | 2?

Answer 1

Im not sure I understand what you want. I think you want to count how many rows you have in x tables where URL field = something. Then you want to know if a specific row of these tables (e.g. the one from gw_world where url = arizona) have a specific value for rank.

Do you really need this in only one query? Because you will get many repeated "sum" fields.

You could make this query to count the results and another one to get all rows with their respective ranks.