循环“mysql_query”仅适用于少量循环？

The code below is a portion of a php file I use to update a mySQLdatabase.

下面的代码是我用来更新mySQL数据库的php文件的一部分。

I can see in FF Firebug console that the POST data is all correct in the form that POSTs to this php file.

我可以在FF Firebug控制台中看到POST数据在POST到这个php文件的形式中都是正确的。

The problem is that if $i is greater that 3 I get the following error:

问题是，如果$ i大于3，我会收到以下错误：

The connection was reset
The connection to the server was reset while the page was loading."

连接已重置在页面加载时重置了与服务器的连接。“

Does this indicate that I have to write a custom php.ini? If so, what values do I need to increase?

这是否表明我必须编写自定义的php.ini？如果是这样，我需要增加什么价值？

I realize my looping mysql_query code may also be incorrect, but it does work with $i values of 1 to 3. Could someone suggest the correct coding if this is the case?

我意识到我的循环mysql_query代码也可能不正确，但它确实可以使用$ i值1到3.如果是这样的话，有人会建议正确的编码吗？

        for ($i=1; $i<=$rr_years; $i++){
            $idrr = GetSQLValueString($_POST['id' . $i], "int");
            $associated_horse = GetSQLValueString($_POST['associated_horse' . $i], "int");
            $year = GetSQLValueString($_POST['year' . $i], "text");
            $age = GetSQLValueString($_POST['age' . $i], "int");
            $starts = GetSQLValueString($_POST['starts' . $i], "int");
            $first = GetSQLValueString($_POST['first' . $i], "int");
            $first_sw = GetSQLValueString($_POST['first_sw' . $i], "int");
            $second = GetSQLValueString($_POST['second' . $i], "int");
            $second_sp = GetSQLValueString($_POST['second_sp' . $i], "int");
            $third = GetSQLValueString($_POST['third' . $i], "int");
            $third_sp = GetSQLValueString($_POST['third_sp' . $i], "int");
            $age_notes = GetSQLValueString($_POST['age_notes' . $i], "text");
            $age_text = GetSQLValueString($_POST['age_text' . $i], "text");
            $earned = GetSQLValueString($_POST['earned' . $i], "text");


        mysql_select_db($database_HDAdave, $HDAdave);
        mysql_query("UPDATE race_records SET
            associated_horse = $associated_horse, 
            year = $year, 
            age = $age, 
            starts = $starts, 
            first = $first, 
            first_sw = $first_sw, 
            second = $second, 
            second_sp = $second_sp, 
            third = $third, 
            third_sp = $third_sp, 
            age_notes = $age_notes,
            age_text = $age_text,
            earned = $earned


            WHERE rr_id = $idrr", $HDAdave) or die(mysql_error());

        }

Thanks for any help you can offer.

谢谢你尽你所能的帮助。

2 个解决方案

#1

This is more of personal taste maybe rather than real performance issue, but GetSQLValueString() (for those wondering, it's a Macromedia DW's custom function generated when using Dreamweaver's help for database. In this SO question it's shown and explained) sometimes is "too much", and a simple mysql_real_escape_string() or a check for value being an INT is enough.

这更多的是个人品味，而不是真正的性能问题，但GetSQLValueString（）（对于那些想知道，它是Macromedia DW的自定义函数，当使用Dreamweaver的数据库帮助时生成。在这个问题中它显示和解释）有时“太多了” “和一个简单的mysql_real_escape_string（）或检查值是一个INT就足够了。

Also, I'd place the connection and the selection of the database outside the loop. Additionally, but it's not needed usually, you can free mysql results after each passage:

此外，我将连接和数据库的选择放在循环外。另外，但通常不需要它，你可以在每次传递后释放mysql结果：

// connect to DB here.
mysql_select_db($database_HDAdave, $HDAdave);

for ($i=1; $i<=$rr_years; $i++){
            $idrr = intval($_POST['id' . $i]);
            $associated_horse = intval($_POST['associated_horse' . $i]);
            $year = mysql_real_escape_string($_POST['year' . $i]);
            $age = intval($_POST['age' . $i]);
            $starts = intval($_POST['starts' . $i]);
            $first = intval($_POST['first' . $i]);
            $first_sw = intval($_POST['first_sw' . $i]);
            $second = intval($_POST['second' . $i]);
            $second_sp = intval($_POST['second_sp' . $i]);
            $third = intval($_POST['third' . $i]);
            $third_sp = intval($_POST['third_sp' . $i]);
            $age_notes = mysql_real_escape_string($_POST['age_notes' . $i]);
            $age_text = mysql_real_escape_string($_POST['age_text' . $i]);
            $earned = mysql_real_escape_string($_POST['earned' . $i]);

        mysql_query("UPDATE `race_records` SET
            `associated_horse` = $associated_horse, 
            `year` = '$year', 
            `age` = $age, 
            `starts` = $starts, 
            `first` = $first, 
            `first_sw` = $first_sw, 
            `second` = $second, 
            `second_sp` = $second_sp, 
            `third` = $third, 
            `third_sp` = $third_sp, 
            `age_notes` = '$age_notes',
            `age_text` = '$age_text',
            `earned` = '$earned'
            WHERE `rr_id` = $idrr", $HDAdave) or die(mysql_error());
        // not needed:
        mysql_free_result($HDAdave);
        }
?>

#2

The error text The connection to the server was reset while the page was loading. tells me that the error is between your web server and your client (browser), not between the webserver and the DB.

错误文本加载页面时重置了与服务器的连接。告诉我，错误发生在您的Web服务器和客户端（浏览器）之间，而不是Web服务器和数据库之间。

Have you looked at the DB and see if the records are properly updated?

您是否查看了数据库并查看记录是否已正确更新？

As possible solutions:

尽可能解决方案：

increase php's max_execution_time
增加php的max_execution_time
increase php's max_input_time
增加php的max_input_time
if the above are not working, split the insert on the client side with javascript into smaller chunks and send them individually
如果上述方法无法正常工作，请使用javascript将客户端的插件拆分为较小的块并单独发送

Also, an InnoDB storage engine might help you here, because it has row-level locking and it might decrease the execution at DB level.

此外，InnoDB存储引擎可能会对您有所帮助，因为它具有行级锁定，并且可能会降低数据库级别的执行。

Also, look in apache's error log and see if there are other errors there.

另外，查看apache的错误日志，看看是否还有其他错误。

#1