php - 限制多个函数中的数据库调用次数

Question

我已经创建了几个函数来从 MySQL 数据库中提取数据。

我的主要查询循环遍历赛马卡片中的每匹马，并使用函数提取该匹马的数据。使用这些函数的代码如下:

//note I'm using select * until I finish knowing what I need to pull//

$horses12 = mysqli_query($db, 'SELECT * FROM tom_cards');
while($todayhorse12 = mysqli_fetch_array($horses12)){
$horse = $todayhorse12['Horse'];
$distance = $todayhorse12['Distance'];
$trainer = $todayhorse12['Trainer'];
$jockey = $todayhorse12['Jockeys_Claim'];
$weight = $todayhorse12['Weight'];
$class = $todayhorse12['Class'];

//function calls go here e.g
echo $horse.horselargerace($horse,$db)."<br />";

}

问题是每个函数都调用我的数据库，它要么永远耗时，要么连接超时。以下是功能 - 谁能想出一种方法来减少我需要建立的 MySQL 连接数？

///////////////////////////////////////////////////////////////////////////////////////
//did the horse run in a large field of 12 or more horses and perform well recently?//
//////////////////////////////////////////////////////////////////////////////////////
function horselargerace($horse, $db)
{
    $horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "' . $horse . '" and Runners > 12  ORDER BY Date Limit 5');
    $count  = 0;
    while ($todayhorse = mysqli_fetch_array($horses)) {
        $runners = 0;
        if ((int) $todayhorse['Place'] < 5) {
            $count = $count + 1;
        }
    }
    return $count;
}
//////////////////////////////////////////////////////////////////
//is the horse moving up in class after a good finish or a win?//
////////////////////////////////////////////////////////////////
function horselastclass($horse, $db, $class)
{
    $horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "' . $horse . '" ORDER BY Date Limit 1');
    $count  = 0;
    while ($todayhorse = mysqli_fetch_array($horses)) {
        $class2 = trim(str_replace("Class", "", $todayhorse['Class']));
        $class  = trim(str_replace("Class", "", $class));
        if ($class2 == "") {
            $class2 = $class;
        }
        if (trim($class) != "" or trim($class2) != "") {
            //if a horse is being dropped in class this should be easier
            if ((int) $class < (int) $class2) {
                $count = $count + 1;
            } elseif ((int) $class > (int) $class2) {
                $count = $count - 1;
            }
        }
    }
    return $count;
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//is the horse picking up or dropping weight today?                                                                                                               //
// 114pds or under is ideal.horse drops 5pds or more from the last start i take that as a positive, if he picks up more than 5pds then i consider that a negative.//
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function horselastweight($horse, $db, $weight)
{
    $horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "' . $horse . '" ORDER BY Date Limit 1');
    $count  = 0;
    while ($todayhorse = mysqli_fetch_array($horses)) {
        $weight2 = preg_replace("/[^a-zA-Z]/", "", $weight);
        $weight2 = substr($weight2, 0, 1);
        if ($weight2 <> "") {
            $weight = substr($weight, 0, strpos($weight, $weight2));
        }
        //get stone and convert to pounds
        $total1 = (((int) substr($weight, 0, strpos($weight, "-"))) * 14) + (int) substr($weight, 1, strpos($weight, "-"));
        $total2 = (((int) substr(str_replace(chr(194), "", $todayhorse['Weight']), 0, strpos(str_replace(chr(194), "", $todayhorse['Weight']), "-"))) * 14) + (int) substr(str_replace(chr(194), "", $todayhorse['Weight']), 1, strpos(str_replace(chr(194), "", $todayhorse['Weight']), "-"));
        $weight = str_replace(chr(194), "", $todayhorse['Weight']) . "=" . $weight;
    }
    $total = (int) $total2 - (int) $total1;
    if ((int) $total > 4) {
        $count = $count + 1;
    } elseif ((int) $total < -4) {
        $count = $count - 1;
    }
    return $count;
}
//////////////////////////////////////////////////////////////////////////////
//did the horse have trouble in his/her last race? (comments broke slow ect)//
/////////////////////////////////////////////////////////////////////////////
function horsehavetrouble($horse, $db)
{
    $horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "' . $horse . '" ORDER BY Date Limit 1');
    $count  = 0;
    while ($todayhorse = mysqli_fetch_array($horses)) {
        if ($todayhorse['Place'] = "2" or $todayhorse['Place'] = "3" or $todayhorse['Place'] = "4" or $todayhorse['Place'] = "5") {
            $targets = array(
                "hampered",
                "awkward",
                "stumbled",
                "slipped",
                "jinked",
                "fell",
                "unseated"
            );
            foreach ($targets as $target) {
                if (strstr(strtolower($todayhorse['Comments']), $target) !== false) {
                    $count = $count + 1;
                }
            }
        }
    }
    return $count;
}
///////////////////////////////////////////////////////////////
//is the same jockey back on today after not winning in last?//
///////////////////////////////////////////////////////////////
function isjockeyonagainafterlosing($horse, $db, $jockey)
{
    $horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "' . $horse . '" and Place != "1" ORDER BY Date Limit 1');
    $count  = 0;
    while ($todayhorse = mysqli_fetch_array($horses)) {
        $pop = array_pop(explode(' ', $todayhorse['Jockeys_Claim']));
        if (trim(array_pop(explode(' ', $todayhorse['Jockeys_Claim']))) == trim(array_pop(explode(' ', trim($jockey))))) {
            $count = $count + 1;
        }
    }
    return $count;
}
//////////////////////////////////////////////////////
//has the jockey won previously on this same horse?//
////////////////////////////////////////////////////
function Hasjockeywonbeforeonhorse($horse, $db, $jockey)
{
    $horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "' . $horse . '" and Place ="1" ORDER BY Date');
    $count  = 0;
    while ($todayhorse = mysqli_fetch_array($horses)) {
        //get todays jockey and check to see if it matches with prev ones 
        if (trim(array_pop(explode(' ', $todayhorse['Jockeys_Claim']))) <> trim(array_pop(explode(' ', trim($jockey))))) {
            $count = $count + 1;
        }
    }
    return $count;
}
////////////////////////////////////
//is the horse changing trainers?//
//////////////////////////////////
function horsechnagedtrainers($horse, $db, $trainer)
{
    $horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "' . $horse . '" ORDER BY Date Limit 1');
    while ($todayhorse = mysqli_fetch_array($horses)) {
        $count = 0;
        //compare last trainer and current
        if (trim(array_pop(explode(' ', $todayhorse['Trainer']))) <> trim(array_pop(explode(' ', trim($trainer))))) {
            $count = $count + 1;
        }
    }
    return $count;
}
///////////////////////////////////////////////
//has the horse won at high odds in the past?//
///////////////////////////////////////////////
function horsehighodds($horse, $db)
{
    $horses = mysqli_query($db, 'SELECT Odds FROM `horsesrp` WHERE `horse` = "' . $horse . '" and Place ="1" ORDER BY Date Limit 1');
    while ($todayhorse = mysqli_fetch_array($horses)) {
        $fraction = str_replace("F", "", $todayhorse['Odds']);
        $fraction = str_replace("J", "", $fraction);
        $fraction = explode("/", $fraction);
        if ($fraction[1] != 0) {
            $fraction = ($fraction[0] / $fraction[1]);
        }
        $fraction = (int) $fraction;
        if (in_array((int) $fraction, range(2, 6))) {
            $count = $count + 1;
        }
    }
    return $count;
}
///////////////////////////////////////////////////////
//was the horse between 2-1 & 6-1 odds in last start?//
///////////////////////////////////////////////////////
function horsebetween2and6($horse, $db)
{
    $horses = mysqli_query($db, 'SELECT Odds FROM `horsesrp` WHERE `horse` = "' . $horse . '" ORDER BY Date Limit 1');
    $count  = 0;
    while ($todayhorse = mysqli_fetch_array($horses)) {
        //convert the odds to decimal
        $fraction = str_replace("F", "", $todayhorse['Odds']);
        $fraction = str_replace("J", "", $fraction);
        $fraction = explode("/", $fraction);
        if ($fraction[1] != 0) {
            $fraction = ($fraction[0] / $fraction[1]);
        }
        $fraction = (int) $fraction;
        if ((int) $fraction <= 2 and (int) $fraction >= 6) {
            $count = $count + 1;
        }
        if (in_array((int) $fraction, range(2, 6))) {
            $count = $count + 1;
        }
        return $count;
    }
}
//////////////////////////////////////////////////////
//was this horse a beaten favorite in last 3 starts?//
//////////////////////////////////////////////////////
function horsebeatenfav($horse, $db)
{
    $count  = 0;
    $horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "' . $horse . '" ORDER BY Date Limit 3');
    while ($todayhorse = mysqli_fetch_array($horses)) {
        if ($todayhorse['Place'] <> "1" and strpos($todayhorse['Odds'], 'F') !== false) {
            $count = $count + 1;
        }
    }
    return $count;
}
////////////////////////////////////////////////
//How many starts has the horse had this year?//
////////////////////////////////////////////////
function startswithin12months($horse, $db)
{
    $startdate = date('Y-m-d', strtotime('-1 year'));
    $enddate   = date('Y-m-d');
    $horses    = mysqli_query($db, 'SELECT Date FROM horsesrp where Horse = "' . $horse . '" and Date >= "' . $startdate . '" AND Date <= "' . $enddate . '" ORDER BY Date');
    return $horses->num_rows;
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//Is the horse changing distances today? - find out if the horse has ever won at this distance - if not -1point//
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function hashorsechangedistance($horse, $distance, $db)
{
    //select all distances this horse has run
    $horses        = mysqli_query($db, 'SELECT Distance FROM horsesrp where Horse = "' . $horse . '" ORDER BY Date');
    //count the times its run at this distance
    $distancecount = 0;
    while ($todayhorse = mysqli_fetch_array($horses)) {
        if ($todayhorse['Distance'] == $distance) {
            $distancecount = $distancecount + 1;
        }
    }
    //if distance is greater then 0 its ran at this distance before
    return $distancecount;
}
/////////////////////////////////////////////////////////
//How long has the horse been off (time between races)?//
/////////////////////////////////////////////////////////
function horselastrace($horse, $db)
{
    //select horse last run
    $sql    = 'SELECT `Date` FROM `horsesrp` where `Horse` = "' . $horse . '" ORDER BY Date limit 1';
    $horses = mysqli_query($db, $sql);
    while ($todayhorse = mysqli_fetch_array($horses)) {
        $dStart = new DateTime($todayhorse['Date']);
        $dEnd   = new DateTime(date('Y-m-d'));
        $dDiff  = $dStart->diff($dEnd);
        return $dDiff->days;
    }
}

组合在一起的查询如下:

$horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "'.$horse.'" ORDER BY Date Limit 1');
$horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "'.$horse.'" ORDER BY Date Limit 1');
$horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "'.$horse.'" ORDER BY Date Limit 1');
$horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "'.$horse.'" ORDER BY Date Limit 1');
$horses = mysqli_query($db, 'SELECT Odds FROM `horsesrp` WHERE `horse` = "'.$horse.'" ORDER BY Date Limit 1');
$sql = 'SELECT `Date` FROM `horsesrp` where `Horse` = "'.$horse.'" ORDER BY Date limit 1';
$horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "'.$horse.'" and Place != "1" ORDER BY Date Limit 1');
$horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "'.$horse.'" and Place ="1" ORDER BY Date');
$horses = mysqli_query($db, 'SELECT Odds FROM `horsesrp` WHERE `horse` = "'.$horse.'" and Place ="1" ORDER BY Date Limit 1');
$horses = mysqli_query($db, 'SELECT * FROM `horsesrp` WHERE `horse` = "'.$horse.'" ORDER BY Date Limit 3');
$horses = mysqli_query($db, 'SELECT Date FROM horsesrp where Horse = "'.$horse.'" and Date >= "'.$startdate.'" AND Date <= "'.$enddate.'" ORDER BY Date');
$horses = mysqli_query($db, 'SELECT Distance FROM horsesrp where Horse = "'.$horse.'"');

表结构:

Field
Type
Null
Key
Default
Extra
ID
int(255)
NO
PRI
NULL
auto_increment
ParentID
int(255)
NO
NULL
Date
date
NO
NULL
Track
varchar(100)
YES
NULL
Runners
varchar(50)
YES
NULL
Going
varchar(50)
YES
NULL
Distance
varchar(50)
YES
NULL
Class
varchar(50)
YES
NULL
Place
varchar(10)
YES
NULL
Losing_Dist
varchar(50)
YES
NULL
Stall
varchar(250)
YES
NULL
Horse
varchar(50)
YES
NULL
Weight
varchar(50)
YES
NULL
Trainer
varchar(50)
YES
NULL
Odds
varchar(50)
YES
NULL
Oddsmovement
varchar(250)
NO
NULL
Jockeys_Claim
varchar(50)
YES
NULL
Comments
varchar(250)
YES
NULL
Race_Name
varchar(250)
YES
NULL
Timetaken
varchar(255)
NO
NULL
OR
varchar(6)
NO
NULL

Answer 1

表 horsesrp 中的字段 Horse 是否有索引？指定的查询应该被快速处理，即使是大数据集。您可以按如下方式执行此操作:

 ALTER TABLE horsesrp ADD INDEX Horse (Horse);

关于 MySQL 索引的更多信息:

http://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html

How do MySQL indexes work?