php - 为什么我在使用 PHP 执行 SQL 查询时收到超时错误？

Question

我正在编写一个模块，该模块从我们的分销商网站下载产品数据，然后根据我定义的设置对其进行分类。类别映射在以下 SQL 表中定义:

这些函数是从 foreach 循环中调用的，该循环还下载数据。在编写查询上述数据库并根据从我们的经销商收到的字符串值保存类别的函数之前，我没有收到任何错误。这是我调用该函数的行:

//Get PS category based on mapped values
$PsCategory = getPsCategory($category1);

调用此函数后，我开始收到以下错误:

fatal error :第 7 行的 C:\xampp\htdocs\prestaprep1-5-3-1\modules\STLimportmodule\model\database.php 超出了最大执行时间 30 秒

相关函数如下:

function getMappedData(){
include('C:/xampp/htdocs/prestaprep1-5-3-1/modules/stlimportmodule/model/database.php');
$query='SELECT * FROM category_maps';
$statement = $db->prepare($query);
$statement -> execute();
$fetchedData = $statement ->fetchAll();
$statement -> closeCursor();

return $fetchedData;
}

function getPsCategory($stlCategory){
$fetchedData = getMappedData();
//    print_r($fetchedData);
foreach ($fetchedData as $mappedSTLvalues) {

    if($stlCategory == $mappedSTLvalues[0]){
        $psCategoryValue = $mappedSTLvalues['ps_category'];
    }else{
        $psCategoryValue = 2;
    }
}
return $psCategoryValue;
}

您可能会注意到，我将其分为两个函数，一个作为控件，另一个仅用于执行 SQL 查询。最初，它们都在同一个功能中，但我将其分开以帮助排除故障。我的数据库文件如下:

$dsn = 'mysql:host=localhost;dbname=ps_development';
$username = 'root';
$password = '';

try {
    $db = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
    $error_message = $e->getMessage();
    include('database_error.php');
    exit();
}
//DEBUG:    echo "database.php is referenced"

第 7 行在这里:

$db = new PDO($dsn, $username, $password);

据我所知，我似乎在代码中的某个地方进入了无限循环。使用此数据库文件时没有其他函数有任何错误，并且我在查询语法中看不到任何错误。数据库不太大，因为它只包含 9 条记录。

如果有人能指出我错过的东西，我将不胜感激。

<小时/>

当我执行 print_r 时，我得到以下输出:

 Array ( [0] => Array ( [stl_category] => Christian Living [0] => Christian Living [ps_category] => 38 [1] => 38 [id] => 3 [2] => 3 ) [1] => Array ( [stl_category] => Inspirational Motivation [0] => Inspirational Motivation [ps_category] => 230 [1] => 230 [id] => 4 [2] => 4 ) [2] => Array ( [stl_category] => Love and Marriage [0] => Love and Marriage [ps_category] => 231 [1] => 231 [id] => 5 [2] => 5 ) ) Array ( [0] => Array ( [stl_category] => Christian Living [0] => Christian Living [ps_category] => 38 [1] => 38 [id] => 3 [2] => 3 ) [1] => Array ( [stl_category] => Inspirational Motivation [0] => Inspirational Motivation [ps_category] => 230 [1] => 230 [id] => 4 [2] => 4 ) [2] => Array ( [stl_category] => Love and Marriage [0] => Love and Marriage [ps_category] => 231 [1] => 231 [id] => 5 [2] => 5 ) ) Array ( [0] => Array ( [stl_category] => Christian Living [0] => Christian Living [ps_category] => 38 [1] => 38 [id] => 3 [2] => 3 ) [1] => Array ( [stl_category] => Inspirational Motivation [0] => Inspirational Motivation [ps_category] => 230 [1] => 230 [id] => 4 [2] => 4 ) [2] => Array ( [stl_category] => Love and Marriage [0] => Love and Marriage [ps_category] => 231 [1] => 231 [id] => 5 [2] => 5 ) ) Array ( [0] => Array ( [stl_category] => Christian Living [0] => Christian Living [ps_category] => 38 [1] => 38 [id] => 3 [2] => 3 ) [1] => Array ( [stl_category] => Inspirational Motivation [0] => Inspirational Motivation [ps_category] => 230 [1] => 230 [id] => 4 [2] => 4 ) [2] => Array ( [stl_category] => Love and Marriage [0] => Love and Marriage [ps_category] => 231 [1] => 231 [id] => 5 [2] => 5 ) ) Array ( [0] => Array ( [stl_category] => Christian Living [0] => Christian Living [ps_category] => 38 [1] => 38 [id] => 3 [2] => 3 ) [1] => Array ( [stl_category] => Inspirational Motivation [0] => Inspirational Motivation [ps_category] => 230 [1] => 230 [id] => 4 [2] => 4 ) [2] => Array ( [stl_category] => Love and Marriage [0] => Love and Marriage [ps_category] => 231 [1] => 231 [id] => 5 [2] => 5 ) ) Array ( [0] => Array ( [stl_category] => Christian Living [0] => Christian Living [ps_category] => 38 [1] => 38 [id] => 3 [2] => 3 ) [1] => Array ( [stl_category] => Inspirational Motivation [0] => Inspirational Motivation [ps_category] => 230 [1] => 230 [id] => 4 [2] => 4 ) [2] => Array ( [stl_category] => Love and Marriage [0] => Love and Marriage [ps_category] => 231 [1] => 231 [id] => 5 [2] => 5 ) ) 

Answer 1

连接到数据库的时间似乎太长。这以前有效过吗？

您可以尝试以下方法来延长脚本的运行时间:

ini_set('max_execution_time', 600); //600 seconds = 10 minutes

连接时间似乎太长了。您使用的是 ODBC，对吗？

更新:

我看到你的内容

include('C:/xampp/htdocs/prestaprep1-5-3-1/modules/stlimportmodule/model/database.php'); 

在函数内。这意味着，如果您有 for 循环，您可能会创建许多 $db 对象并导致卡住。您需要将 include 移出函数，并使用全局 $db 变量。