Posts Tagged jquery

How to Build a Line Database

Posted by Rufio Magillicutty in Database, MLB, MySQL, PHP, Pinnacle on May 28, 2012

Apologies for not having provided any content lately (my tweets have certainly offended about ten users). I would have wrote this months ago but I didn’t.

Let me preface this futher by saying, assuming one will be building a database on a local web-server, I highly recommend using another computer other than the primary one to run a server.  I have an old Toshiba laptop that is running Debian (Debian 6.0 is the latest version) and sits in the back of my closet.

In a previous post I uploaded an Excel file that automatically extracts lines from Pinnacle and inserts into an Access database on open (keep the file open and invoke the “Application.OnTime” VBA function for a reoccurring call, or set up a windows task scheduler event).  But that requires Windows, and ideally one would want a solution that can be applied across various operating systems.  PHP and MySQL is one such solution.  Linux users can simply download apache, php, and mysql from the repository.  Windows or MAC users might want to look into downloading XAMPP.  PHP is a server-side scripting language, so it operates via some sort of web-server, such as apache (if PHP is unfamiliar, just carefully read the code it and should be pretty straight-foward).  And MySQL provides the database structure and query language that can be interfaced with most programming languages. I would also suggest setting up an ssh connection, from one computer on the network to the one running the server.

Here is my SQL table structure configured for baseball lines from Pinnacle (assuming a database has already been created):

