我在一个页面中从不同的表中获取不同的查询。我想减少对数据库的访问。
这是我的代码..
$query = "SELECT COUNT(*) as cnt_news FROM news";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$query = "SELECT COUNT(*) as cnt_adv FROM advertisements";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$query = "SELECT COUNT(*) as cnt_comm FROM comments";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$query = "SELECT SUM(CASE WHEN c.approve = '1' AND c.spam = '0' THEN 1 ELSE 0 END) AS cnt_approved,
SUM(CASE WHEN c.approve = '0' AND c.spam = '0' THEN 1 ELSE 0 END) AS cnt_unapproved,
SUM(CASE WHEN c.spam = '1' THEN 1 ELSE 0 END) AS cnt_spam
FROM COMMENTS c";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$query = "SELECT SUM(a.amount) as t_amnt,
SUM(a.cashpaid) as t_cpaid,
SUM(a.balance) as t_bal
FROM advertisements a";
$result = mysql_query($query);
$row = mysql_fetch_array($result);我对如何进行查询以从新闻、广告和评论这三个不同的表中选择值感到困惑。如何减少我的代码和数据库之旅?
谢谢。
发布于 2010-09-13 15:18:24
您不必减少数据库访问次数,但可以通过使用函数来减少代码
做一个通用的函数会更好。
<?php
$num_news = dbgetvar("SELECT COUNT(*) FROM news");
$num_adv = dbgetvar("SELECT COUNT(*) FROM advertisements");
$num_comm = dbgetvar("SELECT COUNT(*) FROM comments");
function dbgetvar($query){
$res = mysql_query($query);
if (!$res) {
trigger_error("dbget: ".mysql_error()." in ".$query);
return FALSE;
}
$row = mysql_fetch_row($res);
if (!$row) return NULL;
return $row[0];
}总是从重复的代码中生成一个函数。
发布于 2010-09-13 15:20:57
$query = "SELECT
COUNT(n.*) as cnt_news,
COUNT(a.*) as cnt_adv,
COUNT(c.*) as cnt_comm,
SUM(CASE WHEN c.approve = '1' AND c.spam = '0' THEN 1 ELSE 0 END) AS cnt_approved,
SUM(CASE WHEN c.approve = '0' AND c.spam = '0' THEN 1 ELSE 0 END) AS cnt_unapproved,
SUM(CASE WHEN c.spam = '1' THEN 1 ELSE 0 END) AS cnt_spam,
SUM(a.amount) as t_amnt,
SUM(a.cashpaid) as t_cpaid,
SUM(a.balance) as t_bal
FROM
news n, advertisements a, comments c";这应该可以在一个查询中完成。
发布于 2010-09-13 15:46:08
您可以使用mysqli库而不是mysql库来连接MySQL。使用mysqli::multi_query一次运行多个查询并获得结果。
几乎所有的mysql_函数都可以作为mysqli_函数使用:
<?php
$link = mysqli_connect("localhost", "user", "password", "dbname");
/* check connection */
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
//these are your queries. Put them all in a string separated by semicolon ;
$query = "SELECT COUNT(*) as cnt_news FROM news;";
$query .= "SELECT COUNT(*) as cnt_adv FROM advertisements;";
$query .= "SELECT COUNT(*) as cnt_comm FROM comments;";
// Now execute multi query
if (mysqli_multi_query($link, $query))
{
do {
/* store first result set */
if ($result = mysqli_store_result($link))
{
while ($row = mysqli_fetch_row($result))
{
printf("%s\n", $row[0]);
}
mysqli_free_result($result);
}
/* print divider */
if (mysqli_more_results($link)) {
printf("-----------------\n");
}
} while (mysqli_next_result($link));
}
/* close connection */
mysqli_close($link);
?>我仅为您的三个查询展示了示例。您可以在$query中放置更多查询。这样就会有一个对MySQL的请求。
https://stackoverflow.com/questions/3698430
复制相似问题