In 2007 I began working on a website project for an investment company in my hometown of Cleveland, Ohio USA. The purpose of this website was to automatically download financial data of traded securities from two (2) remote Web servers. The streamed data was stored and processed in data tables in a MySQL database on the customer’s website. Their clients logged in to their accounts through a user ID and password protected interface page to view their account details in tabular text format as well as through bar and pie charts. I am going to explain the PHP programming code I used to FTP to one of these Web servers and retrieve the data. The script I will discuss connected to the Web server for Rydex mutual funds of Washington, D.C.
PHP MySQL Connection
Before I begin to narrate the subject matter PHP code, I would like to point out that much of the program code for this application you will see involves updating two (2) data tables in the MySQL database on the Web server. One is a “good audit trail data table” that is updated when an operation in the code was successfully completed. The other is a “bad audit trail data table” that is posted to when an operation failed. As I explain the code I will omit referencing the audit data table posts since there are quite a few of them. The reason I placed these in the code was because it was important to everyone involved to see what succeeded and what failed in a fully automated processing environment. Both of these PHP scripts were set up on the Web server’s “crontab manager” to automatically run at a set time each business day. If there were snafus then we had to be able to refer back to a “digital paper trail”. This told everyone where the problem occurred so the appropriate correction could be made to resolve it. Also, some of the actual file names, logiin parameters and other customer specific references have been renamed from the original for the sake of the customer’s privacy and security.
First PHP Scripts Run on Crontab Manager
The first script from the above link is titled “FIRST PHP SCRIPT TO BE RUN ON A CRONTAB MANAGER”. At the top there is a statement for the include file investment_company.php. This file contains defined constants that are used to connect to the Rydex Web server and then the database that contains the information that is needed. Just before these parameters are used to make the initial connection the current date is read into the PHP variable $todaysdate2 and yesterday’s date is then read into the PHP variable $todaysdate. Next a query is executed to show the data tables in the database. The idea is to verify the existence of eight (8) data tables: “trans_audit_trail_good”, “trans_audit_trail_bad”, “trans_temp_rydex”, “curr_day_trans”, “curr_week_trans”, “curr_month_trans”, “curr_quarter_trans”, and “trans_temp_rydex_account_position_merge”. All eight (8) of these data tables must exist in the database otherwise processing will be aborted.
Next free the resource that was used for the “SHOW TABLES” query operation. All the records will be purged from the Rydex temporary transaction account position data table. Now make the FTP connection to the file to be streamed back to the customer’s Web server:
$file_rydex = "ftp://{ftp user id goes here}:{ftp password goes here}ftp.rydexfunds.com/genericdata".$todaysdate.".csv";
Notice the FTP user ID and password have been embedded in the above PHP directive. Remember, this script is intended to be run on a crontab manager. Since no one will be around to enter the user ID and password pair it must be included in the PHP code. The FTP connection URL is contained in the PHP variable $file_rydex. The PHP variable $file_rydex2 contains the file name portion of the FTP connection URL, ‘genericdata".$todaysdate.".csv"‘. The PHP variable $todaysdate is concatenated between the genericdata and .csv constants. $todaysdate contains yesterday’s date because the csv file to be streamed back to the Web server is denoted with the date from one (1) day ago because that is the trade date of the financial data to be downloaded. This is the file name nomenclature used by Rydex mutual funds. Next the good message audit trail data table will be queried to see if the Rydex csv file contained in the PHP variable $file_rydex2 has been previously processed. If true, then processing will be aborted at this point. Here is the PHP directive for doing this. Note that $test_expression = "!!!PROCESSED!!!: ".$file_rydex2; in the directive below:
$resultz=mysql_query("SELECT * FROM trans_audit_trail_good WHERE success_description = '".$test_expression."'");
Now free the resource that was used for the above operation. Then execute the curl directives by first initializing a curl session. Set options to fetch the URL contained in the PHP variable ‘$file_rydex’ and to return the transfer as a string of the return value of the curl_exec() function. After the curl session is performed trap for curl errors. If found return it in the PHP variable ‘$rydex_err no’. Lastly the curl session is closed. Here is the code:
// initialize a curl session.
$curl = curl_init();
// set option for a curl transfer; this will fetch the URL contained in // '$file_rydex'.
curl_setopt($curl, CURLOPT_URL, $file_rydex);
// set option for a curl transfer; TRUE to return the transfer as a
// string of the return value of curl_exec().
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
// perform the curl session.
$filebuf = curl_exec($curl);
// if there is a curl error number then return it to '$rydex_errno' php // variable.
if (curl_errno($curl)) {
$rydex_errno = curl_errno($curl);
} else {
// close the curl session.
curl_close($curl);
}
// if the curl error number is 'CURLE_FTP_COULDNT_RETR_FILE' then do
// this....
if ($rydex_errno == 19) {
Abort processing here if curl error 19 (CURLE_FTP_COULDNT_RETR_FILE) is trapped in the above code. If this code does not appear then processing will continue by streaming the csv data file ‘$file_rydex2′ back to the customer’s website. First open the FTP connection URL in read only mode then loop through the delimited data file on the Rydex Web server. Grab a line from the csv file up to 1,024 characters in length and read this into the PHP variable ‘$vault’. If the number of fields in ‘$vault’ is equal to sixteen (16) then insert the array elements of ‘$vault’ into the Rydex temporary transaction account position data table. Close the FTP connection URL. Here is the code readout that performs this operation:
// open the ftp connection URL in read only mode. return '$rh' file
// pointer.
$rh = fopen($file_rydex, "r");
// set query failed php variable '$eflag' equal to 0 before entering
// the while loop.
$eflag = 0;
// begin looping through the delimited data file on the Rydex server. // use php 'fgetcsv' function to get a line from file pointer and parse // for CSV fields (length up to 1,024 characters). return the result to // the php variable '$vault'.
while (($vault = fgetcsv($rh, 1024, ",")) !== FALSE) {
// check to see if the number of elements in the php '$vault' variable // is equal to 16; if so proceed.
if (count($vault) == 16) {
// insert pertinent array elements of php variable '$vault' into a new // row in the Rydex temporary transaction account position data table.
$strSQLInsert = "INSERT INTO trans_temp_rydex (accountnumber, groupaccountnumber, name, fundid, fundname, fundsymbol, fundcusip, datex, shareamount, nav, dollaramount, brokerfirmnumber, brokerfirmname, brokerbranchnumber, brokerrepnumber, brokerrepname )";
$strSQLInsert = $strSQLInsert." VALUES ('$vault[0]', '$vault[1]', '$vault[2]', '$vault[3]', '$vault[4]', '$vault[5]', '$vault[6]', '$vault[7]', '$vault[8]', '$vault[9]', '$vault[10]', '$vault[11]', '$vault[12]', '$vault[13]', '$vault[14]', '$vault[15]')";
mysql_query($strSQLInsert);
// if the last query failed then do this....
if (mysql_affected_rows() == -1 ) {
// set query failed php variable '$eflag' equal to 1.
$eflag = 1;
// add message for the current record to the bad audit trail data table
// indicating a failed operation occurred for appending data into the
// Rydex temporary transaction account position data table.
$descr_string = "Customer name: ".$vault[2].", transaction date: ".$vault[7].", Ticker symbol: ".$vault[5]." was not imported into Rydex temporary transaction account position table...proceeding to next record...";
$strSQLInsert = "INSERT INTO trans_audit_trail_bad (failure_description, audit_date)";
$strSQLInsert = $strSQLInsert." VALUES ('$descr_string', '$todaysdate2')";
mysql_query($strSQLInsert);
}
}
// end loop.
}
// close the file.
fclose($rh);
Now query the Rydex temporary transaction account position data table to see if the number of data table records is greater than or equal to one (1). If successful, then proceed to query the Rydex temporary transaction account position data table again. If successful, then fetch the rows from the queried results. Next loop through the queried results and insert a new row in the Rydex account position merge data table as this next code snippet illustrates:
// set query failed php variable '$eflag' equal to 0 before entering
// the while loop.
$eflag = 0;
while($rowz=mysql_fetch_array($result4)) {
// insert pertinent fetched row elements from Rydex temporary
// transaction account position data table into a new row in Rydex
// account position merge data table.
$strSQLInsert = "INSERT INTO trans_temp_rydex_account_position_merge (customer_code, fundsymbol, fundname, shareamount, nav)";
$strSQLInsert = $strSQLInsert." VALUES ('$rowz[0]', '$rowz[5]', '$rowz[4]', '$rowz[8]', '$rowz[9]')";
mysql_query($strSQLInsert);
// if the last query failed then do this....
if (mysql_affected_rows() == -1 ) {
// set query failed php variable '$eflag' equal to 1.
$eflag = 1;
// add message for the current record to the bad audit trail data table // indicating a failed operation occurred for appending data into Rydex // account position merge data table.
$descr_string = "Customer code: ".$row[0].", Ticker symbol: ".$rowz[5]." was not imported into Rydex account position merge table...proceeding to next record...";
$strSQLInsert = "INSERT INTO trans_audit_trail_bad (failure_description, audit_date)";
$strSQLInsert = $strSQLInsert." VALUES ('$descr_string', '$todaysdate2')";
mysql_query($strSQLInsert);
}
// end loop.
}
Free the resource used for the above operation. Next query the Rydex account position merge data table. If successful, then fetch the rows from the queried results. If the fetch rows operation is successful then proceed. Loop through the results and assign row data and constants to PHP variables for number of shares, sector, capitalization, and domestic or international designator. The customer investments data table will be queried with the Rydex customer code and security ticker symbol from the row data. If the number of result rows from this operation is equal to one (1) then update the PHP variables for number of shares, sector, capitalization, and domestic or international designator with the row data from the queried customer investments data table. Free the resource used to query the customer investments data table. Purge all records from the customer investments data table for the Rydex customer code and security ticker symbol query data. Use the assigned and queried data for this queried row and insert a new record into the customer investments data table. Here is the code that does this:
// set query failed php variable '$eflag' equal to 0 before entering
// the while loop.
$eflag = 0;
while($rowy=mysql_fetch_array($result5)) {
// initialize php variable '$numshares' or the number of security
// shares with pertinent data retrieved from queried results of Rydex
// account position merge data table for the current row.
$numshares = $rowy[3];
// initialize other php variables with constants.
$sectr = "GENERAL";
$captlz = "LARGE-CAP";
$di = "DOMESTIC";
// query the customer investments data table for the Rydex customer
// code and the security ticker symbol that were queried from the Rydex // account position merge data table for the current record in the
// query set.
$resultg=mysql_query("SELECT * FROM cinvestments WHERE customer_code_r = '".$rowy[0]."' AND ticker_symbol = '".$rowy[1]."'");
if ($resultg) {
if (mysql_num_rows($resultg) == 1) {
// if the queried result is viable and the number of result rows equals // 1 then proceed.
$numshares = mysql_result($resultg, 0, "total_shares");
$sectr = mysql_result($resultg, 0, "sector");
$captlz = mysql_result($resultg, 0, "capitalization");
$di = mysql_result($resultg, 0, "domestic_international");
// these php variable assignments from this latest query will override // those assignments that were previously made to the same variables.
}
// free '$resultg' resource.
mysql_free_result($resultg);
}
// purge records from the customer investments data table that match
// the Rydex customer code and the security ticker symbol that were
// queried from Rydex account position merge data table for the current
// record in the query set.
mysql_query("DELETE FROM cinvestments WHERE customer_code_r = '".$rowy[0]."' AND ticker_symbol = '".$rowy[1]."'");
// take the assigned and queried data for this queried row and insert a // new record into the customer investments data table. note: the php
// variables '$tdate'(TRADE DATE) and '$pprice'(PURCHASE PRICE) are
// specified in this row insertion even though they contain no values. // this was a special circumstance pertaining to the customer this was // made for and not a coding omission.
$strSQLInsert = "INSERT INTO cinvestments (customer_code, customer_code_r, ticker_symbol, investment, total_shares, trade_date, purchase_price, current_share_price, management_code, lastdate, sector, capitalization, domestic_international, add_subtract_shares)";
$strSQLInsert = $strSQLInsert." VALUES ('N/A', '$rowy[0]', '$rowy[1]', '$rowy[2]', '$numshares', '$tdate', '$pprice', '$rowy[4]', 'Rydex', '$todaysdate2', '$sectr', '$captlz', '$di', '0')";
mysql_query($strSQLInsert);
// if the last query failed then do this....
if (mysql_affected_rows() == -1 ) {
// set query failed php variable '$eflag' equal to 1.
$eflag = 1;
// add message for the current record to the bad audit trail data table
// indicating a failed operation occurred for appending data into the
// customer investments data table.
$descr_string = "Customer code: ".$rowy[0].", Ticker symbol: ".$rowy[1]." was not added to customer investments...proceeding to next record...";
$strSQLInsert = "INSERT INTO trans_audit_trail_bad (failure_description, audit_date)";
$strSQLInsert = $strSQLInsert." VALUES ('$descr_string', '$todaysdate2')";
mysql_query($strSQLInsert);
}
}
Free the resources used for the above operation and close the server connection.
Second PHP Script Run on Crontab Manager
Here I will commence the discussion of the “SECOND PHP SCRIPT TO BE RUN ON A CRONTAB MANAGER” section. This starts out much the same way as “FIRST PHP SCRIPT TO BE RUN ON A CRONTAB MANAGER”. The PHP variables $todaysdate2 and $todaysdate are initialized. The Rydex server is connected to and the database is set. The PHP variable $file_rydex2 is also assigned the file name portion of the FTP connection URL, ‘genericdata”.$todaysdate.”.csv”‘. Next the Rydex temporary transaction account position data table is queried. If successful, then fetch rows from the queried results of this data table. If this is verified then proceed with the following operations for posting the queried row data from the Rydex temporary transaction account position data table:
First update the current daily transaction data table. Loop through the queried results of the Rydex temporary transaction account position data table. Initialize PHP variables for the number of shares and the change in the number of shares. Next query the customer investments data table for the Rydex customer code and the security ticker symbol that were supplied by the previous query on the Rydex temporary transaction account position data table. If the number of rows from this query equals one (1) then assign queried array values to the number of shares. Free the resource used for this query and then retrieve and assign data from the queried results of the Rydex temporary transaction account position data table. Lastly, take the assigned and queried data and append a new record to the current daily transactions data table. Here is the code for this operation without the updates to the good and bad audit trail data tables:
// set query failed php variable '$eflag'(for mysql INSERT operation) // equal to 0 before entering the while loop.
$eflag = 0;
// fetch array results in while loop.
while($row=mysql_fetch_array($result3)) {
// set php variable '$numberofshares' to 0.
$numberofshares = 0;
// set php variable '$shares_delta'(CHANGE IN NUMBER OF SHARES) to 0.
$shares_delta = 0;
// query the customer investments data table for Rydex customer code
// and the security ticker symbol that were queried from Rydex
// temporary transactions data table for the current record in the
// query set.
$resultg=mysql_query("SELECT * FROM cinvestments WHERE customer_code_r = '".$row[0]."' AND ticker_symbol = '".$row[5]."'");
if ($resultg) {
if (mysql_num_rows($resultg) == 1) {
// if the queried result is viable and the number of result rows equals // 1 then proceed.
$numberofshares = mysql_result($resultg, 0, "total_shares");
// assign value in 'total_shares' field from queried row to php
// variable '$numberofshares'.
}
// free '$resultg' resource.
mysql_free_result($resultg);
}
// format date from queried row to prepare for appending to current
// daily transaction data table.
$dvar = substr($row[7], 0, 4)."-".substr($row[7], 4, 2)."- ".substr($row[7], 6, 2);
// take the assigned and queried data for this queried row and append a // new record to the current daily transaction data table.
$strSQLInsert = "INSERT INTO curr_day_trans (customer_code, ticker_symbol, udate, close_price, management_code, total_shares, add_subtract_shares)";
$strSQLInsert = $strSQLInsert." VALUES ('$row[0]', '$row[5]', '$dvar', '$row[9]', 'Rydex', '$numberofshares', '$shares_delta')";
mysql_query($strSQLInsert);
Next update the current weekly transaction data table. Similar to the beginning of the current daily transaction data table, except there is a difference after the inputs have been assigned and/or queried. At this point it will query the current weekly transaction data table for the date is equal to the next Sunday, as well as the customer code and the security ticker symbol that were queried from the Rydex temporary transaction account position data table. If this query is viable then check to see if the number of rows from this latest query are equal to zero (0). If true, then take the assigned and queried data and append a new record to the current weekly transaction data table. If the number of rows from this latest query equals one (1) then update the latest queried record from the current weekly transaction data table with the above mentioned data that was used in the append operation for the instance of zero (0) records from the query on the current weekly transaction data table. Now free the resource used for the query on the current weekly transaction data table.
Now update the current monthly transaction data table. This is almost identical to the update operation for the current weekly transaction data table, except for the date used to query the current monthly transaction data table. This date is constructed from the current month and year and the last day of the current month.
Lastly, update the current quarterly transaction data table. This is almost identical to the update operation for the current monthly transaction data table, except for the date used to query the current quarterly transaction data table. This date is constructed from the current quarter and year and the last day of the last month of the current quarter.
At the end of this second PHP script a message is inserted in the good audit trail data table that verifies that the file contained in the PHP variable $file_rydex2 has been processed. Next free the resource used to query results on the Rydex temporary transaction account position data table. Close the server connection.
Conclusion
These automated PHP scripts worked reliably on the customer’s Web server. It saved a lot of time and work. In today’s business world a reliable, automated PHP script is more than just a vehicle to better efficiency. It is a strong selling point. Nowadays business people everywhere are caught between a rock and a hard place. They have too much work to do and too little time to do it all. Fully automated application development code is the solution that helps to fill this unmet need. For a software development firm it can mean the difference between programming contracts that are won or lost!
I have used my years of experience to compose many business software packages. Aside from websites utilizing PHP and MySQL I have made desktop programs for invoicing, order entry, accounts receivables, accounts payables, chart of accounts, quoting, sales reporting, database, scheduling, job costing and more. Please contact me through my website if you would like to learn more about my developer services.