CREATE TABLE IF NOT EXISTS `LINES` (
  `Date` varchar(55) NOT NULL,
  `vRot` varchar(5) NOT NULL,
  `Away` varchar(55) NOT NULL,
  `vListed` varchar(55) NOT NULL,
  `vLine` varchar(12) NOT NULL,
  `vTotal` varchar(12) NOT NULL,
  `vML` int(11) NOT NULL,
  `hRot` varchar(5) NOT NULL,
  `Home` varchar(55) NOT NULL,
  `hListed` varchar(55) NOT NULL,
  `hLine` varchar(12) NOT NULL,
  `hTotal` varchar(12) NOT NULL,
  `hML` int(11) NOT NULL,
  `nowTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `ID` (`Date`,`vRot`,`vListed`,`hRot`,`hListed`,`hML`,`hTotal`,`hLine`,`vML`,`vTotal`,`vLine`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The ‘nowTime’ column automatically tracks the current time on data insert. This table is meant to accomodate those interested in tracking line movement, because Pinnacle’s XML updates everytime there is new information added. To take advantage of this, an intermittent call (Pinnacle allows at least 60 seconds between calls) can be made using whatever fashion is most convenient for the programmer (cron job, delayed loop…). And to avoid redundant database inserts, indexing every column and using the ‘INSERT IGNORE’ sql command is essential.

Again, I’m using PHP, and here is my PHP code to grab MLB lines from Pinnacle and insert them into the above SQL table (my database name is ‘MLB’):

//error_reporting(0);
$host='localhost';
$username='USER';
$pswrd='PASS';
 
$con = mysql_connect($host,$username,$pswrd);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("MLB",$con) or die('Error while selecting db');
 
$xmldoc = new DOMDocument();
$url = 'http://xml.pinnaclesports.com/pinnacleFeed.aspx?sporttype=baseball&sportsubtype=MLB';
$xmldoc->load($url);
 
$doc = $xmldoc->documentElement;
$event = $doc->getElementsByTagName("event");
foreach( $event as $ev )
{
  $ml_v = $ev->getElementsbyTagName("moneyline_visiting")->item(0)->nodeValue;
  $ml_h = $ev->getElementsbyTagName("moneyline_home")->item(0)->nodeValue;
  if ($ml_h==""){
      continue;
  }
 
  $total_v = $ev->getElementsbyTagName("total_points")->item(0)->nodeValue . " " . $ev->getElementsbyTagName("over_adjust")->item(0)->nodeValue;
  $total_h = $ev->getElementsbyTagName("total_points")->item(0)->nodeValue . " " . $ev->getElementsbyTagName("under_adjust")->item(0)->nodeValue;
 
  $d = $ev->getElementsByTagName("event_datetimeGMT")->item(0)->nodeValue;
  $teamnames = $ev->getElementsByTagName("participant_name");
  $name_v = str_replace("'","",$teamnames->item(0)->nodeValue);
  $name_h = str_replace("'","",$teamnames->item(1)->nodeValue);
  $rot = $ev->getElementsByTagName("rotnum");
  $rotv = $rot->item(0)->nodeValue;
  $roth = $rot->item(1)->nodeValue;
  $pitcher = $ev->getElementsByTagName("pitcher");
  $pitch_v = mysql_real_escape_string($pitcher->item(0)->nodeValue);
  $pitch_h = mysql_real_escape_string($pitcher->item(1)->nodeValue);
 
  $spread_v = $ev->getElementsbyTagName("spread_visiting")->item(0)->nodeValue . " " . $ev->getElementsbyTagName("spread_adjust_visiting")->item(0)->nodeValue; 
  $spread_h = $ev->getElementsbyTagName("spread_home")->item(0)->nodeValue . " " . $ev->getElementsbyTagName("spread_adjust_home")->item(0)->nodeValue; 
 
  $sql = "INSERT IGNORE INTO MLB.LINES (Date,vRot,Away,vListed,vLine,vTotal,vML,hRot,Home,hListed,hLine,hTotal,hML) Values ('$d','$rotv','$name_v','$pitch_v','$spread_v','$total_v','$ml_v','$roth','$name_h','$pitch_h','$spread_h','$total_h','$ml_h')";
   $query = mysql_query($sql,$con);
   if(!$query)
    {
    die('Could not insert values: ' . mysql_error());
    }
  }
mysql_close($con);

You can use whatever language you want, some are more comfortable with python, perl, javascript, brainfuck, etc…

What is important is knowing how to access your MySQL database from the script and how to navigate the Pinnacle XML file.

As a paranthetical, I previously mentioned running a cron job. In Windows, one may have to use the task scheduler. In MAC or LINUX, the ability to run a cron job should already be set up, just edit the crontab file. For example, a linux user simply has to type in a terminal:

crontab -e

And add the line:

*/2 * * * * /usr/bin/php path/to/php/file.php

This simply means, every two minutes (“/2″), a php file will be opened by the program “php.”

Now if everything works, we can start to present the lines in a nice HTML table. First, create a PHP file to query the database, grabbing the latest lines for each game listed at Pinnacle, and outputting the information in JSON format. This can be a bit tricky, but here is my solution (after connecting to a database with the name ‘MLB’):

...
$sql="SELECT * \n"
    . "FROM MLB.LINES AS m\n"
    . "INNER JOIN (\n"
    . "\n"
    . "SELECT c.vROT, MAX( c.nowTime ) AS maxtime\n"
    . "FROM MLB.LINES AS c\n"
    . "GROUP BY c.vROT\n"
    . ") AS a ON m.vROT = a.vROT\n"
    . "AND m.nowTime = a.maxtime WHERE NOW()<=DATE_SUB(m.Date,INTERVAL 4 HOUR)";
$results = mysql_query($sql);
while($row=mysql_fetch_assoc($results)){
  $array[$i]['D']=$row['Date'];
  $array[$i]['hRot'] = $row['hRot'];
  $array[$i]['vRot'] = $row['vRot'];
  $array[$i]['Away'] = $row['Away'];
  $array[$i]['Home'] = $row['Home'];
  $array[$i]['vListed'] = $row['vListed'];
  $array[$i]['hListed'] = $row['hListed'];
  $array[$i]['vML'] = $row['vML'];
  $array[$i]['hML'] = $row['hML'];
  $array[$i]['vTotal'] = $row['vTotal'];
  $array[$i]['hTotal'] = $row['hTotal'];
  $array[$i]['vLine'] = $row['vLine'];
  $array[$i]['hLine'] = $row['hLine'];
  $i++;
}
header('Content-type: application/json');
echo json_encode($array);
...

The advantage of Jquery is the background calls it can make to another file on a server and at the same time read and parse the information that is queried from that file. The “.getJSON” subroutine makes this possible by making calls every 120 seconds (120000) to the php file “MLB_Pinny.php”:

jQuery(document).ready( function( $ ){
	var timeout, d;
	getPinny();
	function getPinny() {
		$.getJSON('php/MLB_Pinny.php?'+new Date().getTime(), function(json_data){
			update = new Date();
			$('#update td:first').text('LAST UPDATE: '+update.toString("yyyy-MM-dd h:mm"));
			$('#today tr:not(:first)').empty();
			$('#tomorrow tr:not(:first)').empty();
    			$.each(json_data, function(i, item){
			d = Date.parse(item.D).addHours(-4);
			cur = (d.getDayName()==Date.today().getDayName()) ? "today" : "tomorrow";
         		$("#"+cur).append($('<tr><td rowspan="2">' + d.toString("yyyy-MM-dd h:mm") + '</td><td>' + item.vRot + '</td><td class="team">' + item.Away + '</td><td class="pitch">' + item.vListed + '</td><td class="ml">' + item.vML + '</td><td>' + item.vTotal + '</td><td>' + item.vLine + '</td></tr><tr><td>' + item.hRot + '</td><td class="team">' + item.Home + '</td><td class="pitch">' + item.hListed + '</td><td class="ml">' + item.hML + '</td><td>' + item.hTotal + '</td><td>' + item.hLine + '</td></tr>'));
   			 });
 
		});
	}
 
	timeout = setInterval(function() { getPinny() }, 120000);
});

Mine looks like this:

I have two “tbody” sections, one with ‘id = “today”‘ and the other ‘id = “tomorrow”‘. This should be self-explanatory.

Feel free to add some table enhancements (in this case, a toggle):

	$('#today').children('tr:eq(0)').click(function () {
     		$('#today').children('tr:gt(0)').toggle();
 	});
	$('#tomorrow').children('tr:eq(0)').click(function () {
    		$('#tomorrow').children('tr:gt(0)').toggle();
        });

It would be nice if included was the ability to query a pitcher’s closing lines for each start:

...
if(isset($_GET['pitch'])) {
	$query = str_replace("%",". ",$_GET['pitch']);
	$sql="SELECT * \n"
    . "FROM MLB.LINES AS m\n"
    . "WHERE vLISTED LIKE '%$query%' OR hLISTED LIKE '%$query%'\n"
    . "ORDER BY nowTime DESC";
}
else {
	header( 'HTTP/1.1 400 Bad Request' );
	die('Please use correct paramaters');
}
$result = mysql_query($sql,$con) or die ('Error while executing query' . mysql_error() . "\n");
echo '<html><head></head><body>
<table border=1><thead><th>Date</th><th>Away</th><th>vListed</th><th>vML</th><th>Home</th><th>hListed</th><th>hML</th></thead><tbody>';
while($row=mysql_fetch_assoc($result)){
	if ($d == $row['Date'])
		continue;
	if($row['vListed']==$query){
		$boldvml = "<strong>".$row['vML']."</strong>";
		$boldvnm = "<strong>".$row['vListed']."</strong>";
		$boldvtm = "<strong>".$row['Away']."</strong>";
		$boldhml = $row['hML'];
		$boldhnm = $row['hListed'];
		$boldhtm = $row['Home'];
	}
	else {
		$boldhml = "<strong>".$row['hML']."</strong>";
		$boldhnm = "<strong>".$row['hListed']."</strong>";
		$boldhtm = "<strong>".$row['Home']."</strong>";
		$boldvml = $row['vML'];
		$boldvnm = $row['vListed'];
		$boldvtm = $row['Away'];
	}
	$d = $row['Date'];
	echo '<tr><td>'.date("Y-m-d hh:MM",strtotime($row['Date'],'-4hours')).'</td><td>'.$boldvtm.'</td><td>'.$boldvnm.'</td><td>'.$boldvml.'</td><td>'.$boldhtm.'</td><td>'.$boldhnm.'</td><td>'.$boldhml.'</td></tr>';
}
echo '</tbody></table></body></html>';
...

Additionally, the HTML table needs to have the cell with the starter’s name clickable. Jquery can do this:

	$('.pitch').live('click',function() {
		window.open('php/linedb.php?pitch='+$(this).text().replace(" ","%").replace(".",""));
});

Occassionally, Pinnacle has a listed starer in the format “F LAST” rather than “F. Last”, this usually occurs when there is a late change in the listed starter or the pitcher is making his/her first start. Hence, there are some minor whitespace and trimming issues that for now seem to be resolved with some of the above code.

Hopefully what all this accomplishes is a personal Pinnacle line service, one that updates every 60+ seconds without having to refresh the browser or re-run a query. One could easily integrate the PHP code for different sports. Obviously, basketball and football do not have listed starters, other than that the PHP code should work fine once pointed to the relevant Pinnacle XML file (or any other sportsbook).

I haven’t updated this in a while, but on my github account there is a “SP-DATABASE” project. More importantly, various PHP and MySQL files are provided that can be used independently of the html front-end, and provide a template to abuse Pinnacle.

Share
Download article as PDF

, , , , , , , , , , , , , ,

1 Comment