PHP：while循环中的mysql_fetch_array（）需要太长时间

I am creating an online calendar for a client using PHP/MySQL.

我正在使用PHP / MySQL为客户创建在线日历。

I initiated a <table> and <tr>, and after that have a while loop that creates a new <td> for each day, up to the max number of days in the month.

我启动了一个

和 ,之后有一个while循环,为每天创建一个新的

,直到该月的最大天数。

The line after the <td>, PHP searches a MySQL database for any events that occur on that day by comparing the value of $i (the counter) to the value of the formatted Unix timestamp within that row of the database. In order to increment the internal row counter ONLY when a match is made, I have made another while loop that fetches a new array for the result. It is significantly slowing down loading time.

在之后的行,PHP通过比较$ i(计数器)的值与数据库该行内格式化的Unix时间戳的值,在MySQL数据库中搜索当天发生的任何事件。为了在匹配时仅增加内部行计数器,我创建了另一个while循环,为结果获取一个新数组。它显着减慢了加载时间。

Here's the code, shortened so you don't have to read the unnecessary stuff:

这是代码,缩短了,所以你不必阅读不必要的东西:

$qry = "SELECT * FROM events WHERE author=\"$author\"";
$result = mysql_query($qry) or die(mysql_error());

$row = mysql_fetch_array($result);

for ($i = 1; $i <= $max_days; $i++) {

    echo "<td class=\"day\">";

    $rowunixdate_number = date("j", $row['unixdate']);

    if ($rowunixdate_number == $i) {
        while ($rowunixdate_number == $i) {
            $rowtitle = $row['title'];
            echo $rowtitle;
            $row = mysql_fetch_array($result);
            $rowunixdate_number = date("j", $row['unixdate']);
        }
    }

    echo "</td>";

    if (newWeek($day_count)) {
        echo "</tr><tr>";
    }
    $day_count++;

}

4 个解决方案

#1

The slowness is most likely because you're doing 31 queries, instead of 1 query before you build the HTML table, as Nael El Shawwa pointed out -- if you're trying to get all the upcoming events for a given author for the month, you should select that in a single SQL query, and then iterate over the result set to actually generate the table. E.g.

很慢很可能是因为你在构建HTML表之前做了31次查询,而不是1次查询,正如Nael El Shawwa所指出的那样 - 如果你想要获得本月给定作者的所有即将发生的事件,您应该在单个SQL查询中选择它,然后迭代结果集以实际生成表。例如。

$sql = "SELECT * FROM events WHERE author = '$author' ORDER BY xdate ASC";
$rsEvents = mysql_query($sql);
echo("<table><tr>");
while ($Event = mysql_fetch_array($rsEvents)) {
    echo("<td>[event info in $Event goes here]</td>");
}
echo("</tr></table>");

Furthermore, it's usually a bad idea to intermix SQL queries and HTML generation. Your external data should be gathered in one place, the output data generated in another. My example cuts it close, by having the SQL immediately before the HTML generation, but that's still better than having an HTML block contain SQL queries right in the middle of it.

此外,混合SQL查询和HTML生成通常是一个坏主意。您的外部数据应该集中在一个地方,输出数据在另一个地方生成。我的例子通过在HTML生成之前立即使用SQL来关闭它,但这仍然比让HTML块在其中间包含SQL查询更好。

#2

Have you run that query in a MySQL tool to see how long it takes?

您是否在MySQL工具中运行该查询以查看需要多长时间?

Do you have an index on the author column?

你有作者专栏的索引吗?

There's nothing wrong with your PHP. I suspect the query is the problem and no index is the cause.

你的PHP没有任何问题。我怀疑查询是问题,没有索引是原因。

#3

aside from their comments above, also try to optimize your sql query since this is one of the most common source of performance issues.

除了他们上面的评论,还尝试优化您的SQL查询,因为这是性能问题的最常见的来源之一。

let say you have a news article table with Title, Date, Blurb, Content fields and you only need to fetch the title and display them as a list on the html page,

假设您有一个包含标题,日期,Blurb,内容字段的新闻文章表,您只需要获取标题并将其显示为html页面上的列表,

to do a "SELECT * FROM TABLE" means that you are requiring the db server to fetch all the field data when doing the loop (including the Blurb and Content which you are not going to use).

执行“SELECT * FROM TABLE”意味着您需要db服务器在执行循环时获取所有字段数据(包括您不打算使用的Blurb和Content)。

if you optimize that to something like:

如果你将其优化为:

"SELECT Title, Date FROM TABLE" would fetch only the necessary data and would be more efficient in terms of server utilization.

“SELECT Title,Date FROM TABLE”将仅获取必要的数据,并且在服务器利用率方面更有效。

i hope this helps you.

我希望这可以帮助你。

#4

Is 'author' an id? or a string? Either way an index would help you.

“作者”是个傻瓜吗?还是一个字符串?无论哪种方式,索引都可以帮助您。

The query is not slow, its the for loop thats causing the problem. Its not complete; missing the $i loop condition and increment. Or is this a typo?

查询并不慢,它的for循环导致问题。它不完整;错过$ i循环条件和增量。或者这是一个错字?

Why don't you just order the query by the date?

为什么不按日期订购查询?

SELECT * FROM events WHERE author=? ORDER BY unixdate ASC

and have a variable to store the current date you are on to have any logic required to group events by date in your table ex. giving all event rows with the same date the same color.

并有一个变量来存储您当前的日期,以便在表格中按日期对事件进行分组。为所有事件行提供相同颜色的相同日期。

Assuming the date is a unix timestamp that does not account for the event's time then you can do this:

假设日期是一个不考虑事件时间的unix时间戳,那么你可以这样做:

$currentDate = 0;
while(mysql_fetch_array($result)){
    if($currentDate == $row['unixdate']){
         //code to present an event that is on the same day as the previous event
    }else{
        //code to present an even on a date that is past the previous event
        //you are sorting events by date in the query
    } 

    //update currentDate for next iteration
    $currentDate = $row['unixdate'];
}

if unixdate includes the event time, then you need to add some logic to just extract the unix date timestmap excluding the hours and minutes.

如果unixdate包含事件时间,那么您需要添加一些逻辑来提取unix日期时间图,不包括小时和分钟。

Hope that helps

希望有所帮助

#1

$sql = "SELECT * FROM events WHERE author = '$author' ORDER BY xdate ASC";
$rsEvents = mysql_query($sql);
echo("<table><tr>");
while ($Event = mysql_fetch_array($rsEvents)) {
    echo("<td>[event info in $Event goes here]</td>");
}
echo("</tr></table>");

#2