manhattes Posted October 6, 2015 Share Posted October 6, 2015 I am trying to take specific dates out of a csv file. Currently I am able to retrieve and display the data with the following code: $url = "http://ichart.finance.yahoo.com/table.csv?s=" . $row2['Symbol']; echo "<html><body><table border=1>"; $f = fopen($url, "r"); $fr = fread($f, filesize($url)); fclose($f); $ch = curl_init(); $timeout = 30; curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, $timeout); $data = curl_exec($ch); echo $row2['Symbol']; $lines = array(); $lines = explode("\n",$data); for($i=0;$i<count($lines);$i++) { echo "<tr>"; $cells = array(); $cells = explode(",",$lines[$i]); for($k=0;$k<count($cells);$k++) { echo "<td>".$cells[$k]."</td>"; } // for k end echo "</tr>"; } // for i end echo "</table></body></html>"; Can I set the script to only grab dates that are on the first of the month or do I have to create a new table and store the information? I guess I dont understand how I would make a variable to be equal to the date column since it is in a csv file. Quote Link to comment Share on other sites More sharing options...
Psycho Posted October 6, 2015 Share Posted October 6, 2015 How you do this depends on how you will use it. You are pulling financial data from Yahoo. If you were to have a LOT of requests, Yahoo could block you. How often is the data updated? It may make sense to parse the data and store it for repeated requests for some amount of time then, after that time expires, fetch new data. But, that's really a different questions. Also, do you ONLY need the date with a specific date? If so, add the logic to the code that reads the data. So, to your code. If the resources is really a CSV file, then the current process is flawed. You don't need to split using \n or on the commas. In fact, that can break if there are other line breaks or commas in the data. PHP has functions for reading csv files which you should use: http://php.net/manual/en/function.fgetcsv.php When reading each line, you will want to test the date field to see if it matches the ones you are looking for. Without knowing the format of that field it is impossible to provide any sample code. Run your code above and change the for loop to this and show us the output for($i=0;$i<count($lines);$i++) { echo "<pre>".print_r($lines[0], true)."</pre>"; } Quote Link to comment Share on other sites More sharing options...
Solution benanamen Posted October 6, 2015 Solution Share Posted October 6, 2015 I will leave it up to you to handle the dates <?php $url = "http://ichart.finance.yahoo.com/table.csv?s=f"; $file = file($url); foreach ($file as $value) { if (stristr($value, '2015-09-10')) { echo $value; } } ?> Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 6, 2015 Author Share Posted October 6, 2015 This is the output: Date,Open,High,Low,Close,Volume,Adj Close Thanks for your response, i didnt know about that function. I only need it once so I guess it would make more sense to save the entire result to a new table for future reference. Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 6, 2015 Author Share Posted October 6, 2015 That almost worked but I think because of the output, the fact the dates are not in the same format is throwing it off. It is also returning the entire row of data: Thursday, October 01, 2015 2011-02-11,5.00,5.09,4.51,4.55,1899700,4.55 Can $value be broken into an array maybe? Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 6, 2015 Share Posted October 6, 2015 I assumed you wanted the entire row based on a particular date. Exactly what "columns" of the data do you want? Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 6, 2015 Author Share Posted October 6, 2015 I am still having trouble with the date format. $file = file($url); $newdate = date_format($row2['Completion Date'], 'y-m-d'); echo $newdate; foreach ($file as $value) { if (stristr($value, $newdate)) { echo $value; } } The first and the 5th column i would like. Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 6, 2015 Share Posted October 6, 2015 You obviously do not have $row2['Completion Date'] stored in your db as y-m-d What is the format and what type of column do you have it in. Best thing to do is let mysql format it in your query. Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 6, 2015 Author Share Posted October 6, 2015 (edited) Yes you are right. $row2['Completion Date'] is stored as Thursday, October 01, 2015 and the format in the csv is y-m-d the column type is varchar Edited October 6, 2015 by manhattes Quote Link to comment Share on other sites More sharing options...
Barand Posted October 6, 2015 Share Posted October 6, 2015 Then why don't you select STR_TO_DATE(completion_date, '%W, %M %d %Y') from your table so you have the format you need. It really is time you converted those dates in your table - it seems to be popping up often in your posts. Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 6, 2015 Author Share Posted October 6, 2015 It is because they are from 2 different sources and each source i add has a different format so I think i need to do it in the code. I am pulling that from my table but I need to match it to a different formatted date. Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 7, 2015 Share Posted October 7, 2015 As @Barand said, you need to get those dates updated properly before you do anything else as well as update the code that is putting it into the database. Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 7, 2015 Author Share Posted October 7, 2015 I fixed this question. The date format function can only be used on a $date variable so I needed to create it first before using date_format. I know my table isn't uniform but that is also why I use PHP to manipulate the text. Here is the fixed code: $date = date_create($row2['Completion Date']); $newdate = date_format($date, 'Y-m-d'); //echo $newdate; foreach ($file as $value) { if (stristr($value, $newdate)) { // echo $value; Thanks for your help @benanamen. Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 7, 2015 Share Posted October 7, 2015 You're still using PHP to format your date. Let my SQL do the work. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.