[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

limit number of database calls within multiple functions

Hello,

I have created several functions to pull data from a mysql database.

I have my main query that loops through each horse in a racecard and uses the functions to pull data on that horse.
the code that uses the functions is below: //note im using select * untill 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 />";

}

Open in new window


but the issue as every function calls my database and it either takes forever or connections time out.
Below are the functions - can anyone figure out a way to cut down on the number of mysql connections i need to make?

///////////////////////////////////////////////////////////////////////////////////////
//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;
    }
}

Open in new window

0
runnerjp2005
Asked:
runnerjp2005
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to check each query and time it and use explain plan to see if/ why its taking a lot of time

for example:
SELECT * FROM `horsesrp` WHERE `horse` = "' . $horse . '" and Place != "1" ORDER BY Date Limit 1

it shall be most efficient if you have a index on field horse (+ eventually on Place field and on date field)
if the field horse is a numerical field, to NOT put quotes around the value, same for place:
SELECT * FROM `horsesrp` WHERE `horse` = ' . $horse . '  and Place != 1 ORDER BY Date Limit 1
otherwise, even if there is a index on the field, it may not be used

reference for the explain plan stuff:
http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now