Posted By

wa-rufio on 12/19/11


Tagged

mysql php table baseball mlb pitchfx


Versions (?)

PHP Create pitchfx Table Structure


 / Published in: PHP
 

Creates table structure for pitchfx data that is provided by mlb.com. Open PHP file and enter username and password for localhost. Feel free to customize to suit your preference.

  1. <?php
  2. $uname = '';
  3. $pswrd = '';
  4. $con = mysql_connect("localhost",$uname,$pswrd);
  5. if (!$con)
  6. {
  7. die('Could not connect: ' . mysql_error());
  8. }
  9. $db = "CREATE DATABASE IF NOT EXISTS pitchfx";
  10. mysql_query($db,$con) or die('Error while running query ' . mysql_error());
  11. mysql_select_db('pitchfx',$con) or die('Error while selecting database. ') . mysql_error());
  12. $sql = "CREATE TABLE IF NOT EXISTS `pitches` (
  13. `inning` tinyint(4) NOT NULL,
  14. `date_id` varchar(30) NOT NULL,
  15. `top_bot` tinyint(1) NOT NULL,
  16. `away` varchar(3) NOT NULL,
  17. `home` varchar(3) NOT NULL,
  18. `pitch_id` mediumint(8) unsigned NOT NULL,
  19. `pitch_hand` varchar(1) NOT NULL,
  20. `ab_id` mediumint(8) unsigned NOT NULL,
  21. `ab_hand` varchar(1) NOT NULL,
  22. `des` varchar(55) NOT NULL,
  23. `type` varchar(1) NOT NULL,
  24. `id` smallint(5) unsigned NOT NULL,
  25. `x` float unsigned NOT NULL,
  26. `y` float unsigned NOT NULL,
  27. `start_speed` float unsigned DEFAULT NULL,
  28. `end_speed` float unsigned DEFAULT NULL,
  29. `sz_top` float unsigned DEFAULT NULL,
  30. `sz_bot` float unsigned DEFAULT NULL,
  31. `pfx_x` float DEFAULT NULL,
  32. `pfx_z` float DEFAULT NULL,
  33. `px` float DEFAULT NULL,
  34. `pz` float DEFAULT NULL,
  35. `x0` float DEFAULT NULL,
  36. `y0` float DEFAULT NULL,
  37. `z0` float DEFAULT NULL,
  38. `vx0` float DEFAULT NULL,
  39. `vy0` float DEFAULT NULL,
  40. `vz0` float DEFAULT NULL,
  41. `ax` float DEFAULT NULL,
  42. `ay` float DEFAULT NULL,
  43. `az` float DEFAULT NULL,
  44. `break_y` float DEFAULT NULL,
  45. `break_angle` float DEFAULT NULL,
  46. `break_length` float DEFAULT NULL,
  47. `ball` tinyint(3) unsigned DEFAULT NULL,
  48. `strike` tinyint(3) unsigned DEFAULT NULL,
  49. `on_1b` mediumint(8) unsigned DEFAULT NULL,
  50. `on_2b` mediumint(8) unsigned DEFAULT NULL,
  51. `on_3b` mediumint(8) unsigned DEFAULT NULL,
  52. `sv_id` varchar(13) DEFAULT NULL,
  53. `pitch_type` varchar(2) DEFAULT NULL,
  54. `type_confidence` double DEFAULT NULL,
  55. `zone` tinyint(2) DEFAULT NULL,
  56. `nasty` tinyint(3) unsigned DEFAULT NULL,
  57. `spin_dir` double unsigned NOT NULL,
  58. `spin_rate` double NOT NULL,
  59. `pitch_seq` varchar(55) NOT NULL
  60. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
  61. mysql_query($sql,$con) or die('Error while executing query. ' . mysql_error());
  62. ?>

Report this snippet  

You need to login to post a comment.