多个查询：多个变量或一个查询

Question

我在用 php 编写所有这些内容时得到了很多帮助（主要是第一个长查询）。

// Query the database for data
$query = "SELECT cards.card_id, concat(title, \" By Amy\") AS TitleConcat, 
    description, meta_description, 
    seo_keywords,concat(\"http://www.amyadele.com/attachments//cards/\",cards.card_id,\"/\",card_image) AS ImageConcat,price 
FROM cards, card_cheapest 
WHERE cards.card_id = card_cheapest.card_id 
ORDER BY card_id";


$result = mysql_query($query);


// Open file for writing
$myFile = "googleproducts.txt";
$fh = fopen($myFile, 'w') or die("can't open file");

// Loop through returned data and write (append) directly to file
fprintf($fh, "%-25s %-200s  %-800s   %-200s %-800s %-800s\n", "id", "label","description","price","image","seo keywords");
fprintf($fh, "\n");
while ($row = mysql_fetch_assoc($result)) {
 fprintf($fh, "%-25s  %-200s  %-800s  %-200s %-800s %-800s\n", $row['card_id'], $row['TitleConcat'], $row['description'],$row['price'],$row['ImageConcat'], $row['seo_keywords']);
}

// Close out the file
fclose($fh);

echo "The file has been written sucessfully to googleproducts.txt. It will run again tomorrow at 12:00pm."
?>

然而，在过去的几天里，我写了一些其他问题，这引出了我的问题。以某种方式将其他查询粘贴到第一个“选择”中会更容易，还是设置多个查询然后将其也插入到行中会更容易（我什至不知道这是否可能）。

查询：选择最低价格

select card_id, min(card_price) from card_lookup_values where card_price > 0 group by card_id;

查询：创建 URL 结构

SELECT CONCAT('http://amyadele.com/', cards.title, '/', categories.seoname, '/', cards.seoname),cards.card_id
FROM cards
LEFT JOIN card_categories
  ON card_categories.card_id = cards.card_id
LEFT JOIN categories
  ON card_categories.category_id = categories.category_id ORDER by card_id;

我想我的问题是最好将我的查询（我已经知道可以工作）设置为多个变量，然后以某种方式将其推送到我设置的表格式中，或者以某种方式将所有这些查询格式化为一个长查询？

我最近刚刚写了

 SELECT 
            replace(lower(concat( 'http://www.amyadele.com/', pcat.seoname,'/',cat.seoname, '/', cards.seoname, '.htm' )),' ','+') AS link,
            concat(pcat.name,'>',cat.name) as category,
            replace(lower(concat( 'http://www.amyadele.com/', cat.seoname, '/', cards.seoname, '.htm' )),' ','+') AS add_to_cart_link          
          FROM cards 
          INNER JOIN card_categories cc ON cards.card_id = cc.card_id AND cards.card_live = 'y' AND cards.active = 'y' AND cc.active = 'Y'
          INNER JOIN categories cat ON cat.category_id = cc.category_id AND cat.active = 'Y'
          INNER JOIN categories pcat ON cat.parent_category_id = pcat.category_id
          INNER JOIN card_lookup_values clv on clv.card_id=cards.card_id and clv.lookup_detail_id
          WHERE cat.parent_category_id <>0
          ORDER BY cc.card_id 

但是，我现在真的很困惑如何添加它

Answer 1

您可以使用“联接”将所有这些信息聚合到来自数据库的单个响应中。我不知道你的确切模式，所以其中一些只是猜测，但这是我的开始方式。

SELECT 
  cards.card_id, 
  concat(cards.title, ' By Amy') AS TitleConcat, 
  cards.description, 
  cards.meta_description,
  cards.seo_keywords,
  concat('http://www.amyadele.com/attachments//cards/',cards.card_id,'/',cards.card_image) AS ImageConcat,
  card_cheapest.price,
  card_import.author,
  min(card_lookup_values.card_price)
FROM
  cards
  join card_cheapest on cards.card_id = card_cheapest.card_id
  left join card_import on card_import.card_id = cards.card_id
  join card_lookup_values on card_lookup_values.card_id = cards.card_id
WHERE card_lookup_values.card_price > 0
GROUP BY
  cards.card_id
ORDER BY
  cards.card_id