Solved

limit number of database calls within multiple functions

Posted on 2015-01-02
1
90 Views
Last Modified: 2015-01-02
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
Comment
Question by:runnerjp2005
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now