13F PHP – Get Data from url, upload to MySQL, make table.




 1 <?php
 2 require_once('globals.php');
 3 $username="*****";
 4 $password="*****";
 5 $host= "localhost:3306";
 6 $database="tranch5_db1";
 7 $drop= ("drop table if exists holdpen");
 8 $create= ("create table holdpen 
 9     (
10     issuer varchar (100),
11     class varchar (100),
12     cusip varchar (100),
13     value  int, 
14     prin varchar (100),
15     type varchar (100),
16     disc varchar (100),
17     other varchar (100),
18     solevote varchar (100),
19     sharedvote varchar (100),
20     novote varchar (100)
21 
22      );")
23                         or die("SELECT Error: ".mysql_error());
24 
25 mysql_connect($host, $username, $password);
26 mysql_select_db($database) or die( "Unable to select database");
27 mysql_query($drop);
28 mysql_query($create);   
29 $subdoc = new DOMDocument;
30 $filerdoc = new DOMDocument;
31 $subdoc->load($_POST["itab"]);
32 $filerdoc->load($_POST["pdoc"]);
33 //$dropfiler= ("drop table if exists filer");
34 $createfiler= ("create table filer 
35     (
36     cik varchar (100),
37     fileno varchar (25),
38     period varchar (25),
39     name varchar (100),
40     street  varchar (100), 
41     city varchar (100),
42     statectry varchar (100),
43     zip varchar (100)
44 
45      );")
46                         or die("SELECT Error: ".mysql_error());
47 //mysql_query($dropfiler);
48 mysql_query($createfiler);
49   
50   $cik = $filerdoc->getElementsByTagName("cik")->item(0)->nodeValue;
51   $fileno = $filerdoc->getElementsByTagName("form13FFileNumber")->item(0)->nodeValue;
52   $period = $filerdoc->getElementsByTagName("periodOfReport")->item(0)->nodeValue;
53   $filerName = $filerdoc->getElementsByTagName("name")->item(0)->nodeValue;            
54   $street = $filerdoc->getElementsByTagName("street1")->item(0)->nodeValue; 
55   $city = $filerdoc->getElementsByTagName("city")->item(0)->nodeValue;  
56   $stateCtry = $filerdoc->getElementsByTagName("stateOrCountry")->item(0)->nodeValue; 
57   $zip = $filerdoc->getElementsByTagName("zipCode")->item(0)->nodeValue; 
58   $positions = $filerdoc->getElementsByTagName("tableEntryTotal")->item(0)->nodeValue;
59   $totval = $filerdoc->getElementsByTagName("tableValueTotal")->item(0)->nodeValue;
60 
61 //echo $cik .  '<br>' . $fileno .  '<br>' . $period .  '<br>'. $filerName .  '<br>' . $street .  '<br>' . $city .  '<br>' . $stateCtry .  '<br>' . $zip .  '<br>';
62 mysql_query("insert into filer (cik, fileno, period, name, street, city, statectry, zip)
63          values ('$cik', '$fileno', '$period', '$filerName', '$street', '$city', '$stateCtry', '$zip' )")
64 or die("SELECT Error: ".mysql_error());
65 $table = $subdoc->getElementsByTagName( "informationTable" ); 
66 foreach($table as $row)
67 {
68        $holdings = $row->getElementsByTagName("infoTable");
69        foreach($holdings as $posn)
70        {
71             $issuer = $posn->getElementsByTagName("nameOfIssuer")->item(0)->nodeValue;
72             $class = $posn->getElementsByTagName("titleOfClass")->item(0)->nodeValue;
73             $cusip = $posn->getElementsByTagName("cusip")->item(0)->nodeValue;
74             $value = $posn->getElementsByTagName("value")->item(0)->nodeValue;
75             $prin = $posn->getElementsByTagName("sshPrnamt")->item(0)->nodeValue; 
76             $type = $posn->getElementsByTagName("sshPrnamtType")->item(0)->nodeValue;  
77             $disc = $posn->getElementsByTagName("investmentDiscretion")->item(0)->nodeValue;  
78             $other = $posn->getElementsByTagName("otherManager")->item(0)->nodeValue;  
79             $solevote = $posn->getElementsByTagName("Sole")->item(0)->nodeValue;
80             $sharedvote = $posn->getElementsByTagName("Shared")->item(0)->nodeValue;
81             $novote = $posn->getElementsByTagName("None")->item(0)->nodeValue;
82 //echo $issuer .", " . $class . ", " . $cusip . ", " . $value . ", " . $prin . ", " . $type . ", " . $disc . ", " . $other . ", " . $solevote . ", " . $sharedvote . ", " . $novote . '<br>';      
83 mysql_query("insert into holdpen (issuer, class, cusip, value, prin, type, disc, other, solevote, sharedvote, novote)
84          values ('$issuer', '$class', '$cusip', '$value', '$prin', '$type', '$disc', '$other', '$solevote', '$sharedvote', '$novote')");
85        }       
86 }
87 $calctotals= ("select sum(value) as calcval, count(cusip) as calcpos from holdpen ");
88 $gettable= ("select issuer, class, cusip, prin as shares, value, (solevote / prin) * 100 as pctvote from holdpen order by issuer");
89 $result=mysql_query($gettable);
90 $calcresult=mysql_query($calctotals);
91 $num=mysql_numrows($result);
92 mysql_query($gettable); 
93 mysql_query($calctotals);
94 $crow = mysql_fetch_array($calcresult);
95 
96 
97 ?>
98