请查询SQL查询 [英] SQL query help please
问题描述
我使用PHP对MySQL进行查询。其实我做的太多了
这是问题所在，它太慢了。我想也许是一个内部联系或
有些东西会更好但是我无法理解这一点。
显示数据的网页有一个国家/地区列表。
每个国家/地区都有一个标签标题，其中列出的网站
包含该国家/地区的信息。任何给定的网站如果有关于该国家/地区的信息，可能会出现很多次（在不同的国家/地区）。
我有三张桌子。它们是：
country - 国家/地区列表及其ID密钥。
data_pairs - 国家和网站ID对
数据 - 列表网站及其ID密钥。
我查询所有国家/地区，然后循环播放每个国家/地区的
标题。在这个while循环中是另一个查询和
while循环以获取所有具有该
国家/地区信息的网站ID。在这个while循环中是另一个查询，而循环到
获取网站并打印它们。
所以要生成一个中等大小的网页我是做了五十次查询！
这需要几秒钟，我很惊讶它的工作原理。是否有一个
方式一次性完成所有这一切，一个查询？
感谢您的帮助。
PHP代码如下：
------------------------------- ------------------------------
函数GetData（）
{
$ link = $ this-> MakeConn（）;
mysql_select_db（" gdk"）或死（&）不选择数据库;
//获取所有国家/地区（ID，国家/地区FROM国家/地区表）
$ query =" SELECT * FROM country ORDER BY country;
$ result = mysql_query（$ query）或die（Query failed：。mysql_error（））;
//通过这些国家进行迭代并打印标题标签
while（$ row = mysql_fetch_array（$ result，MYSQL_NUM））
{
$ typeNum = $行[0]; //国家/地区ID
printf（"< div class = \" Box \">％s< / div>"，$ row [1]）; //打印标题
//获取这个国家/地区的网站ID
//（site_id，country_id，alpha FROM data_pairs表）
$ query2 =" SELECT * FROM data_pairs WHERE country_id = $ typeNum ORDER BY alpha" ;;
$ result2 = mysql_query（$ query2）or die（" Query failed ：。mysql_error（））;
/ *我现在拥有这个国家/地区的所有网站ID。
查询获取网站。对每个
国家/地区执行一次查询，因为这些网站没有任何共同点。
* /
//通过网站迭代id'并打印出来。
while（$ row2 = mysql_fetch_array（$ result2，MYSQL_NUM））
{
//获取链接（对于这个国家）
$ num = $ row2 [0];
//获取网站行（id，site_name，site_URL FROM数据表）
$ query3 =" SELECT * FROM data WHERE id = $ num" ;;
$ result3 = mysql_query（$ query3）or die（" Query failed：" .mysql_error（ ））;
$ site = mysql_fetch_array（$ result3，MYSQL_NUM）;
printf（"< a href = \" $ site [2] \ " target = \" _blank \"> $ site [1]< / a>< / br>"）;
}
printf（" br>"）;
} //而
//免费结果集
mysql_free_result（$ result）;
//关闭连接
mysql_close（$ link）;
} // function
I do a query to MySQL using PHP. Well actually I do too many of them
which is the problem, it''s too slow. I think maybe an inner join or
something would be better but I can''t figure this out.
The web page on which the data is displayed has a list of countries.
Each country has a label heading under which websites are listed that
have information on that country. Any given website may appear many
times (under different countries) if it has information on that country.
I''ve got three tables. They are:
country - A list of countries and their id key.
data_pairs - Country and website id pairs
data - A list of websites and their id key.
I do a query to get all the countries, then a while loop to print a
heading for each country. Inside this while loop is another query and
while loop to get all the website id''s that have information on that
country. Inside this while loop is another query and while loop to
get the websites and print them.
So to produce a medium size web page I''m doing like fifty queries!
It takes a few seconds, I''m surprised it works at all. Is there a
way to do all of this in one fell swoop, one query?
Thanks for your help.
The PHP code is listed below:
-------------------------------------------------------------
function GetData()
{
$link = $this->MakeConn();
mysql_select_db("gdk") or die("Could not select database");
// Get all the countries (id, country FROM country table)
$query = "SELECT * FROM country ORDER BY country";
$result = mysql_query($query) or die("Query failed : " . mysql_error());
// Iterate thru the countries and print their heading label
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
$typeNum = $row[0]; // the country id
printf ("<div class=\"Box\">%s</div>", $row[1]); // print the heading
// get the website id''s for this one country
// (site_id, country_id, alpha FROM data_pairs table)
$query2 = "SELECT * FROM data_pairs WHERE country_id = $typeNum ORDER BY alpha";
$result2 = mysql_query($query2) or die("Query failed : " . mysql_error());
/* I now have all the site id''s for this one country.
Do query to get the websites. Do one query for each
country because the sites have nothing in common.
*/
// Iterate thru the site id''s and print them.
while ($row2 = mysql_fetch_array($result2, MYSQL_NUM))
{
// Get a link (for this country)
$num = $row2[0];
// Get the site row (id, site_name, site_URL FROM data table)
$query3 = "SELECT * FROM data WHERE id = $num";
$result3 = mysql_query($query3) or die("Query failed : " . mysql_error());
$site = mysql_fetch_array($result3, MYSQL_NUM);
printf("<a href=\"$site[2]\" target=\"_blank\">$site[1]</a></br>");
}
printf("<br>");
} //while
// Free resultset
mysql_free_result($result);
// Close connection
mysql_close($link);
} //function
推荐答案
link =
this-> MakeConn（）;
mysql_select_db（" gdk"）或死（无法选择数据库）;
//获取所有国家/地区（ID，国家/地区） country table）
this->MakeConn();
mysql_select_db("gdk") or die("Could not select database");
// Get all the countries (id, country FROM country table)
query =" SELECT * FROM country ORDER BY country;
query = "SELECT * FROM country ORDER BY country";
这篇关于请查询SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
