MySQL>汇总为一个查询(包括Google Maps) [英] MySQL > sum up into a single query (incl. google maps)
问题描述
此问题是@Vincent Savard对我的一个问题发表评论的后续报道.
This Q is posted as a follow up to a comment on one of my Qs by @Vincent Savard.
目前,我正在尝试(取得了一些成功-谷歌地图XML请求在60秒后达到超时)将(漂亮的)大型表中的数据拆分为许多较小的表-以及对其进行转换/修改/等操作.苍蝇.对于此任务,我在以下示例附近使用php:
Currently I'm trying (with some success - hitting timeout after 60 sec. for google-maps XML requests) to split data from a (pretty) large table into lots of smaller tables - plus converting/modifying/etc them on the fly. For this task I'm using php close to the following example:
// The following happens inside some functions.
// The main table has some "groups" of content.
// fn_a creates the new small tables
"
CREATE TABLE {$small_table_a}
col_group_a_id int UNSIGNED NOT NULL AUTO_INCREMENT,
col_group_a_fname tinytext,
col_group_a_lname tinytext,
col_group_a_valA tinytext,
col_group_a_valB tinytext,
col_group_a_address tinytext,
col_group_a_lat decimal(9,3),
col_group_a_lng decimal(9,3),
PRIMARY KEY (id)
"
"
CREATE TABLE {$small_table_b}
col_group_b_id int UNSIGNED NOT NULL AUTO_INCREMENT,
col_group_b_fname tinytext,
col_group_b_lname tinytext,
col_group_b_valA tinytext,
col_group_b_valB tinytext,
col_group_b_address tinytext,
col_group_b_lat decimal(9,3),
col_group_b_lng decimal(9,3),
PRIMARY KEY (id)
"
// fn_b loads the content from the big table, modifies it and saves it row per row into the small tables
$sql = "
SELECT *
FROM {$big_table}
"
foreach ( $sql as $data )
{
$id = $data->id;
$group_a_fname = $data->group_a_fname;
$group_a_lname = $data->group_a_lname;
$group_a_lname = "{$group_a_fname}, {$group_a_lname}";
$group_a_valA = $data->group_a_valA ? $data->group_a_valA : '-';
$group_a_valA = $data->group_a_valB ? $data->group_a_valB : 'none';
$group_a_valA = $data->group_a_address;
$group_b_fname = $data->group_b_fname;
$group_b_lname = $data->group_b_lname;
$group_b_name = "{$group_b_fname}, {$group_b_lname}";
$group_b_valA = $data->group_b_valA ? $data->group_b_valA : '/';
$group_b_valA = $data->group_b_valB ? "€ {$data->group_b_valB}" : null;
"
INSERT INTO {$small_table_a} ... VALUES ...
"
}
// fn_c pulls in data from the small tables, asks the google map API for lat & lng and _should_ update the small table
$sql = "
SELECT *
FROM {$small_table_a}
"
foreach ( $sql as $data )
{
$output['id'] = $data->id;
$address = urlencode( $data->address );
$url = "http://maps.google.com/maps/api/geocode/xml?address={$address}&sensor=false";
$content = file_get_contents( $url );
$file_data = new SimpleXMLElement( $content );
$file_data = $file_data->result ? $file_data->result : null;
if ( ! $file_data )
continue;
$location = $file_data->geometry->location;
$output['lat'] = (string) $location->lat;
$output['lng'] = (string) $location->lng;
}
foreach ( $output as $data )
{
"
UPDATE {$table}
SET lat=SET lat={$data['lat']}, lng={$data['lng']}
WHERE id=$data['id']
}
问题:我如何在一个查询中做到这一点?或者如何减少数据库查询?当今天超出地理编码限制时,如何在不中断查询构建的情况下将lat/lng添加到表中-我不想仅仅因为超出限制而放弃所有内容.
Question: How could I do this in one query? Or how could I reduce DB-queries? And how would I add the lat/lng to the tables without interrupting the query building when my geocoding limit was exceeded for today - I don't want to drop everything just because I've gone over my limit.
谢谢!
注意:该示例是我脑海中手写的.那里可能有失败.
推荐答案
我们需要知道您的foreach循环中的INSERT INTO查询是什么,因为这可以汇总为一个查询.基本上,这是一个主意:
We need to know what the INSERT INTO query is in you foreach loop, because this is the one that can be summed into one query. Basically, here is the idea:
INSERT INTO {$small_table} -- you can specify which columns to fill,
-- i.e. INSERT INTO table (col_a, col_b)
SELECT group_a_fname, group_a_lname,
group_a_valA, group_a_valB,
group_a_address, group_b_fname,
group_b_lname, group_b_valA, group_b_valB -- etc
FROM {$big_table};
很显然,您必须调整查询来满足您的需求.您只需要了解其背后的想法即可:您可以使用SELECT查询插入行.
Obviously, you'll have to adapt the query to fill your needs. You just need to grasp the idea behind it : you can insert rows with a SELECT query.
UPDATE查询是不同的,因为您必须依赖外部数据(网站).我认为在一个查询中没有一种简单的方法可以做到这一点,但是我可能是错的.
The UPDATE query is different because you have to rely on external data (a website). I don't think there is an easy way to do it in one query, but I may be wrong.
这篇关于MySQL>汇总为一个查询(包括Google Maps)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!